Job sequencer : passing parameters to a job

[luca]

Hi !

A few days ago, I asked a way to find a value stored in a Database (date) and pass it to a job as value for a parameter in a Job sequencer.
This works fine using a routine in the job sequencer.

Now, I have changes to my requirements as I'll have to find values for many parameters (20 or even more).
I am not going to write one routine to get each of this parameters.
I thought of doing this in a job sequencer which would call 2 jobs :
- first a job to find all the parameters and write it in a file,
- then a job control code to read this file and launch my job sequencer with the parameters.
This could be done but I have many jobs sequencer runing job sequencer (2 or 3 levels).
If I launch my job sequencer from a job control, I will not be able to follow and sequence the different job sequencer.
Hope you get my point (not easy to explain it).

Another way of explaining this could be :
I have one job sequencer JS1, which sequences 3 other jobs sequencers (JS_11, JS_12, JS_13)
Each of this jobs sequencers can sequence again other jobs sequencers (ex : JS_11 sequences JS_111, JS_112, JS_113, JS_114)
Then at the lowest level, the jobs sequencers run simples jobs with design flow (ex : J_S111 runs simples jobs J_1111, J_1112)
How can I do to find parameters for any of the lowest level jobs
ex : in JS_111, I want to find the parameters for jobs J_1111, J_1112, ...
The parameters name and values will be stored in a table which coulb be : T_JOB_PARAM (pk_job, pk_param_name, param_value)
Of course, I want to do this in a reusable object.

Do one of you have already done this ?
How did you manage to do it ?
What about Parameter Manager ? Is this the kind of things that Parameter Manager would help to manage ?

Thanks

[ray.wurlod]

Parameter Manager would be perfect to ensure that the same parameter names, types, default values and descriptions are loaded into each job at design time. However, it does not manage parameter values at run time.

The only mechanism you have for propagating parameters is downwards from the controlling jobs. Given that all jobs probably need to use the same values for these parameters in any given run, it is most likely that you would obtain these values in the master controlling job (JS1 in your example).

I would advise creating a small job to move the parameter values from the table into a text file (perhaps called jobparams.ini?), then the master controlling job (sequencer) would read them from here and set the values of its child jobs.

Second-level jobs would interrogate their own parameter values (using DSGetParamInfo() function) to obtain values with which to set the parameter values of the jobs that they control. In your example, job JS11 would use its own parameter values to set the parameter values for JS111, JS112, etc. And, of course, the same strategy would be used for any levels lower in the hierarchy.

I have done this at a number of sites, among other customizations of the job control code produced by DataStage.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[luca]

Hi Ray !
that's the way we are doing this right now.
We want to change this in order to have set of jobs reusable anywhere, without having dependencies with their top job sequencer.
this would be usefull for exemple to avoid having to change all the hierarchy of job sequencer when you add one parameter to a low level job.
Kind of object oriented thing, do you understand our issue ?
Hope so.

Thanks for the link to Parameter Manager pdf file.

[ray.wurlod]

You'd know, from reading this Forum, that a job cannot set its own parameter values once it is running.
You could certainly achieve your aim by creating a Routine as your "reusable object" and invoking it through a Routine activity. Typically, the ReturnValue from the routine would have to be a doubly delimited list of parameter names and values, such as
param1=value1|param2=value2|param3=value3
Look at the SDK routine UtilityRunJob for one example of how to do this. The mechanism (code) for decomposing this structure into job parameter (DSSetParam calls) could be constructed in a header file (for example called LoadParams.h) within the project and included in the code generated by the sequencer.
You will need to create some BASIC code; this is because the Routine activity is executed before the Job activity, so that you don't have access to the job handle variable within the Routine code.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518