Job status for running

[Indrani]

Hi,
I tried to search the forum, but I didn't get it.
Iam trying it with DSJ.

1. How can I find out that the job is running and finished.
2. Is it possible to get the log files that is created and how
3. And to find the stop time of the job and the elapsed time.

Thanks
Indu

[roy]

Hi,
It depends on the context and your needs.
If you need a command line to be run from outside DS then seach for the dsjob command line utility.
If you need an internal DS job to get this info you need to build some routines or job controll an utilies some of the DSJob various routines.

All this is in the docs on your client...
And a search away in theese forums.

IHTH,

[Indrani]

Iam trying to do it within a DS job. Could you please tell me the functions to be used for those.

Indu

[rajeevs]

If you are using sequencer -- enable the option to include job status on the job activity stage.. This will give u the log which should also indicate the time it took for it to run !!

[Indrani]

I have to load a table and not to a notification activity as of now.

[djm]

Indrani,

My suggestion depends on which of the following you are trying to achieve:

1) Get information about a particular job.
A) Try looking at the documentation on the "dsjob" command in the manuals. This command will allow you to output different flavours of information about a job and the detail you are after may be available via this command. You could capture the output and process it to a format suitable for loading into the table you mention.

2) Get information about many jobs
A) Various posts have been made in the past identifying a solution based around selecting from RT_LOG* hashed files. Alternatively, I have posted two solutions based around DataStage jobs that you may find useful. The first interprets the Director Log to providing job elapsed timing. The second creates dumps of the entire log for one or more jobs. You may be able to use these jobs directly or use them as a basis for your own solution.

3) Neither of the above is what you are trying to achieve.
A) Can you try to clarify what you are try to achieve and someone else may contribute their answer.

Thanks.

David

[Titto]

Indrani,

for the first one
Try this routine

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H 
* ----------------------------------------------------------------------- 
      JobStatus = '' 
* ----------------------------------------------------------------------- 
      OldJobName = JobName 
         RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE) 
         JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS) 
* ----------------------------------------------------------------------- 
         begin case 
            case JStat = 0 
               JobStatus = 'Running' 
						case JStat = 1 
               JobStatus = 'Finished' 
            case JStat = 2               ; * Finished with warnings 
               JobStatus = 'Finished' 
            case JStat = 3 
               JobStatus = 'Aborted' 
            case JStat = 11 
               JobStatus = 'Finished' 
            case JStat = 12              ; * Validated with warnings 
               JobStatus = 'Finished' 
            case JStat = 13 
               JobStatus = 'Finished' 
            case JStat = 21 
               JobStatus = 'Finished' 
            case JStat = 96 
               JobStatus = 'Aborted' 
            case JStat = 97 
               JobStatus = 'Stopped' 
            case JStat = 98 
               JobStatus = 'Finished' 
            case JStat = 99 
               JobStatus = 'Finished' 
         end case 
Ans = JobStatus

For the second one
Use

Code: Select all

 DSGetLogSummary 

function

For the Third one
Try this routine

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H 
*-------------------------------------------------------------------------------
         JobStatus = ' '
         OldJobName = JobName 
         RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE) 
         JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS) 
           StartedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBSTARTTIMESTAMP) 
            iStartedDate = iconv(StartedDateTime[1,10], 'D4-YMD') 
            iStartedTime = iconv(StartedDateTime[12,8], 'MT') 

            if JobStatus = 'Running' then 
               FinishedDateTime = oconv(date(), 'D4-YMD'):' ':oconv(time(), 'MTS') 
            end else 
               FinishedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBLASTTIMESTAMP) 
            end 
            iFinishedDate = iconv(FinishedDateTime[1,10], 'D4-YMD') 
            iFinishedTime = iconv(FinishedDateTime[12,8], 'MT') 
            
            * print iFinishedTime:' - ':iStartedTime 
            if FinishedDateTime<>'' and StartedDateTime<>'' and iFinishedTime<>'' and iStartedTime<>'' then 
               ElapsedTime = ((iFinishedDate-iStartedDate)*60*60)+iFinishedTime-iStartedTime 
            end 
            if ElapsedTime<0 then 
               ElapsedTime = '' 
            end
            ElapsedTimeMin = ElapsedTime/60
          Ans1 = ElapsedTime : "    Seconds" : "  --OR--  " :  ElapsedTimeMin : "    Minutes"
         
          Ans2 = FinishedDateTime
          Ans = Ans1:' Last Run Date-N-Time :- ' : Ans2
*-----------------------------------------------------------------------------------

you can write the output to a file using OpenSeq and WriteSeq with in the same routines. Hope this helps U !

[Indrani]

Thanks titto for the routines.
Since I haven't used routines. Could you please tell me how to create a job to use this routines. I know how to use it inside the transformer.

But what will be my first stage to get the job name. I have also seen a routine to get the jobname. I have searched a forum, and the job should be like
Hash File --> Transformer ---> DB/Flat file.

How can I call the routine/get the job name.

What I actually need in my job, is to load the table having these columns:
1. Job Name
2. Valid rows in the inputlink,
3. Rejected rows.
4. Error type(warning, aborted, finished with warnings)
5. Error Description(that is collected in the log file)
5. The job is running(processing) / finished running.
6. start time
7. stop time.

Please explain me.

Thanks
Indrani

[kumar_s]

HI,
You can use dsjob -ljobs <project_name> to get the total list of jobs in your project.
And this can be maintained in a file. So that the routine can be called for indivual jobs in transformer.

