Identify Jobs that comples with warning

dsuser_cai · Post by **dsuser_cai** » Wed Apr 22, 2009 11:23 pm

Hi

We have nearly 200 to 250 jobs (server jobs and parallel jobs) that run everyday, ymy question is, is there any way we can create a job that will identify the jobs (job names) that completed with status Finished / Finished with warning / Aborted and load them in a table or send a report as email. Also if the jobs have some warning then it should give the warning message. For example the output should be having a columns like Job_Name, Status (OK/Warning/Aborted) and Description (this should include the warning and error messages).

chulett · Post by **chulett** » Wed Apr 22, 2009 11:31 pm

You were doing fine up until that last little bit. Getting the first part is easy and a simple script can get a list of the job names in a project then iterate through them to check and record the status of each. All that using 'dsjob'. Write the results out to a flat file and then either load it to a table or email it. Or both.

Adding in log messages complicates things but doesn't make it impossible. For a job found to not be in an "OK" status, you could do further dsjob checks of the specific contents of their logs and pull out warnings and/or errors if you like.

All of the functions you can use for this are documented in either of the two Developer's Guide pdfs in the Command Line Interface chapter.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 23, 2009 12:24 am

And you can also do it with a job, which was your original question, but you will need to write a couple of routines to gather information as each job is processed through your new job (which must be smart enough not to process itself, lest you introduce infinite recursion).

chulett · Post by **chulett** » Thu Apr 23, 2009 8:03 am

True... not sure why I went down the script path, but either is valid and would use the same techniques with the caveat Ray mentioned for the job approach.

chulett · Post by **chulett** » Thu Apr 23, 2009 2:21 pm

First off, it's not SQL though it looks like it is, so there's no Oracle or Toad or sqlplus for this. You need to run it in the "Universe" repository and that means either manually using the Command window of the Administrator (or from a TCL prompt) or in a job by using the UV stage set to "localuv".

Also note that your query gets you the job number which you don't need for this and only works for one job at a time. For your task, use a more basic query to get a list of all job names in the current project:

Code: Select all

SELECT NAME FROM DS_JOBS

Off the top of my head, you'll get more than just jobs, meaning control records will be included as well. You can either filter them out in the query directly or constrain them out in a downstream transformer. I don't recall exactly what they will look like, "\\" something or other, but they will be obvious when you run the query.

Then with the name you can establish a handle by attaching to each job, then use that handle to pull the information that you need. Don't forget two very important things: 1) You current job will also get selected in the list, do not attempt to attach to it. And 2) always detach each handle when you are through with it. Me, I would put all this in a routine - pass in the job name and pass back whatever you need, delimited if there are multiple values and parse the results - rather than trying to work directly inside a derivation.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 23, 2009 2:42 pm

If you compile with Multi Job Compile tool you can get a report at the end of which jobs compiled OK, which compiled with warnings, and which failed to compile.

dsuser_cai · Post by **dsuser_cai** » Thu Apr 23, 2009 2:43 pm

Thank you so much, i will go through the pdf guides.

chulett · Post by **chulett** » Thu Apr 23, 2009 3:00 pm

Well... that "comples" in the subject is a typo and should say "complete" not "compile", but I would wager you knew that.

dsuser_cai · Post by **dsuser_cai** » Thu Apr 23, 2009 3:18 pm

chulett wrote:Well... that "comples" in the subject is a typo and should say "complete" not "compile", but I would wager you knew that.

Im Sorry about that. Next time I will make sure this dosent happen.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 23, 2009 4:42 pm

"compiles" is a closer match than "completes" (has QualityStage probabilistic matching hat on).

For "completes" the easy solution is in Director; disable display of categories in the View menu then sort by Status and/or filter on Status. Print report (perhaps to file) from the Project menu.