Add new parameter to all jobs

[Simba]

I want to add new parameter to all jobs like owner_stg. Also, I want to modify user defined query with #owner_stg# and table name in the target. Please advise if there any easy way to do this. I have around 500 jobs in a folder.

Thanks,
Moses

[kduke]

Someone asked a similar question last week on how to change the schema name on a fully qualified table name on all their jobs. Do a search. There were several answers all have issues in that once you start playing around with DS_JOBOBJECTS you could have serious problems.

[ray.wurlod]

Get Parameter Manager for DataStage.
You can find out about it from here

[vmcburney]

There is a way to do it by exporting the folder to a sequential file and adding the parameter using search and replace commands in MS Word.

In an export file find a parameter that is in all jobs, it looks something like this:

Code: Select all

      BEGIN DSSUBRECORD
         Name "CODE"
         Prompt "Enter a code"
         Default "MONARO"
         ParamType "0"
      END DSSUBRECORD

In MS Word search and replace part of that text string to add a marker to each parameter section to indicate where the new parameter will go. In Word you can use ^p to find and replace end of line characters.

Code: Select all

Find:      BEGIN DSSUBRECORD^p         Name "CODE"
Replace With:|ZZ|^p      BEGIN DSSUBRECORD^p         Name "CODE"

Now replace the |ZZ| with the new parameter.

Code: Select all

Replace:|ZZ|
With:     BEGIN DSSUBRECORD^pName="NewParamName"^pPrompt="New Prompt"^pDefault="Whatever"^ParamType=0^pEND DSSUBRECORD

Should give you this before each job in the export file:

Code: Select all

BEGIN DSSUBRECORD
Name "NewParamName"
Prompt "New Prompt"
Default "Whatever"
ParamType "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
         Name "CODE"
         Prompt "Enter a code"
         Default "MONARO"
         ParamType "0"
      END DSSUBRECORD

Not as good as parameter manager. There is a good chance that this could corrupt your export file so don't go overwriting anything you haven't got backed up!

[kduke]

Ray is right. PM will add the parameter to all the jobs. I also posted code which will copy parameters. The hard part is changing the table name to a parameter. Creating the parameter is a lot easier.