Mass parameterization via XML export

[gateleys]

We have tons of old jobs where variables such as database, user, password, etc were hard-coded. We now need a mechanism to automate the process of parameterizing each of these jobs. One of the ways would be export all these jobs as XML and parse the files for the variable and replace them with the desired parameter names. I know it can easily be done outside DataStage, using XML parsing tools. However, I wish to do it within DataStage itself. Has anybody performed similar tasks in the past, and if so, how did they proceed?

Thanks.

[ArndW]

I have done this type of thing before. Export as a .DSX and then write a DS job to read it in, parse & replace the text and write it back out to a sequential file as a .DSX

[gateleys]

Hi Arnd,
I exported as .dsx a simple job and looked at the underlying code. Now, if I were to parameterized just the database, user and password, I would be looking at the following section of the code-

Code: Select all

BEGIN DSRECORD
      Identifier "V0S42"
      OLEType "CCustomStage"
      Readonly "0"
      Name "MY_OCI_STAGE_NAME"
      NextID "3"
      OutputPins "V0S42P2"
      StageType "ORAOCI9"
      Properties "CCustomProperty"
      BEGIN DSSUBRECORD
         Name "DATABASE"
         Value "MY_DB_NAME"
      END DSSUBRECORD
      BEGIN DSSUBRECORD
         Name "USERID"
         Value "MY_USER_ID"
      END DSSUBRECORD
      BEGIN DSSUBRECORD
         Name "PASSWORD"
         Value "MY_ENCRYPTED_PASSWORD"
      END DSSUBRECORD

Can you please tell me how you would go about changing, for example, the value of database ("MY_DB_NAME") to #DBPARM#, and further below to change user and password to #USERPARM# and #PASSPARM# respectively. Oh yes, lets assume that we do not have access to any UNIX systems to perform the conversion.
Thanks.

[ArndW]

In your transform derivation do a

Code: Select all

EREPLACE(In.DSXLine,'Value "MY_DB_NAME"','Value "#DBPARM#"')

You will also need to make sure that your job has this DBPARM declared as a parameter, if not then this also needs to get added to the DSX output file, otherwise the new jobs will not compile or run.

[gateleys]

In your transform derivation do a

Code: Select all

EREPLACE(In.DSXLine,'Value "MY_DB_NAME"','Value "#DBPARM#"')

You will also need to make sure that your job has this DBPARM declared as a parameter, if not then this also needs to get added to the DSX output file, otherwise the new jobs will not compile or run.

Ok, so the entire file will just have ONE field, on which the find-and-replace technique will have to be applied, and then add the parameter-names with the necessary tags to the .dsx file---so that the parameters are set at the job properties. Simple and clean approach..thanx.

Now, I have a routine that is used to pass parameters to these 'changed' jobs. I already have these jobs in sequences. I guess I will have to parse the dsx file of the sequences as well....add required lines, change some of them. I will give it a shot and let you guys know the progress.

Thanks.

[ray.wurlod]

You'll find that easier if you do the export including defaulted properties. Then you'll find that more things are replaceable, and you don't have to worry quite so much about inserting new structures.

[kduke]

Buy ParameterNavigator from me would save you lots of time and trouble.

Parameters are simple. They are stored in the ROOT record of a job in DS_JOBOBJECTS.

Here is some SQL to report on them and to list them.

Code: Select all

AttrNo
14. ParamNames
15. ParamDesc
16. ParamPrompt
17. ParamDefault
18. ParamHelpTxt
19. ParamType
20. ParamListValues

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamNames', 
   'D', 
   '14', 
   'ParamNames', 
   '50L', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamDesc', 
   'D', 
   '15', 
   'ParamDesc', 
   '40T', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamPrompt', 
   'D', 
   '16', 
   'ParamPrompt', 
   '40T', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamDefault', 
   'D', 
   '17', 
   'ParamDefault', 
   '40T', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamHelpTxt', 
   'D', 
   '18', 
   'ParamHelpTxt', 
   '40T', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamType', 
   'D', 
   '19', 
   'ParamType', 
   '3R', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'ParamListValues', 
   'D', 
   '20', 
   'ParamListValues', 
   '40T', 
   'M', 
   'MvParams'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP
) VALUES (
   'MvParams', 
   'PH', 
   'ParamNames ParamDesc ParamPrompt ParamDefault ParamHelpTxt ParamType ParamListValues'
)
; 

Code: Select all

select
   DS_JOBS.NAME JobName,
   ParamNames,
   ParamDesc,
   ParamPrompt,
   ParamDefault,
   ParamHelpTxt,
   ParamType,
   ParamListValues
from 
   unnest DS_JOBOBJECTS on MvParams,
   DS_JOBS
where 
   DS_JOBOBJECTS.OBJNAME = 'ROOT'
   and ParamNames = 'pCheckpointInvocationIDBegin'
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
order by
   ParamNames
; 

select
   DS_JOBS.NAME JobName FMT '40L',
   ParamNames,
   ParamDefault
