DSXchange

Posted: **Thu Jun 30, 2005 12:16 pm**

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

Posted: **Thu Jun 30, 2005 2:28 pm**

Search for DSGetJobInfo.

Posted: **Thu Jun 30, 2005 3:30 pm**

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.

Posted: **Fri Jul 01, 2005 7:06 am**

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

Posted: **Fri Jul 01, 2005 8:34 am**

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)

Posted: **Fri Jul 01, 2005 8:45 am**

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.

Posted: **Fri Jul 01, 2005 7:04 pm**

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).

Posted: **Sat Jul 02, 2005 10:05 am**

Ray

This is a job except the whole SQL statement is sent to the next job. I was trying to explain it so JobNo, StartId and EndId are the parameters.

Posted: **Sat Jul 02, 2005 7:23 pm**

I think I was trying to say that the design

Code: Select all

UV stage  --->  Transformer stage  --->  Sequential File stage

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.

DSXchange

Getting DS log messages from DS and into Oracle.

Getting DS log messages from DS and into Oracle.