I have to join five large tables (8 millions and about 4-5 GB data in each), and then insert records into another table.
currently we are doing it using procedures and the procedures takes almost 1 day for processing; so now wanted to do it in Datastage.
i am new to server job. can't find any stage like join, lookup.
hash file also has limit of 2 GB....
any suggestion please.
Join in server job
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
If these "five large tables" are all in the same database then your join is a sql join in the source db stage. Otherwise, yes a hashed file can be used to effect a join. That 2GB limit is only for 32bit (default) hashed files, they can be created as 64bit so there is no such limit.
Would need more details about the nature of this "join" you are doing.
Would need more details about the nature of this "join" you are doing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
currently the tables are left outer joined with master table, and inserted into a temporay table,
then join this temporary table to another table and load another temporary table,
then join this second temporary table to another table and load into third temporary table, .................like wise...
a cursor is used to select and insert records, because of larger volume a single SQL Select .......insert fail for rollback segment..
then join this temporary table to another table and load another temporary table,
then join this second temporary table to another table and load into third temporary table, .................like wise...
a cursor is used to select and insert records, because of larger volume a single SQL Select .......insert fail for rollback segment..
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Can you split the rows into multiple groups - something like if tableCustomer returns any rows then follow tableCustomerDetails else follow tableInternalCustomer etc.
If you can split by either logic or subject area, you can break the SQL into multiple jobs with simpler logic.
This way it will also be easy for testing.
If you can split by either logic or subject area, you can break the SQL into multiple jobs with simpler logic.
This way it will also be easy for testing.