Surrogate Key Generator

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Surrogate Key Generator

Post by Teej »

Hey folks -- I am trying to determine the best method to use the Surrogate Key Generator stage that has been introduced for 7.0.1. Before I really get into it (and right now I'm pulled in all directions), I would like to pick everyone's brain here:

For 7.0.1, what is the best method to pass a value obtained from a SQL query (Oracle) onto a job's parameter (that will be used by the PX Surrogate Key Generator stage)?

I definitely will need to review the manuals, but I want to also pick your brains on ideas for this.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

Doing a simple database retrieval has been discussed before, use the DSExecute command in a routine to shell out and run a SQL Plus command that executes a statement that echos the data required. Read this as the output from the DSExecute and make it the return value of the routine.

Have a look at this thread about executing SQL Plus:
viewtopic.php?t=85234&highlight=sql+plus

There are a couple other options for getting a value out of Oracle for use in a sequence job or batch code however this way is pretty simple.
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

When do I execute DSExecute? How can the returned value be migrated to the job parameter via Job Sequencer? Or do I have to use BASIC to call those jobs with the new values?

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
jreddy
Premium Member
Premium Member
Posts: 202
Joined: Tue Feb 03, 2004 5:09 pm

Post by jreddy »

i guess what Vincent suggests sounds doable to me.

We could code a call DSExecute, to get the value you need, then use a DSSetParam within the routine itself to set that value to a job parameter, in the job control. Since this is at the job level, you could directly use that job parameter in the surrogate key generator (i dont have access to the documentation or the s/w yet, but from your question, assumed u could use a job parameter in that stage to initialize the value of the surrogate key probably)
Post Reply