Unix Sript calling in After Sub routine

[manojbh31]

Hi All,

I have unix script which get me the jobinfo like start time, end time and status of the job. I am using this script in after sub routine in one of the multi instance job. I am passing job name and invocation id as parameter through the job, Issue is the instance is finished but the status returned by script is showing as running. Can anybody help on this.

[PaulVL]

The "after job" section is still part of the job, just after the regular canvas ETL flow. So yes, the status of the job is still running since your measurement is taken during the execution.

You might want to try a sequence or potentially execute the script in your main shell script outside of the datastage execution.

[chulett]

You can check for "job interim status" at that point to know if the job is going to finish OK or abort after it completes the after job section... i.e. if there was a problem with the job itself.

[manojbh31]

Hi Paul,

Thanks for your response, As my job is multi instance, i want to capture status for each instance, If i call the script at the end of the seqeuce by using execute command i cannot achieve my requirement. I want to capture the status for each instance, how this can be done.

Appreciate your help

[chulett]

As I said:

DSJ.JOBINTERIMSTATUS Returns the status of a job after it has run all stages and controlled jobs, but before it has attempted to run an after-job subroutine. (Designed to be used by an after-job subroutine to get the status of the current job).

[manojbh31]

Craig,

Sorry to ask silly question what should i select in drop down for after sub routine to use DSJ.JOBINTERIMSTATUS?

[chulett]

Sorry, forgot you said you were calling a script. I assume you are using dsjob -jobinfo to get the information you are after? Unfortunately it doesn't show the interim status. I'm curious, what does the script do once it has the information that it needs, write it to a file perhaps?

You'd need to write a routine that uses the API instead, call DSGetJobInfo with the info_type posted earlier. You can also use DSJ.JOBSTARTTIMESTAMP to get the start time and the current time for the end time. There is also a DSJ.JOBLASTTIMESTAMP but I'm not sure what exactly constitutes the 'last' timestamp from reading the documentation.

From the routine you should still be able to output the data you've gathered as needed.

[manojbh31]

First step in script to get the jobinfo like starttime, endtime, job status, jobname and seq name. Once this is done, script will send mail if the job status is other then RUN OK, then next is to load the above stats into table.

[chulett]

Since we don't know how you are running these jobs, it's hard to make good suggestions. At this point I would say a Sequence could do all that. Your after-job script is never going to know anything other than the job is still RUNNING, as you've found.

Your script could be run by an after-job routine. First it could check the interim status as noted and then use DSExecute to run your script, passing the status as an argument so the script knows what it needs to do from there.

[kduke]

I have said over and over I hate after job routines. When they fail the sequence seems to quit for no reason. The job looks successful in the log. This way also slows down the job stream. Sometimes it takes longer to get row counts and save them into tables than the job takes to run.

Get row counts in the background. So either phantom off the job getting row counts and not wait for it or get row counts in batches like get row counts for all jobs in sequence. You can ask a multi-instance job all its instance ids and loop through and get all row counts for each instance. There are lots of ways of solving this problem. Always do it in the background and not wait for the get row count job to finish. If it fails who cares. You have serious problems if a job fails that writes one row per link. You are probably out of disk space and it will show up somewhere. This database with row counts in it is probably not important. So let it fail. Check it later.

Make your ETL run as fast as possible. Do not slow it down waiting on row counts or any other audit process. Optimize what is important. Make everything as solid as possible but do not sweat the little stuff.

[chulett]

Oh, I completely agree... just answering the question as asked.

I've advocated here before several times your same approach, disconnect any status or stats gathering from the jobs themselves and do it all as a 'post' process. I don't want failures in that gathering effort to interrupt the loads, let them run to completion and then collect any status and statistics after the fact at your leisure. Never mind that when things are that tightly coupled and it's all done 'after job' there isn't necessarily a good way to just execute that part without rerunning the entire job.

As to the specifics, as noted there are many different ways to skin that cat.

[manojbh31]

Hi Criag,

I used execute command in the sequence to get the status for each instance.

[ray.wurlod]

You could have used an activity variable from the Job activity, and avoided the overhead of creating and invoking an operating system process. Just a thought.

[kduke]

Guess we need to keep saying it till they do it.