Read JobList from a specified project and category

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Okay, this is the last on this thread for me.

You are executing SQL, when you need to be using the internal command language for accessing hash files.

Instead of:

Code: Select all

Cmd = 'SELECT CATEGORY FROM DS_JOBS WHERE CATEGORY = "HashFilesTest"' 
Please use

Code: Select all

Cmd = "'SSELECT DS_JOBS WITH F3 = 'HashFilesTest'"
I also sent you a BATCH utility for compilation of jobs. In there was all the necessary logic for what you have been trying to do. You should read that and study it. For those interested, here's the logic, minus an external subroutine called. In addition, some of the variables used are expected to be job parameters with values passed at runtime:

Code: Select all

$INCLUDE DSINCLUDE DSR_UVCONST.H
$INCLUDE DSINCLUDE DSR_COMCONST.H

      Equate LOG.OK.NAME To "OK"
      Equate LOG.WARN.NAME To "!!!"
      MyJobNo = DSJobName
      SUBR = DSR.SUB.JOB
      Key = DSR.SUB.JOB.GETNO
      Call @SUBR(Key, MyJobNo)
      Dummy1 = "" ; Dummy2 = ""

      DEFFUN CallSystemCmd(aCmd, pSystemType) Calling "DSU.CallSystemCmd"

*_______________________________________Define Functions___________________________________________*
*                                                                                                  *

*______________________________________Initialize Variables________________________________________*
*                                                                                                  *
      BatchName = DSGetJobInfo(DSJ.ME, DSJ.JOBNAME)
      JobFileName = "DS_JOBS"
      JobsLike = TRIM(JobsLike)

*___________________________________________Open Files_____________________________________________*
*                                                                                                  *
      Open JobFileName To JobFile Else
         Call DSLogFatal("Error opening file - ":JobFileName, BatchName)
      End

*______________________________________________Main________________________________________________*
*                                                                                                  *

      If NOT(JobsLike) Then
         Call DSLogWarn("No Jobs specified", BatchName)
         Goto Fini
      End
      If UPCASE(AreYouSure) # "Y" Then
         Call DSLogWarn("You were not sure!", BatchName)
         Goto Fini
      End

      JobListCnt = DCOUNT(JobsLike,",")

      If JobListCnt > 1 Then
         JobList = JobsLike
         Convert "," To @AM IN JobList
         FormList JobList
      End Else
         Begin Case
            Case JobsLike = "ALL"
               TCLcmd = "SSELECT DS_JOBS"
            Case 1
               TCLcmd = 'SSELECT DS_JOBS LIKE "...':JobsLike:'..."'
         End Case
         If Folder # "" Then TCLcmd := ' WITH F3 LIKE "':Folder:'..."'
         Call DSLogInfo("TCLcmd: ":TCLcmd, BatchName)
         Call DSExecute("TCL", TCLcmd, ScreenOutput, SystemReturnCode)
         If TRIM(ScreenOutput) # "" Then Call DSLogInfo("Screen Output: ":ScreenOutput, BatchName)
      End

      JobList = ""
      JobsSkipped = ""
      JobCnt = 0
      AllJobsDone = @FALSE

      Loop
         Readnext JobName Else AllJobsDone = @TRUE
      Until AllJobsDone Do

         If JobName[1,1] # "\" And JobName[1,22] # BatchName Then
            ProcessJob = @TRUE

            If NotCompiledOnly = "Y" Then
               Readv JobNo From JobFile, JobName, 5 Then
                  If SystemType = "UNIX" Then
                     cmd = "ls -l RT_BP":JobNo:".O | wc -l"
                  End Else
                     cmd = "dir RT_BP":JobNo:".O"
                  End
                  Result = CallSystemCmd(cmd, SystemType)
                  If SystemType = "UNIX" Then
                     Result = OCONV(Result, "MCN")
                     If Result > 1 Then ProcessJob = @FALSE
                  End Else
                     Result = Index(Result,"2 File(s)              0 bytes",1)
                     If Result = 0 Then ProcessJob = @FALSE
                  End
                  Call DSLogInfo("JobName: ":JobName:"  JobNo: ":JobNo:"   Result: ":Result:"   ProcessJob: ":ProcessJob, BatchName)
               End Else
                  Call DSLogFatal("Error reading ID - ":JobName:"  from file - ":JobFileName, BatchName)
               End
            End

            If ProcessJob = @TRUE Then
               JobList<-1> = JobName
               JobCnt += 1
            End Else
               JobsSkipped<-1> = JobName
            End
         End
      Repeat

      If JobsSkipped Then Call DSLogInfo("Jobs skipped: ":@AM:JobsSkipped, BatchName)

      If JobList Then
         JobsCompiled = ""
         JobsNotCompiled = ""
         Call DSLogInfo("Jobs that will be compiled: ":@AM:JobList, BatchName)
         For J = 1 TO JobCnt
            JobName = JobList<J>
            Gosub CompileJob             ; * compile job
         Next J
         Call DSLogInfo("Jobs compiled: ":@AM:JobsCompiled, BatchName)
         If JobsNotCompiled Then Call DSLogWarn("Jobs NOT compiled: ":@AM:JobsNotCompiled, BatchName)
      End Else
         Call DSLogWarn("There were no jobs compiled", BatchName)
      End

      Goto Fini                          ; *** End of Program



*------------------------------------------------
*--- Subroutines
*------------------------------------------------

CompileJob:
      Key = DSR.SUB.JOB.RESERVE
      Arg2 = JobName
      Call @SUBR(Key, Arg2)
      Call DSD.Init(MyJobNo, Dummy2)
      If Key <> "" Then
         JobsNotCompiled<-1> = JobName:"   ":"Cannot attach to job"
         NumFailed += 1
      End Else
         Key = DSR.SUB.JOB.COMPILE
         Arg2 = JobName
         Call @SUBR(Key, Arg2)
         Call DSD.Init(MyJobNo, Dummy2)
         If Key <> "" Or Arg2<1> <> "" Then
            JobsNotCompiled<-1> = JobName:"   ":"Cannot compile job"
            NumFailed += 1
         End Else
            JobsCompiled<-1> = JobName
            Ignore = 0
            JobNo = DSRGetJob(JobName,Ignore)
            Execute "CLEAR.FILE ":DSR.RTSTATUS.FNAME:JobNo
            Execute "CLEAR.FILE ":DSR.RTLOG.FNAME:JobNo
         End
         Key = DSR.SUB.JOB.RELEASE       ; * unlock job, ignoring any errors
         Arg2 = JobName
         Call @SUBR(Key, Arg2)
         Call DSD.Init(MyJobNo, Dummy2)
      End
      Return


Fini:                                    ; *** finish ***
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
kduke
Charter Member
Charter Member
Posts: 5227
Joined: Thu May 29, 2003 9:47 am
Location: Dallas, TX
Contact:

Post by kduke »

You cannot use a SQL SELECT and a READNEXT statement. A WHERE clause is a SQL style SELECT. You need to use WITH instead of a WHERE. SQL statement uses single quotes around values. Pick style SELECT uses double quotes. SQL statement also needs a FROM and ; at the end. Your Pick style SELECT should be:

Code: Select all

Cmd = 'SELECT DS_JOBS WITH CATEGORY = 'MyJobFolder' "
loop while readnext JobName
...
repeat
Mamu Kim
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Oh, of COURSE you can!! :P

Code: Select all

PERFORM "SELECT ... FROM ... TO SLIST 5;"
LOOP WHILE READNEXT KEY FROM 5
...
REPEAT
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply