Purging Job Logs - Query regarding Code being used

droberts · Post by **droberts** » Tue Sep 08, 2009 2:40 am

Hi,

I did some searching on how to clear job logs en masse and found a number of useful posts. I found some code by Ken Bland (thanks Ken

) but I can't seem to get it to work. I am using a Datastage Parallel Job. The code has been cut and paste into the Job Control tab under Job properties and the two Job Parameters used (Folder and SearchString) are set as below.

I used the Job Control tab as the original instruction was to cut and paste the code into the job and this is the only way I can see of doing that without creating a routine:

Parameters:

Folder is blank
SearchString is JP_01_RTCOL_CEN_LAND_010_ACC_FACTORS

The SearchString above is a valid jobname.

Job Control Code:

TCL = 'SSELECT DS_JOBS'

If SearchText Then TCL:= ' LIKE "':SearchText:'"'
If Folder Then TCL:= ' WITH F3 = "':Folder:'"'

Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode)
Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg")

OPEN "DS_JOBS" TO JobsXrefTable Else
Call DSLogFatal("Unable to open DS_JOBS", "Msg")
End

JobList = ""
AllJobsDone = @FALSE
Loop
Readnext JobName Else AllJobsDone = @TRUE
Until AllJobsDone Do
If LEFT(JobName,2) # "" Then
Read Row From JobsXrefTable, JobName Then
JobList = JobName:"|":Row
End
End
Repeat

Call DSLogInfo("Processing the following jobs: ":JobList, "Msg")

JobCount = Dcount(JobList,@AM)
For Job = 1 TO JobCount
JobName = FIELD(JobList, "|",1)
TableName = "RT_LOG":FIELD(JobList, "|",2)
TCL = "CLEAR.FILE ":TableName
Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode)
Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg")
Next Job

Log Output from Job Run:

Starting Job JP_99_Clear_Job_Logs.
SearchText = JP_01_RTCOL_CEN_LAND_010
Folder =

JP_99_Clear_Job_Logs..JobControl (Msg): TCL Output:
1 record(s) selected to SELECT list #0.

JP_99_Clear_Job_Logs..JobControl (Msg): Processing the following jobs: JP_01_RTCOL_CEN_LAND_010_ACC_FACTORS|NRO

\RTCOL\Jobs\01-Landing
Land contributory factors data in native format for Central RTC
486
3

Attempting to Cleanup after ABORT raised in job JP_99_Clear_Job_Logs..JobControl

The code is picking up the jobname as it is selecting one record. I don't really know what the '|NRO' immediately after the message "Processing the following jobs: JP_01_RTCOL_CEN_LAND_010_ACC_FACTORS|NRO" means and whether this indicates anything.

However, It isn't clear to me why the job is aborting. The joblog in DS Director for this job has a number of entries.

I can't claim to understand what the code is doing 100% but I know that the 486 in the job log snippet above equates to the correct RT_LOG entry. I believe the 3 immediately after is the system return code but cannot be sure.

Any help on deciphering the above would be appreciated.

Thanks,

Daren

ArndW · Post by **ArndW** » Tue Sep 08, 2009 5:39 am

The dynamic variable contains the key (job name) then the record data separated with a pipe, which is why that is being displayed - the "NRO" is the first data column and means that the job is not read only. So you can ignore that display, it indicates that the job is working correctly.

You could modify the last loop to read:

Code: Select all

JobCount = Dcount(JobList,@AM) 
For Job = 1 TO JobCount 
   JobName = FIELD(JobList, "|",1) 
   TableName = "RT_LOG":FIELD(JobList, "|",2) 
   TCL = "CLEAR.FILE ":TableName 
   CALL DSLogInfo('Clearing log from job "':JobName:'".','')
   Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode) 
   Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg") 
Next Job

So that you will know which "CLEAR.FILE" command caused the abort. My guess is that the file is broken, which causes the clearing job to abort as well, since that error isn't handled in the job.

droberts · Post by **droberts** » Tue Sep 08, 2009 6:01 am

Hi ArndW,

Thanks for that. I made the change and get this extra message in the log - the job still aborts however:

JP_99_Clear_Job_Logs..JobControl (): Clearing log from job "JP_01_RTCOL_CEN_LAND_010_ACC_FACTORS".

So it seems to be failing on either one of the two last calls?

Thanks,

Daren

droberts · Post by **droberts** » Tue Sep 08, 2009 6:09 am

I have added some logging and interestingly it is setting the TableName variable to be RT_LOGNRO, which is incorrect - it should be 486.

Why is NRO being set within here??

Daren

ArndW · Post by **ArndW** » Tue Sep 08, 2009 6:14 am

Umm... are you sure that this code is working, even for the other logs? I would use the line

Code: Select all

TableName = "RT_LOG":FIELD(JobList, "|",2)<5>

