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!
Join multiple DB2 tables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Join multiple DB2 tables
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
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Join multiple DB2 tables
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
SMB