Dymanic update of parametres/variables

ktweedly · Post by **ktweedly** » Tue Feb 17, 2009 12:49 pm

I am looking for a way to dynamically update parameters in DS 7.5.1.

Here is the scenario:

In my sequence the first job selects max(key). I need to pass the value to the next job and plug it into a surrogate_key_generator to provide the starting value.

If I use a stage variable I can't pass it to the next job. I need to get the value into a parameter that the SKG stage can understand.

I am not talking about parameters on the command line.

I need to get the value returned from the select into a "sequencer level" parameter that will be available to all jobs in the sequence.

I have perused many forums and all I have found are allusions to params on the command line or 'you can't do it'.

I refuse to believe that such a simple concept is not doable in DS.

Thanks,

DSguru2B · Post by **DSguru2B** » Tue Feb 17, 2009 1:07 pm

Well it is certainly doable.
Let the first job be a server job that gets the max and stores it in the USER Status of the job. (Search the forum on how to do that)

Write a simple three/four liner server routine that gets this value back(Again search for it, i have seen it on the forum).

In the derivation portion of parameters of the job activity that calls your parallel job, call the server routine. This way the value will be populated during run time.

velagapudi_k · Post by **velagapudi_k** » Tue Feb 17, 2009 1:27 pm

I have done this before. Write the max surrogate key in to a flat file. The flatfile will have onlt one value. Write a routine to read the value from file.
The job that follows next will get the key from 'routine.returnvalue'

JoshGeorge · Post by **JoshGeorge** » Tue Feb 17, 2009 1:36 pm

I have a blog post on the same - Surrogate Key Generation in DataStage - An elegant way. If you know to use DataStage Job Control Interfaces specified in Advanced developer guide this will be really helpful.

chulett · Post by **chulett** » Tue Feb 17, 2009 2:18 pm

JoshGeorge · Post by **JoshGeorge** » Tue Feb 17, 2009 2:31 pm

ktweedly · Post by **ktweedly** » Wed Feb 18, 2009 10:23 am

I have created the routine to set $Userstatus. It compiled and tested OK. When I try to use it in the column derivation (per the posts) it stays red (Variable 'userstatus' not defined).

Am I doing something wrong?

DSguru2B · Post by **DSguru2B** » Wed Feb 18, 2009 2:01 pm

There has to be two routines. One to SET it (which you already have). A second one that GETs the value from the user status. And the second routine needs to go in the derivation.