I have certain queries which results milions of data and I'm updating target tables based on the data derived thru above queries.
but whole job is taking long time around 2-3 days.
How can I optimize this process?
Job optimization
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Certain queries", eh? That's not very informative - could it be the ten way full outer join query from hell? How long does the same query take if DataStage is not in the mix? You will not be able to do anything in DataStage to improve this time. Parallel execution is a possibility but, in a server job (or several) you need to design this in.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Are you only doing updates? If not, you are probably doing upserts. Split the file into two, one for inserts and one for updates. Then run each file through the proper load.
If you are doing all updates, consider generating a key file and delete all the rows with those keys. Then bulk insert all the rows. Of course if you do not have all the columns for the rows in the source, you would need to bring down the missing columns and 'join' them to the source in datastage.
The point is, you need to understand what is taking so long. Upserts are expensive, updates are next. Inserts through a non-bulk OCI are pretty good. But nothing beats bulk utilities.
If you are doing all updates, consider generating a key file and delete all the rows with those keys. Then bulk insert all the rows. Of course if you do not have all the columns for the rows in the source, you would need to bring down the missing columns and 'join' them to the source in datastage.
The point is, you need to understand what is taking so long. Upserts are expensive, updates are next. Inserts through a non-bulk OCI are pretty good. But nothing beats bulk utilities.
Michael Favero
2852 Humboldt Ave So
Minneapolis, MN 55408
2852 Humboldt Ave So
Minneapolis, MN 55408