Last Error

[sonia jacob]

Hi,

My requirement is to write into a log file (.txt file) the status of each job that is run in a sequencer; if Abort then the reson for the same else if Finished OK then the rows loaded into the target or if Finished with warning then the rows loaded into the target and that which were rejected.

I have a routine that gets the handle of a job using which I tried getting the DSGetJobInfo (JobHandle, DSJ.JOBSTATUS). But it returned a value -5. I also tried DSGetLinkInfo (JobHandle, DSJ.ME, DSJ.ME, DSJ.LINKLASTERR), not hoping for any sensible values and it gave me -8.

The attach job returned a status = 0.

Am I right in my approach? Any suggestions.

Thanks,
Sonia

[roy]

Hi,
apart from miss using at least 1 of the routines you mentioned;
a job status should be gained after it has finished.
my advice is to use a job control in basic to get the info you need and write it to your log file.
this job control might be run periodically or as the last job activity run in each job sequence you have.

recheck the manuals on how to get jobstatus and link row count.

IHTH,

[kduke]

You need to do a search. These have been covered a lot. You cannot use DSJ.ME. You need to use DSAttach() and get a job handle for each job be fore you can get info on it.

[sonia jacob]

You need to do a search. These have been covered a lot. You cannot use DSJ.ME. You need to use DSAttach() and get a job handle for each job be fore you can get info on it.

I went through the routine "KgdGetLastWarning(JobName)" from "http://www.duke-consulting.com/DataStage_Tips_plain.htm". I was hoping to use the same to create my log file. There are couple of things that I did not understand
1. Did not understand why the following were done
@ID = fmt(RtLogId, "10'0'R")
@RECORD = RtLogRec
@RECCOUNT = 1

2. The routine returns all the warnings. I was hoping to get the warning of the latest run. So do I need to take the max(RtLogId)? If so then how do I need to change the select query.

The one that was originally in the Routine is as follows

Cmd = 'SSELECT ':FileName :' UNLIKE "//..." BY-DSND @ID '
if RealJobName <> JobName then
Cmd := ' AND WITH F5 LIKE ':JobName:'...'
end


Thanks

Sonia

[kduke]

There are some dictionary items which require @ID, @RECORD to be set. You could probably do this better in SQL. The last run has an id greater or equal to the one stored in '//JOB.STARTED.NO'. So you can modify this routine or here is some SQL.

Code: Select all

select
   TIMESTAMP,
   SEVERITY,
   FULL.TEXT
from
   RT_LOG111
where
   EVAL "@ID" NOT LIKE '//%'
   AND @ID >= 222
order by 
   TIMESTAMP
;

Where 111 is the job number and 222 is '//JOB.STARTED.NO'.

Code: Select all

select
   TIMESTAMP,
   SEVERITY,
   FULL.TEXT
from
   RT_LOG1370
where
   EVAL "@ID" NOT LIKE '//%'
   AND @ID > CAST(EVAL "FIELD(TRANS('RT_LOG1370','//JOB.STARTED.NO',1,'X'), @SVM, 1)" AS DECIMAL)
order by 
   TIMESTAMP
;

Does it all.

[sonia jacob]

Hi,

thanks for the SQL.

I tried commenting the running the routine again. I did not find any difference in the original output and the output after commenting. So still its unclear to me what is gained by setting @ID, @records and @recordcount

There are some dictionary items which require @ID, @RECORD to be set.

[sonia jacob]

The last post was unclear about what I had commented. So here it is again

I commented the statements of the KgdGetLastWarning(JobName) routine which were setting @ID, @RECORD and @RECCOUNT. Executed the routine again. I did not find any difference. Is there anything I am missing?

Thanks

Sonia

[kduke]

If it does not make a difference then leave them commented out. The SUBR() subroutine sometimes needs these varaibles set. I did it out of habit.

Currently this routine selects the whole log file. You need to select the records where @ID is greater than or equal to '//JOB.STARTED.NO'.

Add this line:

Code: Select all

         Cmd := " AND WITH @ID >= CAST(EVAL ":'"':"FIELD(TRANS('RT_LOG":JobNo:"','//JOB.STARTED.NO',1,'X'), @SVM, 1)":'" AS DECIMAL)'

[sonia jacob]

Stuck again!
1.
My query

SELECT SEVERITY, TIMESTAMP, FULL.TEXT, WAVE.NO, User FROM RT_LOG1 WHERE EVAL "@ID" NOT LIKE "//%" AND @ID > CAST(EVAL "FIELD(TRANS('RT_LOG1','//JOB.STARTED.NO',1,'X'), @SVM, 1)"AS DECIMAL) order by TIMESTAMP

returns 28 records when run in the administrator.

But when trying to run the same using the KgdGetLastWarning(JobName) the routine never comes to an end. I tried to come out of the routine after the execute command (using return(1)), but it did not happen.

Any suggestions as to what is happening to the execute cmd.

