Getting DS log messages from DS and into Oracle.
Moderators: chulett, rschirm, roy
Getting DS log messages from DS and into Oracle.
I have a business requirement to gather datastage event information and load it into an Oracle database. To be more specific, as a job runs or immediately after it has finished, we need to pull the log messages for Rows processed, run time, error codes, error messages and so forth from the ds repository and load them into an oracle database.
Is there a way to pull that information fom DS at runtime, or has anyone done anything similiar?
Any and all info would be appreciated
zam
Is there a way to pull that information fom DS at runtime, or has anyone done anything similiar?
Any and all info would be appreciated
zam
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
You should be able to use a UV stage and write a job to extract from the RT_LOGnnn file to update Oracle. You need a batch job to feed the job number nnn to the UV stage.
select * from RT_LOG#JobNo#;
You need to trick DataStage to get the metadata. You need to create a q-pointer. It should not import directly from RT_LOG123. So create a VOC record like:
RtLog
1: Q
2:
3: RT_LOG123
You should be able to
SELECT * FROM RT_LOG123;
All of this can be done with the ED command at the TCL prompt when you telnet into DataStage.
I think your Oracle key should be job name and timestamp. That way you can load the whole file and not duplicate log entries. If you want to select only the last run then that is a little tricky. If you want this code then let me know. I will dig it out. In fact it is part of EtlStats on my tips page. I do a similar thing only I email the log records from the last job run in a job called GenHtmlJobLog.
select * from RT_LOG#JobNo#;
You need to trick DataStage to get the metadata. You need to create a q-pointer. It should not import directly from RT_LOG123. So create a VOC record like:
RtLog
1: Q
2:
3: RT_LOG123
You should be able to
SELECT * FROM RT_LOG123;
All of this can be done with the ED command at the TCL prompt when you telnet into DataStage.
I think your Oracle key should be job name and timestamp. That way you can load the whole file and not duplicate log entries. If you want to select only the last run then that is a little tricky. If you want this code then let me know. I will dig it out. In fact it is part of EtlStats on my tips page. I do a similar thing only I email the log records from the last job run in a job called GenHtmlJobLog.
Mamu Kim
Mr Kim.
If you could get the source, or at least some of it, that would be wonderful! In the mean time, I will investigate the options that you have suggested! Thanks
email: zam62@yahoo.com
If you could get the source, or at least some of it, that would be wonderful! In the mean time, I will investigate the options that you have suggested! Thanks
email: zam62@yahoo.com
Here is the core of it:
Code: Select all
open "DS_JOBS" to DsJobs else stop
read JobRec from DsJobs, JobNameToLog else
Call DSLogWarn(JobNameToLog:" not in DS_JOBS.", "JobControl")
goto TheEnd
end
JobNo = JobRec<5>
JobCategory = JobRec<3>
FileName = JobNameToLog:'_Log'
* -----------------------------------------------------------------
* Build Sql
* -----------------------------------------------------------------
SqlStmt = ''
SqlStmt< 1> = 'select'
SqlStmt< 2> = ' TIMESTAMP,'
SqlStmt< 3> = ' SEVERITY,'
SqlStmt< 4> = ' FULL.TEXT'
SqlStmt< 5> = 'from'
SqlStmt< 6> = ' RT_LOG':JobNo
SqlStmt< 7> = 'where'
SqlStmt< 8> = ' EVAL "@ID" NOT LIKE ':"'//%'"
* -----------------------------------------------------------------
* Get only MaxRecs of the last run
* -----------------------------------------------------------------
if not(DebugSw) then
HashFileName = "RT_LOG":JobNo
open HashFileName to RtLog then
RtLogId = '//JOB.STARTED.NO'
read RtLogStartRec from RtLog, RtLogId else
RtLogStartRec = 0
end
RtLogId = '//SEQUENCE.NO'
read RtLogEndRec from RtLog, RtLogId else
RtLogEndRec = 1
end
StartId = RtLogStartRec<1,1>
EndId = RtLogEndRec<1,1> - 1
if (EndId - StartId) > MaxRecs then
SqlStmt<-1> = " AND @ID BETWEEN ":StartId:" AND ":(StartId+10):" "
SqlStmt<-1> = " AND @ID BETWEEN ":(EndId-(MaxRecs-11)):" AND ":EndId:" "
end else
SqlStmt<-1> = " AND @ID >= ":StartId:" "
end
end else
Call DSLogWarn(HashFileName:" is empty.", "JobControl")
goto TheEnd
end
close RtLog
end
SqlStmt<-1> = 'order by '
SqlStmt<-1> = ' TIMESTAMP'
SqlStmt<-1> = ';'
* convert char(13):char(10):@VM:@FM:@SVM to " " in SqlStmt
SqlStmt = change(SqlStmt, @FM, char(13):char(10))
Call DSLogInfo("SqlStmt: " : SqlStmt, JobName)
* -----------------------------------------------------------------
* Setup GenHtmlFromSql, run it, wait for it to finish, and test for success
* -----------------------------------------------------------------
hJob1 = DSAttachJob("GenHtmlFromSql.":FileName, DSJ.ERRFATAL)
Mamu Kim
The '//JOB.STARTED.NO' record stores a multivalued list of runs. The first one is the last run StartId = RtLogStartRec<1,1>. The end record is based on the last record in the file which is stored in '//SEQUENCE.NO'. You need to subtract one from this. I did not want to email more than 100 records. So I throttle it down using MaxRecs.
You could use these as parameters.
select TIMESTAMP,SEVERITY,FULL.TEXT from RT_LOG#JobNo# where EVAL "@ID" NOT LIKE '//%' AND @ID BETWEEN #StartId# AND #EndId#;
Please post your solution.
Thanks Kim.
You could use these as parameters.
select TIMESTAMP,SEVERITY,FULL.TEXT from RT_LOG#JobNo# where EVAL "@ID" NOT LIKE '//%' AND @ID BETWEEN #StartId# AND #EndId#;
Please post your solution.
Thanks Kim.
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I think it would be much easier to create a DataStage job to perform this task. The job number would have to be a job parameter. You would also probably need a parameter (maybe the same one) to identify the job in your Oracle table(s).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I think I was trying to say that the design
could perform the required task without much (any?) code.
Table name in the UV stage is RT_LOG#JobNumber#
WHERE condition in the UV stage is TIMESTAMP > '#JobStartTimeStamp#'
Both of these can be retrieved using DSGetJobInfo() in the job sequence or in job control code.
Code: Select all
UV stage ---> Transformer stage ---> Sequential File stage
Table name in the UV stage is RT_LOG#JobNumber#
WHERE condition in the UV stage is TIMESTAMP > '#JobStartTimeStamp#'
Both of these can be retrieved using DSGetJobInfo() in the job sequence or in job control code.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.