This uses the 5th field of the data, which contains the job number. The more I look at it, the more I think that the original code would never find the right number.

droberts · Post by **droberts** » Tue Sep 08, 2009 6:25 am

ArndW wrote:Umm... are you sure that this code is working, even for the other logs? I would use the line
Code: Select all
TableName = "RT_LOG":FIELD(JobList, "|",2)<5>
This uses the 5th field of the data, which contains the job number. The more I look at it, the more I think that the original code would never find the right number.

Hi,

No, I found the code today on DSXchange and I am trying to get it to work. I haven't used TCL before, hence my very limited understanding.

That code you have supplied now works and deletes the correct log, so thanks,

However....

The JobCount is being set at 6 - but there is only one RT_LOG486 directory.

The line that sets the JobCount is:

JobCount = Dcount(JobList, @AM)

Any ideas why it's set to 6 - it does this for other logs regardless of how many runs are retained in the log prior to deletion.

Thanks,

Daren

ArndW · Post by **ArndW** » Tue Sep 08, 2009 7:14 am

That means that there are 6 entries (or jobs) in your DS_JOBS file. Thus the outer loop executes for each job, then the code retrieves the corresponding job number for using the substring/extraction. The string has a pipe symbol to denote the name and the data part, thus the FIELD(JobList...
Hmm, I just realized that the code won't work in last loop either. I don't know where you found this, but it just won't work. Let me post the changes:

Code: Select all

JobCount = Dcount(JobList,@FM) 
For Job = 1 TO JobCount 
   JobName = FIELD(JobList<Job>, "|",1) 
   TableName = "RT_LOG":FIELD(JobList<Job>, "|",2)<5>
   TCL = "CLEAR.FILE ":TableName 
   Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode) 
   Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg") 
Next Job

ArndW · Post by **ArndW** » Tue Sep 08, 2009 7:27 am

I just went over the rest of the code. It won't run at all, and it cannot have ever run. Although slight changes would make it work, try:

Code: Select all

OPEN '','DS_JOBS' TO DSJobsFilePtr ELSE CALL DSLogFatal('Unable to open DS_JOBS file, status is ':STATUS(),'')
SELECT DSJobsFilePtr TO 1
Finished = @FALSE
READNEXT JobName FROM 1 ELSE Finished = @TRUE
LOOP UNTIL Finished = @TRUE
   CALL DSLogInfo('Processing job "':JobName:'".','')
   READV JobNumber FROM DSJobsFilePtr, JobName, 5
   THEN 
      EXECUTE 'CLEAR.FILE RT_LOG':JobNumber CAPTURING ScreenIO RETURNING ErrorCode
      *******************************************
      ** Add error handling here, if necessary **
      *******************************************
   END
   ELSE CALL DSLogWarn('Job has no number, skipping.','')
   READNEXT JobName FROM 1 ELSE Finished = @TRUE
REPEAT
CLOSE DSJobsFilePtr

droberts · Post by **droberts** » Tue Sep 08, 2009 7:38 am

Thanks ArndW - I just came to the same conclusion as the initial JobList kept overwriting the job details, so only the last ever log was cleared.

Thanks again for all your help - I have picked up a lot today

Daren

chulett · Post by **chulett** » Tue Sep 08, 2009 10:17 am

For the record, that job is part of the "Utility" suite of jobs supplied with the "KBA Job Control Utilities" and have been around for... well, forever. I know you said you used a Parallel job and I don't know if that really makes any difference but I've always had them as Server jobs and they run without issue. And when you said:

I used the Job Control tab as the original instruction was to cut and paste the code into the job and this is the only way I can see of doing that without creating a routine.

That was the right thing to do and exactly what he meant. FWIW, here's the code I have from my "release" that 8 or so years old that I've used without issue. Well, other than the known issue that using CLEAR.FILE on your own brings - the loss of any 'auto purge' settings. Which is why, I assume, there's also one that sets the auto purge information.

Code: Select all

      TCL = 'SSELECT DS_JOBS'
      If JobNameSubstringMatch Then TCL:= ' LIKE "':JobNameSubstringMatch:'"'
      If Folder Then TCL:= ' WITH F3 = "':Folder:'"'
      Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode)
      Call DSLogInfo("TCL Output for: ":TCL:"  >> ":ScreenOutput, "Msg")

      OPEN "DS_JOBS" TO JobsXrefTable Else
         Call DSLogFatal("Unable to open DS_JOBS", "Msg")
      End

      JobList = ""
      AllJobsDone = @FALSE
      Loop
         Readnext JobName Else AllJobsDone = @TRUE
      Until AllJobsDone Do
         If LEFT(JobName,2) # "\\" Then
            Read Row From JobsXrefTable, JobName Then
               JobList<-1> = JobName:"|":Row<5>
            End
         End
      Repeat

      Call DSLogInfo("Processing the following jobs: ":JobList, "Msg")

      JobCount = Dcount(JobList,@AM)
      For Job = 1 TO JobCount
         JobName = FIELD(JobList<Job>, "|",1)
         TableName = "RT_LOG":FIELD(JobList<Job>, "|",2)
         TCL = "CLEAR.FILE ":TableName
         Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode)
         Call DSLogInfo("TCL Output for: ":TCL:"  >> ":ScreenOutput, "Msg")
      Next Job