2. In order to set the following variable MSG.ARGS and MSG.TEXT, could you give me the column name for the appropriate data in the RT_LOGnnn file.
RtLogRec<10> I suppose referes to FULL.TEXT. But could not figure out RtLogRec<5>.

MSG.ARGS = RtLogRec<5>
MSG.TEXT = RtLogRec<10>
EventMsg = SUBR("*DataStage*DSR_MESSAGE", @ID, MSG.TEXT, RAISE(MSG.ARGS))


Thanks in advance

Sonia

[sonia jacob]

The Code is as follows

Code: Select all

* -----------------------------------------------------------------
* KgdGetLastWarning(JobName)
* Decription: Get last warning.
* Written by: Kim Duke
* -----------------------------------------------------------------
* Notes:
* -----------------------------------------------------------------
      Ans = ''
* -----------------------------------------------------------------
* initialize standard variables
* -----------------------------------------------------------------
      ProgId = 'KgdGetLastWarning'
      CrLf = char(13) : char(10)
      SearchStr = 'The value of the row is:'
      LenSearchStr = len(SearchStr)
      JobLogs = ''
      ColumnNames = ''
      Values = ''
      ValLens = ''
      open 'DS_JOBS' to DsJobsPtr else goto TheEnd
      TextSw = @false
* -----------------------------------------------------------------
      open 'DICT','RT_LOG' to DictRtLog then
        read FullText from DictRtLog, "FULL.TEXT" then
            TextSw = @true
        end
      end
      RealJobName = JobName
      if index(RealJobName, '.', 1)>0 then
         RealJobName = field(JobName, '.', 1)
      end
* -----------------------------------------------------------------
      read JobRec from DsJobsPtr, RealJobName then
         JobNo = JobRec<5>
         FileName = 'RT_LOG':JobNo
* -----------------------------------------------------------------
         open FileName to RT.LOG else goto TheEnd
* -----------------------------------------------------------------
         DblQuote = '"'
         EventNo = "CAST(EVAL " : DblQuote : "FIELD(TRANS('" : FileName : "','//JOB.STARTED.NO',1,'X'), @SVM, 1)" : DblQuote : "AS DECIMAL)"
         Cmd = 'SELECT SEVERITY, TIMESTAMP, FULL.TEXT, WAVE.NO, User FROM ' : FileName : ' WHERE EVAL "@ID" NOT LIKE "//%" AND @ID > ' : EventNo : ' order by TIMESTAMP'
         if RealJobName <> JobName then
            Cmd := ' AND WITH F5 LIKE ':JobName:'...'
         end

         execute Cmd capturing Output
         Cnt = 0

* -----------------------------------------------------------------
         loop while readnext RtLogId do

            read RtLogRec from RT.LOG, RtLogId then
               tmp = RtLogRec
               TypeNo = RtLogRec<1>
               EventTime = RtLogRec<2>
               EventUser = RtLogRec<5>
               EventType = 'Control'
* -----------------------------------------------------------------
               begin case
                  case TypeNo = 1
                     EventType = 'Info'
                  case TypeNo = 2
                     EventType = 'Warning'
                  case TypeNo = 3
                     EventType = 'Fatal'
                  case TypeNo = 4
                     EventType = 'Reject'
                  case TypeNo = 5
                     EventType = 'Control'
                  case TypeNo = 6
                     EventType = 'Reset'
                  case TypeNo = 7
                     EventType = 'Error'
                  case TypeNo = 8
                     EventType = 'Debug'
               end case
* -----------------------------------------------------------------
               if TextSw then
                   @ID = fmt(RtLogId, "10'0'R")
                   @RECORD = RtLogRec
                   @RECCOUNT = 1
*                   MSG.ARGS = RtLogRec<5>
                   MSG.TEXT  = RtLogRec<4>
*                   EventMsg = SUBR("*DataStage*DSR_MESSAGE", @ID, MSG.TEXT, RAISE(MSG.ARGS))
                    EventMsg =  RtLogRec<4>
               end else
                  EventMsg = lower(RtLogRec<10>)
                  EventMsg = change(EventMsg, '%s', JobName)
                  EventMsg = change(EventMsg, '%1', JobName)
               end
               EventMsg = change(EventMsg, char(13), '')
               EventMsg = change(EventMsg, char(10), @VM)
               NoOfLines = dcount(EventMsg, @VM)
               EventMsgSave = EventMsg
               EventMsg = trim(change(EventMsg, @VM, ' '))
               * EventMsg = lower(EventMsg)
* -----------------------------------------------------------------
               if EventType = 'Warning' or EventType = 'Fatal' or  EventType = 'Error' then
                  print 'RtLogId=':RtLogId : " Event Type " : EventType
                  for i=1 to NoOfLines
                     print i 'R#5':'. ':EventMsgSave<1, i>
                  next i
                  print
                  print
                  Found = index(EventMsg, SearchStr, 1)
                  if Found > 0 then
                     MsgLine = trim(EventMsg[Found + LenSearchStr, len(EventMsg) - Found - LenSearchStr + 1])
                     convert '=' to @FM in MsgLine
                     NoOfLines = dcount(MsgLine, @FM)
                     for i=1 to NoOfLines
                        ThisLine = trim(MsgLine<i>)
                        NoWords = dcount(ThisLine, ' ')
                        ColumnName = trim(field(ThisLine, ' ', NoWords))
                        NextLine = trim(MsgLine<i+1>)
                        NoWordsNext = dcount(NextLine, ' ')
                        Value = field(NextLine, ' ', 1, NoWordsNext-1)
                        Cnt += 1
                        ColumnNames<1, Cnt> = ColumnName
                        Values<1, Cnt> = Value
                        ValLens<1, Cnt> = len(Value)
                     next i
                     Ans = ColumnNames
                     Ans<2> = Values
                     Ans<3> = ValLens
                     goto TheEnd
                  end
               end
* -----------------------------------------------------------------
               tmp2 = ''
               tmp2<1, 1> = RtLogId
               tmp2<1, 2> = EventType
               tmp2<1, 3> = EventTime
               tmp2<1, 4> = EventUser
               tmp2<1, 5> = EventMsg
* -----------------------------------------------------------------
               JobLogs = tmp2 : @FM : JobLogs
               * if Cnt >= 100 then goto TheEnd
            end
         repeat
      end
* -----------------------------------------------------------------
      NoOfLogs = dcount(JobLogs, @FM)
      if trim(JobLogs<NoOfLogs, 1>) = '' then
         del JobLogs<NoOfLogs, 1>
         if trim(JobLogs<NoOfLogs-1, 1>) = '' then
            del JobLogs<NoOfLogs-1, 1>
         end
      end
      if trim(JobLogs<1, 1>) = '' then
         del JobLogs<1, 1>
      end
* -----------------------------------------------------------------
TheEnd:
      CLEARSELECT ALL
      for MvNo=1 to Cnt
         print MvNo 'R#5':'. ':Ans<1, MvNo>:'(':Ans<3, MvNo>:') = ':Ans<2, MvNo>
      next MvNo
      * print JobLogs
      * return

[kduke]

In BASIC there are 2 types of SELECT statements. One is a SQL SELECT. It has to end with a semicolon ";". The other has a syntax which does resemble a SQL statement. It has the form:

Code: Select all

SELECT MyHashFile WITH field = "VALUE" AND WITH field2 = "VALUE" fieldlist BY field1 BY field2

This type of SELECT can be used to feed a READNEXT id statement. Your code has the wrong type of SELECT statement now. You need to go back to the original SELECT statement and modify it. It needed a few AND WITHs added to it.

Your routine is hanging because it needs ";". You cannot process a SQL SELECT with a READNEXT.

[sonia jacob]

In BASIC there are 2 types of SELECT statements. One is a SQL SELECT. It has to end with a semicolon ";". The other has a syntax which does resemble a SQL statement. It has the form:

Code: Select all

SELECT MyHashFile WITH field = "VALUE" AND WITH field2 = "VALUE" fieldlist BY field1 BY field2

This type of SELECT can be used to feed a READNEXT id statement. Your code has the wrong type of SELECT statement now. You need to go back to the original SELECT statement and modify it. It needed a few AND WITHs added to it.

Your routine is hanging because it needs ";". You cannot process a SQL SELECT with a READNEXT.

thanks Kim,

I changed the Cmd to

SSELECT RT_LOG1 WITH EVAL "@ID" >= CAST(EVAL "FIELD(TRANS('RT_LOG1','//JOB.STARTED.NO',1,'X'), @SVM, 1)"AS DECIMAL) BY TIMESTAMP

But I get an error on "CAST". Says that its an unexpected symbol. How do I convert it into a decimal. I tired EVAL "@ID" > value. But since varchar values are being compared, I am not getting the desired output (the whole log file gets printed).

Sorry that I am being so persistant in getting my quries / problems clarified.

[kduke]

You should just read the '//JOB.STARTED.NO' record and place the value in the SELECT statement.

Code: Select all

open 'RT_LOG1' to RtLog else 
  call DsLogWarn()
end
read RtLogRec from RtLog, '//JOB.STARTED.NO' else
   RtLogRec = 0
end
StartNo = RtLogRec<1>
Cmd = 'SSELECT RT_LOG':JobNo:' WITH @ID >= ':StartNo:' BY TIMESTAMP '

[kduke]

When you get it working then please post your solution.

[sonia jacob]

You should just read the '//JOB.STARTED.NO' record and place the value in the SELECT statement.

Code: Select all

open 'RT_LOG1' to RtLog else 
  call DsLogWarn()
end
read RtLogRec from RtLog, '//JOB.STARTED.NO' else
   RtLogRec = 0
end
StartNo = RtLogRec<1>
Cmd = 'SSELECT RT_LOG':JobNo:' WITH @ID >= ':StartNo:' BY TIMESTAMP '

I am getting there. One small change would be StartNo = RtLogRec<1,1>.

Definitely I would post the code once I get it to work for me.