Hi,
I would like some advice on which of the following methods is recommended to extract data from source DB and load to target DB with minimal transformation.
Option A -
Step 1: Extract from Source DB to Text File via Shell Script / SQL
Step 2: Load the contents of Text File (from Step 1) to Target DB via Datastage
Option B -
Step 1: Extract from Source DB and Load to Target DB Text File via Datastage (same job)
which option is better with consideration to performance and better troubleshooting ? Appreciate your advise. Thanks
Recommended method to Extract and Load from source DB
Moderators: chulett, rschirm, roy
Depends how stable your environment is.
If you suffer from network disconnects from time to time, I'd use datastage to unload (not a shell script. Drop to dataset (not a sequential file). Then job 2 would load target from dataset.
That should only be done if you have ANY concerns about dropping a job while it is still running the load to target. You don't want to have to re-extract all that data.
The best approach is have datastage extract and and load in the same job.
If you suffer from network disconnects from time to time, I'd use datastage to unload (not a shell script. Drop to dataset (not a sequential file). Then job 2 would load target from dataset.
That should only be done if you have ANY concerns about dropping a job while it is still running the load to target. You don't want to have to re-extract all that data.
The best approach is have datastage extract and and load in the same job.
Re: Recommended method to Extract and Load from source DB
1. Both source and target are same Database (I mean Oracle, SQL server etc)?cosec wrote:I would like some advice on which of the following methods is recommended to extract data from source DB and load to target DB with minimal transformation.
2. Just a direct load or any transformation?
3. Just insert / update?
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Recommended method to Extract and Load from source DB
1. Source is Oracle; Target is DB2
2. loading with minimal transformation
3. update or Insert
2. loading with minimal transformation
3. update or Insert
Re: Recommended method to Extract and Load from source DB
Thanks for your inputs.
However considering if there is any recovery required in case of a job failure (for e.g. special character in the source record and row is rejected during loading), wouldn't it be more efficient to amend the sequential file rather than the source DB (which may belong to an external party)? just a thought.
However considering if there is any recovery required in case of a job failure (for e.g. special character in the source record and row is rejected during loading), wouldn't it be more efficient to amend the sequential file rather than the source DB (which may belong to an external party)? just a thought.
In the project I am working at the moment we have had to load tables with something like 300m rows and the prospect of a failure and restart could not be considered.
What we did was to create a control table which contained the first part of the key to the source table records so that we could load groups of records and any failure would not therefore be catastrophic.
This assumes, of course, that your record keys lend themselves to some sort of grouping by taking the first part of the key.
Hope that helps.
Bob.
What we did was to create a control table which contained the first part of the key to the source table records so that we could load groups of records and any failure would not therefore be catastrophic.
This assumes, of course, that your record keys lend themselves to some sort of grouping by taking the first part of the key.
Hope that helps.
Bob.
Bob Oxtoby