Page 1 of 1
UNION ALL v/s FUNNEL Design Issue
Posted: Thu Nov 30, 2006 12:20 am
by balajisr
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?
Posted: Thu Nov 30, 2006 2:38 am
by BalageBaju
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...
Posted: Thu Nov 30, 2006 3:52 am
by Nageshsunkoji
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.
Posted: Thu Nov 30, 2006 1:51 pm
by ray.wurlod
Let the database do it.
Posted: Sun Dec 03, 2006 10:39 pm
by balajisr
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
Posted: Mon Dec 04, 2006 1:03 pm
by chandra
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 .
Posted: Mon Dec 04, 2006 1:48 pm
by thebird
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.