Page 1 of 1
Query on DataStage Repository
Posted: Thu Jan 27, 2011 4:49 am
by chvenkat.v
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
Posted: Thu Jan 27, 2011 4:53 am
by ray.wurlod
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.
Posted: Thu Jan 27, 2011 5:06 am
by chvenkat.v
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
Posted: Thu Jan 27, 2011 6:45 am
by chulett
Turn off the Category and Refresh options then print to file. Import into Excel and sort away.
Posted: Thu Jan 27, 2011 7:51 am
by chvenkat.v
hi chulett,
can please tell me how to do that.
Thank you,
venkat.
Posted: Thu Jan 27, 2011 8:58 am
by chulett
I did... sorry, meant you can do that in the Director client.
Posted: Thu Jan 27, 2011 9:24 am
by chvenkat.v
ok no problem
anyone can help me on this plz
Posted: Thu Jan 27, 2011 10:12 am
by surionline
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
Posted: Thu Jan 27, 2011 10:27 am
by chvenkat.v
hi
Thanks, But we have more then 60+projects in my application.
so is anyway is thier to do it automatically.
Thank you
Posted: Thu Jan 27, 2011 10:43 am
by chulett
You could easily do all this with a looping script that leverages dsjob to first get all projects then all jobs in each project.
Posted: Thu Jan 27, 2011 10:50 am
by chvenkat.v
hi
can please give me any idea about scripting.
Posted: Thu Jan 27, 2011 3:42 pm
by ray.wurlod
You can get this from the internet, for example
here
Posted: Thu Jan 27, 2011 6:30 pm
by chulett
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...