Query on DataStage Repository

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 4:49 am

Hi

I need to query the DataStage repository to know the Job last run time and job status, for example,

Projecct_name job_name job_category job_lastrun_date, job_status
xxxx a temp\a 01/23/2011 finished
xxxx b temp\b 11/01/2009 compiled

I got Projecct_name job_name job_category

ie. SELECT NAME, JOBTYPE, CATEGORY FROM DS_JOBS;

but unable to get last two columns. please help me on this.

Thank you

venkat

ray.wurlod · Post by **ray.wurlod** » Thu Jan 27, 2011 4:53 am

Why do you need to query the Repository? This information is available directly using the Director client.

It is a non-trivial exercise to query the Repository, because every job's status information is stored in a separate table.

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 5:06 am

hi ray,

actually my project contains 1000+ server jobs. In that we have 300+ unused jobs. we need a xls sheet that contains used jobs one list and unused jobs one list. so could you please help on this.

Thanks you

venkat

chulett · Post by **chulett** » Thu Jan 27, 2011 6:45 am

Turn off the Category and Refresh options then print to file. Import into Excel and sort away.

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 7:51 am

hi chulett,

can please tell me how to do that.

Thank you,
venkat.

chulett · Post by **chulett** » Thu Jan 27, 2011 8:58 am

I did... sorry, meant you can do that in the Director client.

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 9:24 am

ok no problem

anyone can help me on this plz

surionline · Post by **surionline** » Thu Jan 27, 2011 10:12 am

chvenkat.v wrote:ok no problem

anyone can help me on this plz

Hi Venkat,

Chullett already explained how to do that...

Here in detail what Chullett said,

Go to director and switch off the categories (Folders) from View menu ,

Then all jobs will list out in one window..
Now go to project menu and select print , there you will see print to file option..
then save it as file then convert to excel..

--suri

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 10:27 am

hi

Thanks, But we have more then 60+projects in my application.

so is anyway is thier to do it automatically.

Thank you

chulett · Post by **chulett** » Thu Jan 27, 2011 10:43 am

You could easily do all this with a looping script that leverages dsjob to first get all projects then all jobs in each project.

chvenkat.v · Post by **chvenkat.v** » Thu Jan 27, 2011 10:50 am

hi

can please give me any idea about scripting.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 27, 2011 3:42 pm

You can get this from the internet, for example here

chulett · Post by **chulett** » Thu Jan 27, 2011 6:30 pm

chvenkat.v wrote:can please give me any idea about scripting.

What kind of "idea" are you looking for? If you don't have any shell scripting experience, it's not something someone can teach in a post or two. Do you have anyone you work with you could enlist to help you? You help them with the DataStage syntax, they do all the nasty scripty bits...