How do I store job status information in table?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
How do I store job status information in table?
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?
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?
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
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
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
Regards
LakNar
LakNar
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
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
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
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"
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:
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 104
- Joined: Thu Jul 12, 2007 11:32 pm
- Location: Canada
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.
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.
-
- Premium Member
- Posts: 34
- Joined: Fri May 16, 2008 6:24 am