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.
Surrogate Key Generator
Moderators: chulett, rschirm, roy
Surrogate Key Generator
Developer of DataStage Parallel Engine (Orchestrate).
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
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)
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)