List out all the DS jobs using specific environment variable

ramu1 · Post by **ramu1** » Tue Apr 10, 2012 12:31 am

Hi Everyone,

How can we list all the DS jobs which were using a specific environment variable in a project?

We have thousands of DS jobs in our project and need to list all the jobs used by specific environment variable like #$SqlServerDSN#.

I took the latest copy of .dsx file and manually searching for the jobs. Is there any better approch to list out the all the jobs?

Can anyone please help me by your suggestion.

Thanks and regards,
Ramu

vamsi.4a6 · Post by **vamsi.4a6** » Tue Apr 10, 2012 12:51 am

We can implement through unix script as below

1)Get the list of the jobs in project by using dsjob command
2)For each Job get the list of parameters by using dsjob command
3)Check whether specific environment variable is present or not in a job.
We need to pass proper parameters for dsjob command to implement the first and second step

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 10, 2012 4:02 am

yes that will give you the parameters defined in the job, whether used anywhere or not. Also you can find that out by querying datastage repository and also whether it is used inside any stage or not.

If to be done from DSX, I think Phil Hibbs posted a spreadsheet with macros to parse the dsx and give you the details in tabular format. You might want to check that out.

vamsi.4a6 · Post by **vamsi.4a6** » Tue Apr 10, 2012 5:10 am

Can any one please tell me where to query the Datastage Repository tables like DS_JOBS and DS_JOBOBJECTS for a particular Datastage project?

kmsekhar · Post by **kmsekhar** » Tue Apr 10, 2012 6:32 am

vamsi.4a6 wrote:Can any one please tell me where to query the Datastage Repository tables like DS_JOBS and DS_JOBOBJECTS for a particular Datastage project?

Administrator Client Component-> Projects Tab-> Command Button -> Here You need to query Repository Tables...
Another way is from DSEngine folder-->using dssh command connect to repostiry

kmsekhar · Post by **kmsekhar** » Tue Apr 10, 2012 6:33 am

priyadarshikunal wrote:If to be done from DSX, I think Phil Hibbs posted a spreadsheet with macros to parse the dsx and give you the details in tabular format. You might want to check that out.

viewtopic.php?t=144212&highlight=