DSXchange

Hi,
I'm having 9 source tables.I'm using DB2 database as Source and Target also.There is common key for all source tables.I have two options to design a job.
First One is take 9 tables DB2 stages and join them using join/lookup stages.
Second One is using Souce SQL Query join all tables like this
select
A.col1,A.col2,A.col3,B.COL4,B.COL5 , C.COL6,C.COL7,D.col8,D.col9 from ORG A,ORGN_NME B,FIN_ORG C,INTR_ORGN D WHERE
A.col1= B.col1 AND A.col1= C.col1 and A.col1= D.col1
I'm concern about Performance.Which'll give good performance.
Please Clarify it.

Warm Regards,

There are two many variables for anyone to know that answer, if you really want to compare / contrast the two methodologies build both. Me, I would assume the 'all in the database' would work out better.

If the join keys are supported by indexes, then the equi-join in the database will certainly and substantially out-perform the join in DataStage. A further benefit is that DataStage will only need to process those rows that are the result of the join, rather than all rows from all tables.

If the data volume is huge, rather than doing it in the DB level, better to use it in Join level will be fater is my understanding.

Still as chulett said, better do both the way and share the result.

DS User