How do I store job status information in table?

[adityavarma]

Hi All,

I have a requirement where in i need to update a table, if the job is failed or success a table need to be updated with the below fields to be populated.
JOB_SEQ_START_DT
JOB_SEQ_END_DT
DURATION
STATUS (OK/KO)

Split job --> Tranfm job --> Load job.

Can you please suggest me on how to proceed on this?

[priyadarshikunal]

Do you need to insert records in the table after each job?

Not a bad idea, but you need to execute a routine after each job. As after job sub-routine or routine in the sequence. You can manipulate the code found at Kim Duke's website as per your needs.

Can you opt for inserting all entries after all the jobs finishes its execution?

In this case Just use Kim Duke's ETLStats which gives you almost all the details you may need to log about the jobs (No need to reinvent the wheels). In this case also, you may need to manipulate it as per your needs.

[laknar]

Exception activity-------> Job Activity(Status Update Job in Table)........> Terminator Activity for Failed Jobs.


For successfull completion. Call Job Activity(Status Update Job in Table) at end of the activity.

so your design will be like below.


Job Activity(Actual Job)--->Job Activity(Status Update Job in Table)
Exception activity---> Job Activity(Status Update Job in Table)..>Terminator Activity

[adityavarma]

We are thinking of implementing a routine for each sequence, once it is failed the table will be updated and if it is successful, at the end a routine to update the status ok.

@kunal,
We could not find any routine corresponding to this in the duke's website.

Can you please suggest us on this.

[chulett]

ETL Stats is in the Tips & Tricks section of Kim's site.

[kduke]

Click on icon below my sig.

[adityavarma]

Thanks duke,

I am able to go through your site, but i am not able to find out the correct routine which is required for my requirement.

I am new to routines and may be this is one reason i am not able to find it out.

Thinking of writing a routine, but i am stuck in how to connect to oracle database to update the status in the table and get the start time and end time.

i am very much new to routines, i just tried one basic routine, but not able to compile it.

Jobstat = DSGetJobInfo(jobname, DSJ.JOBSTATUS)
If Jobstat = DSJS.RUNFAILED
then
Ans = 1

Jobstat = DSGetJobInfo(jobname, DSJ.JOBSTATUS)
If Jobstat = DSJS.RUNWARN
then
Ans = 1

Jobstat = DSGetJobInfo(jobname, DSJ.JOBSTATUS)
If Jobstat = DSJS.RUNOK
then
Ans = 0


IF ANS = 1
then
sqlplus -l UNAME/PWD@DATABASE

INSERT statement

exit;

IF ANS = 0
then
sqlplus -l UNAME/PWD@DATABASE

INSERT statement

exit;


Can you please suggest me on this

[ArndW]

The "IF-THEN-ELSE" construct functions in that if you have the format "IF (condition) THEN (statement)" then only one statement is executed and you don't need an "END", but your format has the "THEN" at the end of the line, which means that all the statements until the next matching END are part of the condition. Your compile error is due to that. Change your lines to read "... THEN Ans = 1"

[ArndW]

I just read the rest of the program. It won't work at all, there are sevearl problems with it, including that DataStage BASIC has no idea of what commands such as "sqlplus" are...

Here is a modified version:

Code: Select all

   JobHandle = DSAttachJob(jobname,DSJ.ERRNONE)
   Jobstat = DSGetJobInfo(JobHandle, DSJ.JOBSTATUS) 
   IF Jobstat = DSJS.RUNOK 
   THEN
      Ans = 0
      Script = '/path/to/script.sh'
   END
   ELSE
      If Jobstat = DSJS.RUNFAILED OR Jobstat = DSJS.RUNWARN THEN Ans = 1
      Script='/path/to/otherscript.sh'
   END
   EXECUTE 'SH -c "':Script:'"' CAPTURING ScreenIO RETURNING ErrorCode
   Dummy = DSDetachJob(JobHandle)

[ray.wurlod]

A bit obscure to introduce the SH verb, Arnd. A call to DSExecute can go directly to the operating system shell.

[ArndW]

You are correct, Ray. Just force of habit from ye olde UniVerse days which had no DSExecute() call.

[adityavarma]

Instead of using routine for the above requirement, can we use a shell script, i thought of doing but

dsjob -report <Projectname> <JobName> BASIC > log.txt


**************************************************
STATUS REPORT FOR JOB: <JobName>
Generated: 2010-06-29 10:06:45
Job start time=2010-06-29 08:42:38
Job end time=2010-06-29 08:42:43
Job elapsed time=00:00:05
Job status=1 (Finished OK)

The above is the output when i executed the dsjob command

i am not sure how to pass the project and jobname to the shell script.
and take these details and update the table.

Please suggest me on this.

[laknar]

The project name and Jobname can be passed through DS Macros.

Execute the script using Execute Command Activity Stage and Pass the required parameters.

Derive the project name and Jobname prior to the Execute Command Activity.

[kduke]

It is not a routine. It is a job. The job takes dsjob -report XML and outputs it to a JobName.xml file in the before routine. Next it processes this into 3 tables ETL_JOB_HIST, ETL_ROW_HIST and ETL_PARAM_HIST. The DDL is included.

[varaprasad]

One way is, write a shell script to update your Oracle table and call that script using the DSExecute() function. Pass the captured values as parameters to this call.