Purging of DataStage Log from Unix Script

[harshkohli]

We run all our DataStage jobs using dsjob command from Unix scripts. Few days ago, we made the project wide change using DS Administrator to auto purge the log upto previous 2 runs. I understand that this default 'auto purge' will only apply to the new jobs created after this change.

We have some old jobs and would like to clear the log automatically upto last 2 runs. Can we use any dsjob command in Unix script to purge the log before we run the job? or if we recompile the old programs then will they be purged automatically upto last 2 runs as we have a project wide 'Auto Purge' default?

Thanks

[nsm]

If you do a Project wide Auto Purge setting using DS Administrator it should work for existing jobs too.

nsm.

[chulett]

Nope, sorry. The OP has it correct, this setting only applies to new jobs added to the Project.

You'd need to understand the underlying structures and write some code to update the Auto Purge information in old jobs. Luckily, I believe you'll find that someone out there has already written a little Batch job control type program to do just that. We'll just have to see if they are willing to share the fruits of their labors.

[kcbland]

I have a utility available for mass-updating the auto-purge setting. This works for DS 5 and 6, don't know yet about 7.

USE AT YOUR OWN RISK. The author makes no guarantee to results, and make sure you have your project backed up whenever attempting internal hacking.


Paste this into a BATCH job, and setup four parameters:

Folder -- Optional - Category\Subcategory...
JobNameSubstringMatch -- Optional - FullJobName OR ...xxxx...
PurgeType -- DAY=purge by day interval, RUN=purge by number of runs
PurgeInterval -- interval setting, integer value

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)
         OPEN TableName 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
      Next Job

[ray.wurlod]

Project-wide purge parameters are stored in the DSParams file. There is a section that looks like this:

Code: Select all

[AUTO-PURGE]
PurgeEnabled=0
DaysOld=0
PrevRuns=0

A routine to update all jobs with these defaults should read these settings, then update the control record //PURGE.SETTINGS in the RT_LOGnnn file for each job. To determine the value of nnn, map from the DS_LOGS table.

Ken's code could be improved in a couple of ways. Note that the following is not exactly the same solution; it is an "all or nothing" approach, except as noted in code comments. Most error checking has been omitted for clarity.

Code: Select all

PurgeEnabled = 0
DaysOld = 0
PrevRuns = 0
OpenSeq "DSParams" To ParamFileVar
Then
   Loop
   While ReadSeq Line From ParamFileVar
      If Trim(Line) = "[AUTO-PURGE]"
      Then
         ReadSeq Line From ParamFileVar Then PurgeEnabled = Field(Line, "=", 2)
         ReadSeq Line From ParamFileVar Then DaysOld = Field(Line, "=", 2)
         ReadSeq Line From ParamFileVar Then PrevRuns = Field(Line, "=", 2)
         Exit
      End
   Repeat
   CloseSeq ParamFileVar
End

ControlKey = "//PURGE.SETTINGS"
ControlRec = PurgeEnabled : @FM : DaysOld : @FM : PrevRuns

Open "DS_JOBS" To DSJobsVar
Then
   Select DSJobsVar To 9
   Loop
   While ReadNext JobName From 9
      If JobName Matches "1A0X"
      Then
         ReadV JobNumber From DSJobsVar, JobName, 5
         Then
            LogFileName = Convert(" ", "", "RT_LOG" : JobNumber)
            Open LogFileName To LogFileVar
            Then
                 ReadU PurgeSettings From LogFileVar, ControlKey
                 Locked
                     * Control record in use by another process - bypass
                     Msg = "Cannot access purge settings for " : Quote(JobName)
                     Call DSLogWarn(Msg, "ResetPurgeParams")
                 End
                 Then
                     * Disable this statement if you do not want to override
                     * jobs that already have purge settings
                     Write ControlRec To LogFileVar, ControlKey
                 End
                 Else
                     Write ControlRec To LogFileVar, ControlKey
                 End
                 Close LogFileVar
            End
         End
      End
   Repeat
End

The same caveats apply to this code as to Ken's.

[harshkohli]

Thank you all for your replies and sharing the code.

To my original question, just to recap -

1. There is no dsjob command in DataStage for purging the log.

2. Project wide Auto-purge will only take affect for the newer jobs created (not compiled) after the project wide auto-purge is set.

Thanks.


Harsh Kohli

[kcbland]

1. Yes

2. Yes

[kduke]

If you take Ray's routine and change the lines from:

Code: Select all

Open LogFileName To LogFileVar 
then
   ...
end

to:

Code: Select all

execute "CLEAR.FILE ":LogFileName capturing output

then it will clear the file.

[ray.wurlod]

... including the control records!

A better command to execute (though a little slower) is:

Code: Select all

"DELETE FROM " : LogFileName : " WHERE CAST(@ID AS VARCHAR) NOT LIKE '//%';"

[kduke]

I agree. Sorry, should of caught that.

[collingridge]

We have a situation where all the jobs in our DataStage 4.1 system have their AutoPurge option set correctly but, from time to time, this setting becomes un-set.

Would the code listed above work in a 4.1 system? If not, is there another method I could use?

[ray.wurlod]

It will work in all versions from 1.0 through 7.5.

[Amos.Rosmarin]

If you run a job in multiple invocations where the invocation id is random
the log is purged but the job.invocation stays in the director

Is there a way to purge it togather with the log contant


Amos

[kduke]

I have tried doing this and you have to disconnect from DataStage and reconnect to see the change. Not cool. They are stored in RT_STATUSnnn. The log file has a field to specify which instance id this log record belongs to.

[ray.wurlod]

There is a known bug in 5.2 - maybe in 4.1 as well - where the auto-purge settings do not propagate into newly-created jobs. It was fixed in 6.0.