UNION ALL v/s FUNNEL Design Issue

balajisr · Post by **balajisr** » Thu Nov 30, 2006 12:20 am

I have three tables in DB2 with the same metadata. I need to combine these three tables into a single dataset (similar to unix cat command). Each table has approx 5 lakh records. I am running on 2 node configuration. My datastage version is 7.5.1A.

I can think of two options to combine 3 tables

1. Using UNION ALL in a single DB2 API stage to combine 3 tables.
or
2. Read 3 tables using 3 seperate DB2 API stages and funnel(Continuous funnel) it.

Which one of them would be better in terms of performance or is there any better alternative?

BalageBaju · Post by **BalageBaju** » Thu Nov 30, 2006 2:38 am

Balaji,

I think it is better to use your first option ie by using the UNION ALL command in DB2 API Stage for combining your tables.

Or otherwise if you are using Funnel stage then it is better to use the
'Sequence funnel' bcoz this property copies whole data from first table and loads into target and then second table and third table and so on...

Nageshsunkoji · Post by **Nageshsunkoji** » Thu Nov 30, 2006 3:52 am

Hi,

As per my past expereince, it is always better to utilise the Datastage rather than DataBase queries.

In funnel stage, you can use sequential funnel, if your requirement is that, other wise go for continous funnel by default. If you want your data in a sorted manner then go for Sort funnel.

If you don't have any requirement, go for Continous funnel by default.

ray.wurlod · Post by **ray.wurlod** » Thu Nov 30, 2006 1:51 pm

Let the database do it.

balajisr · Post by **balajisr** » Sun Dec 03, 2006 10:39 pm

Thanks everyone for your replies.

I will let the database do it.

I could not mark the topic as resolved. I get a dialog box stating "failed: topic_id=?" etc.. when i click on Resolved button

chandra · Post by **chandra** » Mon Dec 04, 2006 1:03 pm

I am out of touch with datastage ! can you please tell me what is this :-
sequential funnel, if your requirement is that, other wise go for continous funnel .

I know only funnel stage ! i never went into properties of it .

thebird · Post by **thebird** » Mon Dec 04, 2006 1:48 pm

chandra wrote: can you please tell me what is this :-
sequential funnel, if your requirement is that, other wise go for continous funnel .

Inside the Funnel, you have options for Continuous, Sequntial or Sort.

Continuous Funnel combines records as they arrive (i.e. no particular order); Sort Funnel combines the input records in the order defined by one or more key fields; Sequence copies all records from the first input data set to the output data set, then all the records from the second input data set, etc.