-Kumar

[ray.wurlod]

Is there a convention for naming the input link? If not, how do you propose to create an algorithm to request the row count from the input link? (Do it in English - we can get it into code later).

Create a job that is a job control routine only. Here is its algorithm (not its code):

Code: Select all

For each jobname in (list of job names in project)
  If jobname <> thisjob
    attach job
    determine input link name
    get input link row count (DSGetLinkInfo)
    determine rejected rows link name
    get rejected rows link row count
    determine event number of most recent job start (DSGetNewestLogId)
    For each event after that
      get event detail
      If severity is warning or fatal
        get error text
      End If
    End For
    determine job status
  End If
  Write information to file
End For

[Indrani]

Hi
Kumar thanks, I will do it the same way.

Ray, its the same i need to create. But I have naming conventions in our jobs. But all the link name are different for each an every job.

If the input link, the output link and reject link should have the same name, then i can change it in each an every job.

This is the way I have created
1.Seq1 (calls seq1 and seq 2)
1.seq2 (calls Job 1 and Job 2)
Job1
Job2
2 .Seq3 (Calls Job3)
Job3

Shd, I call the job only once after the whole Sequencer is completed.
I also need to get the sequencers name for the job1 ie for job 1, the seq1 and seq2.

Please let me know and also the routine for it.

Thanks
Indrani

[ray.wurlod]

Find a list of the active stage names in a job with DSGetJobInfo() and find the stage name you require in that list. Find a list of the links connected to that stage using DSGetStageInfo() and find the link name that you require in that list. Use DSGetLinkInfo() to determine the link row count.

Alternatively, find the "active stage finishing" event in the log (DSGetLogSummary) and retrieve the row counts from the detail of that event (DSGetLogEvent).

For me to write the code for you would have to be a commercial transaction. Please revert off line if that's what you want.

You should only need to invoke the job once. However, if you do so after the entire sequence has finished you will never see a status of running. The other possibility is to run the reporting job at regular intervals, or on demand if that suits your needs better.

[Indrani]

Thanks for your reply. Could you please, tell me. How to get the job names

I got this in the forum :

"Code:
Call DSExecute("UV","SELECT NAME FMT '60R' FROM DS_JOBS WHERE NAME NOT LIKE '%\%';",output,sysretcode)
Your output variable "output" will contain a multivalued list of the jobs located."

I still couldn't get, what to write in the output, should i write the job name. "

What is sysretcode?


How can I use this routine in the job. I know we should use it in the transformer.
But What will be my first stage in that what should i do(in case if it is sequential file, what file name can i use).
Please explain

How can I pass this job name as an argument in the following routine given by titto

$INCLUDE DSINCLUDE JOBCONTROL.H
*-------------------------------------------------------------------------------
JobStatus = ' '
OldJobName = JobName
RunHandle = DSAttachJob(OldJobName, DSJ.ERRNONE)
JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
StartedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBSTARTTIMESTAMP)
iStartedDate = iconv(StartedDateTime[1,10], 'D4-YMD')
iStartedTime = iconv(StartedDateTime[12,8], 'MT')

if JobStatus = 'Running' then
FinishedDateTime = oconv(date(), 'D4-YMD'):' ':oconv(time(), 'MTS')
end else
FinishedDateTime = DSGetJobInfo(RunHandle, DSJ.JOBLASTTIMESTAMP)
end
iFinishedDate = iconv(FinishedDateTime[1,10], 'D4-YMD')
iFinishedTime = iconv(FinishedDateTime[12,8], 'MT')

* print iFinishedTime:' - ':iStartedTime
if FinishedDateTime<>'' and StartedDateTime<>'' and iFinishedTime<>'' and iStartedTime<>'' then
ElapsedTime = ((iFinishedDate-iStartedDate)*60*60)+iFinishedTime-iStartedTime
end
if ElapsedTime<0 then
ElapsedTime = ''
end
ElapsedTimeMin = ElapsedTime/60
Ans1 = ElapsedTime : " Seconds" : " --OR-- " : ElapsedTimeMin : " Minutes"

Ans2 = FinishedDateTime
Ans = Ans1:' Last Run Date-N-Time :- ' : Ans2
*-----------------------------------------------------------------------------------

[ray.wurlod]

The fourth argument to DSExecute is the system return code; for SQL it returns the number of records processed or, if a negative integer, an error code.

Job control routines of the kind we're talking about are not usually created in the Routines branch but, rather, as code on the Job Control tab of a server job.

The SQL query returns a result set of job names in a dynamic array. You process these in a loop using REMOVE to get one at a time.

Code: Select all

SQLQuery = "SELECT NAME FMT '60L' FROM DS_JOBS WHERE NAME NOT LIKE '%\%';"

Call DSExecute("UV", SQLQuery, JobNameList, ExitStatus)

Loop

   Remove JobName From JobNameList Setting MoreJobs

   hJob = DSAttachJob(JobName, DSJ.ERRNONE)

      If hJob <> DSJ.ME
      Then

         * processing for individual job name

      End  ; * of test to exclude current job

   Ignore = DSDetachJob(hJob)

While MoreJobs
Repeat

Perhaps that will get you started. Consult Help or the DataStage BASIC manual for any statements with which you are unfamiliar.

[sudheer05]

HI Indrani,
Can you please explain me how did you solve loading the table having these columns:
1. Error type(warning, aborted, finished with warnings)
2. Error Description(that is collected in the log file)

Please explain me.