Tech Note: Extract Parameter Values From a Database And Use Them In Subsequent Job Activities In DataStage

A common requirement when building a DataStage jobs is to read some configuration values and use them in subsequent jobs. One of the best ways to do this is to use a Parameter set and have a job that writes the configuration values as a parameter value file. Bellow we will review how to build such a job and use the generated parameter value files.

The Parameter sets in DataStage are used to store a collection of parameters in a named object. The values for these parameters can be stored in a value file on the file system. Step-by-step guide how to work with Parameter sets in DataStage could be found on the IBM's site here. Because the values for the parameter sets are picked up at run-time, this will allow us to recreate these value files just before the start of the job where they are used. To do so we need to know the value file format:

Parameter1Name=Parameter1Value
Parameter2Name=Parameter2Value
...
ParameterNName=ParameterNValue

The above could be easily achieved within a single job with a Transformer stage and Sequential file stage. If you like SQL better you can skip the transformer stage and do the formatting right in the source database stage. Shown below are the DataStage job that produces the value file, the transformer derivation used and the stage that outputs the file:

Now comes the question - where to store the value file. The directory for each DataStage project, has sub directory "ParameterSets" where each Parameter set has its own sub-directory. There you will find all value files for a Parameter set. You can provide this info to the job that generates the value file as shown below (please note the use of the @PATH system variable). The "MyParamsSet" is the name of the parameter set you want to use and the "valueFileDB" is the name of the value file where you will put the values loaded from the database. The more experienced DataStage of course knows, that you can leave out the @PATH, since the default path is the project directory anyways.

Now we are ready to use the parameter set, but we need to make sure that the job that uses it will be started after the job that generates the value file - this is how the sequence looks like:

Of course you could achieve the same with a user status as discussed in our previous post. But as you can see with parameter set value files you leave parsing the parameter names and values to DataStage. No complex Value Expressions in your Job Activities, but simply the predefined value file. Its cleaner, faster to implement and easier to maintain!