DSXchange

Hi to all the Gurus and helpful memeber of this forum,

I am new to DataStage and I would just like to ask for your opinion regarding one of my current project wherein I will need to Join 6 tables and output it into a single table:

Table A = 50,000 records
Table B = 300,000 records
Table c = 300,000 records
Table D = 3,000,000 records
Table E = 100,000 records
Table F = 5,000,000 records

with the following conditions:

A.id = B.id
B.name = C.name
C.code = D.code
D.add = E.add
E.state = F.state
F.version = 'A'

my Target table will contain the columns
id, name, code, add, state & version

All Source Tables and Target Table are in DB2.

Can I use one DB2 stage and join all tables using a SQL statement then link it to my Target Table or Is there a better and more efficient way of doing this?

Thanks in advance!

In the database is usually better, because the join can leverage any indexes that may exist on the join keys.

It all depends on ur requirement.

For example
Table A = 50,000 records
Table B = 300,000 records

If u have one to many or many to one or both relationship on above two table the the result u will get a cartesian product from both which may differ from your expected result set, when u fire the query at database level.

If the requirement is to get all records from Table A and their matching records only from rest of the table, then u have treat Table A as driving table and rest of the table is lookup/hash table.

U isn't performing the query. bpaje is.

Ok thanks ray.wurlod & rafik2k for your inputs.

ray.wurlod wrote:U isn't performing the query. bpaje is.

Ray, ofcourse It's not me..
But I had similar kind of issue while joining multiple source table.

due to big numer of record on f table peraps it is etter to do not have only a step with multiple join, but have different job (two it i great i suppose) where the second one read the output of first one and make the other join using temporary table. off course avoid to use look up instead of join , them are so slow

What i think is Using hash files and look ups would be a better option as it would be a bit faster than the joins