Recommended method to Extract and Load from source DB

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
cosec
Premium Member
Premium Member
Posts: 230
Joined: Tue May 08, 2007 8:10 pm

Recommended method to Extract and Load from source DB

Post by cosec »

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
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

(cough) Server job so sequential file it is.
-craig

"You can never have too many knives" -- Logan Nine Fingers
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

I dislike server jobs since they can't be grid enabled.

*looks for a spittoon*
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Re: Recommended method to Extract and Load from source DB

Post by SURA »

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.
1. Both source and target are same Database (I mean Oracle, SQL server etc)?
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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I would tend to opt for Option B, with inter-process row buffering enabled.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cosec
Premium Member
Premium Member
Posts: 230
Joined: Tue May 08, 2007 8:10 pm

Re: Recommended method to Extract and Load from source DB

Post by cosec »

1. Source is Oracle; Target is DB2
2. loading with minimal transformation
3. update or Insert
cosec
Premium Member
Premium Member
Posts: 230
Joined: Tue May 08, 2007 8:10 pm

Re: Recommended method to Extract and Load from source DB

Post by cosec »

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.
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

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.
Bob Oxtoby
Post Reply