Running job with multiple parameters

[ds_infy]

Hi All

I have a job with four main parameters: DB2 DSN, User, Schema and Password. they are set as environment variables and I am reading it in the job, the job extracts data from one of the database tables and writes to a dataset file.

I have a requirement to run the same job with in the same Datastage project multiple times for different Source DB/user/pwd/schema. table names and logic will remain same, Now I added new environment variables for each new source, I created a outer sequencer which will read the required source details (from env variables) and pass it on to the job, likewise I am creating new outer sequencer for every new source,

I would like to know if there is a better way to handle this dynamically(with out the need to develop something in Datastage for every new source), Kindly help

Thanks

[chulett]

Perhaps drive the sources from a table or file, read from that to dynamically drive your loop.

[ray.wurlod]

Use parameter set values files. Provide the name of the appropriate values file as the "value" of the parameter set at run time.

[naveenkumar.ssn]

Hi

Create a parameter set with all the values you wanted with the respective value files to it.

Add the parameter set to the sequence.Pass those to the multiple instance jobs in a sequence. ( you dont need to create any separate sequence)

Regards
Naveen

[ds_infy]

Thanks Chulett, Ray and Naveen. I have combined the approach mentioned here, we are maintaining source credentials in a database table and we created a one time job to populate the parameter set value files, I am able to pass the required value files at the run time, we want to maintain credentials in administrator or a database table(rather than a parameter set), hence this approach.

However due to another issue, I could not still get this job running dynamically, our sequencer has job 1. generates unique id for the main job and sets the status as running in an audit table, 2. Main job which extracts the required db table into a file 3. updates the status as success for the same unique id (step 1 and 3 are for audit purposes, 2 is actual functionality), a parameter set value file was created to hold the unique id and this was passed from job 1 to job 3. In the job activity stage for parameter set mapping mentioned the value file name,

since the same sequencer is used now for running with different sources, 3 value files(to hold unique id) were created for three different source, but if I pass parameter set name or as pre-defined in the mapping of the job activity stage, the job 3 gets the unique id generated before the start of the run and not the one created in the job 1, pls let know if I am missing something here, eventually I have to create a new sequencer for every source but can run the extract job alone dynamically

I am also looking to use execute command activity stage and run a unix command to grep the value file and then pass on unique id to job 3.. pls let know if there is a better approach, thanks a lot for your time

[kduke]

I would read the database in a job. You need to get the parameter values in a variable in the sequence to use in a parameter. I would make the sequence multiple instance. Use the source as the instance id. There are lots of ways to get the values into variables. No need to to use environment variables.

[ds_infy]

Thanks all for inputs, we created a routine to read the parameter value file and get the value from it for required parameter. when calling a job, in the sequencer job parameters mapping section, routine will be called by passing paramfile name and parameter name, so return value(in my case new unique id generated in job 1) gets mapped to the job3.

I am still not sure, if this is a bug in Datastage as we are not able to use the parameter set value file updated in job 1 in subsequent jobs of the same sequencer, yes it works if the job is called by passing "value file name", but in a scenario of value files need to be passed dynamically it does not work,

Thanks all, I was unable to see kduke's full reply as its a premium content, I have been trying quite some time to create a new id for me and get premium access but registration doesnot work, will work that out.

[Mike]

No bug. Just standard parameter behavior. A job resolves its parameter values when it starts. Changes to parameter values after a job starts won't be recognized.

In your case, the job sequence read the values file when it started executing. Any changes that were made to the values file after that won't be recognized by the job sequence.

So the job sequence just passes the parameter set values that it read at startup to its controlled jobs.

Using a routine is one technique for reading dynamic parameter values from a file.

Mike

[ray.wurlod]

There's nothing to stop you re-reading the file later in the sequence, which would pick up any changes.