from 
   unnest DS_JOBOBJECTS on MvParams,
   DS_JOBS
where 
   DS_JOBOBJECTS.OBJNAME = 'ROOT'
   and ParamNames = 'pCheckpointInvocationID'
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
order by
   ParamNames
; 

   and DS_JOBS.NAME = 'LoadEtlJob'
   and ParamNames = 'TargetDSN'
   and ParamNames = 'pCheckpointInvocationID'
   and ParamNames = 'pCheckpointInvocationIDBegin'
   and ParamNames = 'pCheckpointInvocationIDEnd'

[kduke]

Here is similar information on how parameters are applied in a sequence.

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'JobActParamNames', 
   'D', 
   '20', 
   'JobActParamNames', 
   '30L', 
   'M', 
   'MvJobAct'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'JobActParamDefault', 
   'D', 
   '21', 
   'JobActParamDefault', 
   '30L', 
   'M', 
   'MvJobAct'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'JobActParamType', 
   'D', 
   '22', 
   'JobActParamType', 
   '1R', 
   'M', 
   'MvJobAct'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'JobActParamValues', 
   'D', 
   '23', 
   'JobActParamValues', 
   '30L', 
   'M', 
   'MvJobAct'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP
) VALUES (
   'MvJobAct', 
   'PH', 
   'JobActParamNames JobActParamDefault JobActParamType JobActParamValues'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'JobAct2JobAct2InstanceIdsCount', 
   'I', 
   'field(@RECORD<14>, "/", 2)', 
   'JobAct2JobAct2InstanceIdsCount', 
   '3R', 
   'S' 
)
; 

Code: Select all

select
   DS_JOBS.NAME JobName FMT '40L',
   DS_JOBOBJECTS.NAME LinkName FMT '30L',
   JobActParamCount,
   JobActParamNames,
   JobActParamDefault,
   JobActParamType,
   JobActParamValues
from 
   unnest DS_JOBOBJECTS on MvJobAct,
   DS_JOBS
where 
   DS_JOBOBJECTS.OLETYPE = 'CJSJobActivity'
   and JobActParamNames = 'pCheckpointInvocationID'
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
order by
   JobActParamNames
; 

   DS_JOBOBJECTS.OLETYPE = 'CJSJobActivity'
   and DS_JOBS.NAME = 'Master_Sequence'
   
   and DS_JOBOBJECTS.NAME = 'Mnt_Sprocs'
   and DS_JOBOBJECTS.NAME = 'Cognos_Reportnet'

   and JobActParamNames = 'pCognosBatFilePath'
   and JobActParamNames = 'CheckpointInvocationID'
   and JobActParamValues = '"dmaint"'
   and JobActParamDefault = '"dmaint"'
   and JobActParamValues <> JobActParamDefault
   
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 

[chulett]

Buy ParameterNavigator from me would save you lots of time and trouble.

How much is that bad boy nowadays, Mr Kim?

[kduke]

Ask Dennis.

[chulett]

Done.

[gateleys]

Thanks Craig,
Your response has allowed me to get a better understanding of the internal structures of these jobs, and hence be able to manipulate them (with your help again). You guys are awesome. Oh yeah, I wish I had the authority to make purchases, but sure can influence them. Does the ParameterNavigator work with DS7.0 on Windows, with Oracle, SQL Server, DB2? Is there any trial version? How much does it cost?

Thanks.
gateleys

[chulett]

We all know you really mean Kim. It's probably just his sig that threw you off.

[kduke]

ParameterNavigator is my product. There is a free trial version. It is describe on my web site below. It does just about anything you can think of with parameters. It has usage analysis. It copy groups of parameters to jobs like the 3 or 4 parameters to connect to a specific database. Maybe you have 20 parameters which every sequence needs then all 20 can be copied at once.

I have had routines which do this for years. I just added a nice front end and a repository. I also combined it DwNav so they are one product with common interface. I have always used job metadata to help me work faster and smarter.

Guess what when I post a SQL statement like the above then you can use these in a job with a UV stage. You can then create your own metadata repository. I do this a lot in EtlStats. EtlStats is free. It will show you how to extract job names, link names, job type (sequence or server) besides row counts. I want to automate reports against this information like run times of all sequences in the Production\Sequence category. Can you run a report like that? What is the average rows per second on all ODBC stages?

When you move jobs from DEV to TEST can you change the default parameter values from the development database to the test one? The ParameterNavigator can. How many of us have updated the wrong database because the job took the default parameter of development worse is you updated a production database by accident. Eliminate these mistakes. The dictionaries posted here add a lot value in understanding how sequences use parameters and are all sequences doing it the same way.

[gateleys]

We all know you really mean Kim. It's probably just his sig that threw you off.

Oh yes, I meant Kim. But Craig has come to my rescue many a times too. Kim, you still did not provide answer about ParameterNavigator's compliance with DS7.0, and the $ Amount.

gateleys