Note that the 'search string' parameter can be left blank to mean all jobs in the indicated folder, or if you want to 'wildcard' it you'll need to use the (whatever it is officially called) 'Universe' style of pattern matching for that. Also I just posted this without really seeing what was different about it between the old KB post or what you and Arnd worked out. It's just another example of Something That Works.

droberts · Post by **droberts** » Tue Sep 08, 2009 10:34 am

Thanks for the replies guys. I have now created and tested the job which:

a) Clears down log files based on either a category, project or substring;
b) Re-instates auto-purge settings.

The code may not be perfect but does what is intended

Code: Select all

*************************************************************************
** If no Category is specified, loops through all Jobs for the Project.
** Reads all jobnames, getting the RT_LOG number for the Jobname 
** and deletes the logs. 
** Also sets the auto-purge settings posts delete of the RT_LOG.
************************************************************************* 

TCL = 'SSELECT DS_JOBS' 

*******************************************************************
**Establishes whether a category or search string has been used and 
**uses the correct TCL code accordingly
****************************************************************** 

If SearchText Then TCL:= ' LIKE "':SearchText:'"' 
If Category Then TCL:= ' WITH F3 = "':Category:'"'  

*********************
** Executes TCL code
*********************

Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode) 
Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg") 

*****************************************
** Opens DS Jobs to Variable, else Error
***************************************** 

OPEN "DS_JOBS" TO JobsXrefTable Else 
Call DSLogFatal("Unable to open DS_JOBS", "Msg") 
End 

JobList = "" 
JobLog  = ""
AllJobsDone = @FALSE 

Loop 
Readnext JobName Else AllJobsDone = @TRUE 
Until AllJobsDone Do 

********************************************
** Do not process if it is a directory entry
** i.e., beginning "\\".
******************************************* 

 If LEFT(JobName,2) # "\\" Then
 Read Row From JobsXrefTable, JobName Then 
   JobList = JobName:"|":Row
   JobLogNo = "RT_LOG":FIELD(JobList, "|",2)<5>

****************************************************
** Do not process if the log number is not populated
** Otherwise, clear the log
****************************************************

   If Len(Trim(JobLogNo)) > 0 Then  
     Call DSLogInfo("Processing the following joblog: ":JobLogNo, "Msg")

     TCL = "CLEAR.FILE ":JobLogNo 

     Call DSExecute("TCL", TCL, ScreenOutput, SystemReturnCode) 
     Call DSLogInfo("TCL Output: ":ScreenOutput, "Msg")

*********************************************
** Sets the auto-purge settings based on the 
** PurgeType in the job parameters.
** Then writes the settings.
*********************************************

     OPEN JobLogNo TO F.FILE Then 
       Read PurgeSettings FROM F.FILE, "//PURGE.SETTINGS" Else            PurgeSettings = "" 
       If PurgeType = "DAY" Then 
          PurgeSettings<1> = 1 
          PurgeSettings<2> = PurgeInterval 
          PurgeSettings<3> = 0 
       End 
       If PurgeType = "RUN" Then 
          PurgeSettings<1> = 2 
          PurgeSettings<2> = 0 
          PurgeSettings<3> = PurgeInterval 
       End 
      WRITE PurgeSettings ON F.FILE, "//PURGE.SETTINGS" 
     End
   End 
 End 
End
Repeat

Daren

chulett · Post by **chulett** » Tue Sep 08, 2009 10:41 am

That... works ok? Seems to me that the auto purge read is too late being after the 'clear' and that you'd need to read it first, then clear and then lastly write it back out, but perhaps I'm missing something.

Or does this work because it is version 8? I vaguely recall something about a fundamental change there. Perhaps I'll take your code for a spin later (I have both versions) and see.

Edited to add: never mind, I see trying to get this code to run that you have job parameters for purge type and interval so you're setting them regardless rather than trying to retain their current value.

chulett · Post by **chulett** » Tue Sep 08, 2009 11:11 am

Me again.

Thought it might be worth mentioning that while the category can be just that in the versions prior to 8 it needs to be fully qualified for 8. So for a category called 'Fred' you'd need to specify it as follows:

7.x: Fred
8.x: \Jobs\Fred

And I'm pretty sure this code will only work in 8.x if you've reverted the logging back to the 'old' way in the Universe repository by setting RTLogging=1 in DSParams. Otherwise they are stored in the XMETA repository and this won't touch that, log-wise.