Read JobList from a specified project and category

[Simba]

I am trying to read a bunch of jobs under a specified project and category. Please let me know if there is any way to access Universe to complete this task. Please suggest alternative methods.

Thanks,

[kcbland]

You have multiple support access methods for getting this information. Read your BASIC manual, it documents the API's for asking a project what jobs are contained therein.

The other method is through the command line program "dsjob", which has command line options to return a list of projects on a server, the list of jobs in project, the status of jobs, etc.

[kduke]

Simba

There are several ways to list this out. The job category is stored in DS_JOBS. If you list the dictionary side of a hash file then you can see all the column names

Code: Select all

LIST DICT DS_JOBS

Output:

Code: Select all

DICT DS_JOBS    12:13:28pm  10 Dec 2003  Page    1

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            DS_JOBS         10L    S
NAME           D    0                            Job name        20T    S
READONLY       D    1                            Reserved        8L     S
OLETYPE        D    2                            Reserved        20L    S
CATEGORY       D    3                            Category        20L    S
DESC           D    4                            Description     60T    S
JOBNO          D    5                            No.             5L     S
JOBTYPEIND     D    6                            Reserved        5L     S
JOBTYPE        I      IF JOBTYPEIND                                     S
                      = "" THEN 0
                      ELSE
                      JOBTYPEIND
@              PH     ID.SUP NAME
                      DESC JOBNO
                      CATEGORY _

10 records listed.

Field 3 is CATEGORY. You can use a SQL statement to list this hash file.

Code: Select all

SELECT CATEGORY FROM DS_JOBS WHERE CATEGORY = 'whatever';

In BASIC you can use a Pick style SELECT to process these records.

Code: Select all

open "DS_JOBS" to DsJobs else stop
**
* use this style or the one below SELECT DsJobs
**
Cmd = 'SELECT DS_JOBS WITH CATEGORY = "whatever" '
execute Cmd capturing output
loop while readnext id do
  read DsJobsRec from DsJobs, id then
    Category = DsJobsRec<5>
     ...do something...
  end
repeat

This should get you close. You can use this code in Job Control Code and create a job.

[Simba]

Thank you verymuch. I will work on this. Your help greatly appreciated

[kduke]

If you tell us what you are trying to do then we can give you some more ideas. The API also has a way to list all the jobs. If you need VB or Java examples then we can probably give you those as well.

I wanted to explain how to research this for yourself by listing the dictionary and how that translates into BASIC code. If you search for DS_JOBS and DS_JOBOBJECTS or any of the other hash files like RT_LOG then you might be able to understand how DataStage works better. I believe that never hurts and may make your jobs run better.

Kim.

[Teej]

On the command line:

dsjob -ljobs [project]

(dsjob is located under 'cat /.dshome'/bin)

-T.J.

[Simba]

Our goal is to read jobs under a specified project and category. Right now i have declared an array and assigned jobnames. It would be a problem in the future, if they want to add some more jobs. That is why i want to read the list of jobs from the repository. After reading from the repository, i have to compile and run the jobs based on the status. Rest of the code is completed. I am stuck on this reading part from the repository. Thanks for your help. Please advice.

[kcbland]

If you simply go to the Start Menu --> Ascential --> Online Documentation and open up the BASIC manual, you will see a wealth of API's.

Since all API's are named DSsomething, then all the API's you need are bunched together. Simply browse this and you will see APIs like:

DSGetProjectInfo
DSGetJobInfo
DSGetLogSummary
DSGetStageInfo
DSRunJob
DSStopJob

[Simba]

Kim and Ken,
Here is some code to read the jobs from a specified folder. Please go through and let me know your suggestions. This code compiled and finished successfully but none of the jobs were compiled.

*______________________________________Function Definition ________________________________________*
* * *

DEFFUN CompileJobRoutine(Arg1,Arg2) Calling "DSU.CompileJobRoutin"
*______________________________________Initialize Variables________________________________________*
* *
BatchName = DSGetJobInfo(DSJ.ME, DSJ.JOBNAME)
JobFileName = "DS_JOBS"
*___________________________________________Open Files_____________________________________________*
* *
Open JobFileName To JobFile Else
Call DSLogFatal("Error opening file - ":JobFileName, BatchName)
End
*______________________________________________Main________________________________________________*
* *

Cmd = 'SELECT DS_JOBS WHERE CATEGORY = "HashFileName"'
Execute Cmd capturing output

JobList = ""
JobCnt = 0

Loop
while Readnext JobName Do

read DsJobsRec from JobFile,JobName then
JobList<-1> = JobName
JobCnt += 1
End

Repeat

For J = 1 TO JobCnt
JobName = JobList<J>
Result = CompileJobRoutine(Ans,JobName) ; * compile job
Next J


Thanks,

[kcbland]

First, please use the Code wrapper so that formatting is retained. I had to reapply all the formatting again to your cut and paste code.

Second, this line is wrong:

Code: Select all

DEFFUN CompileJobRoutine(Arg1,Arg2) Calling "DSU.CompileJobRoutin" 

Should be

Code: Select all

DEFFUN CompileJobRoutine(Arg1,Arg2) Calling "DSU.CompileJobRoutine" 

Third, do yourself a favor an embed debugging statements throughout your code. Setup a job parameter to pass in that activates/deactivates the debugging logic.

Code: Select all

DEBUG = "Y"
DEFFUN CompileJobRoutine(Arg1,Arg2) Calling "DSU.CompileJobRoutine" 

BatchName = DSGetJobInfo(DSJ.ME, DSJ.JOBNAME) 
JobFileName = "DS_JOBS" 
Open JobFileName To JobFile Else 
   Call DSLogFatal("Error opening file - ":JobFileName, BatchName) 
End 

If DEBUG = "Y" Then
   Call DSLogInfo("Executed Cmd ":Cmd, "DEBUG")
End
Cmd = 'SELECT DS_JOBS WHERE CATEGORY = "HashFileName"' 
Execute Cmd capturing output 
If DEBUG = "Y" Then
   Call DSLogInfo("Results of Cmd ":output, "DEBUG")
End

JobList = "" 
JobCnt = 0 

Loop 
while Readnext JobName Do 
   read DsJobsRec from JobFile,JobName then 
      JobList<-1> = JobName 
      JobCnt += 1 
   End 
Repeat 

If DEBUG = "Y" Then
   Call DSLogInfo("Processing JobList ":JobList, "DEBUG")
End

For J = 1 TO JobCnt 
   JobName = JobList<J> 
   If DEBUG = "Y" Then
      Call DSLogInfo("Compiling job ":JobName , "DEBUG")
   End
   Result = CompileJobRoutine(Ans,JobName) ; * compile job 
   If DEBUG = "Y" Then
      Call DSLogInfo("Compilation result ":Result, "DEBUG")
   End
Next J 

Try doing this an finding out what isn't working and what is working.

By the way, what is variable "Ans" in

Code: Select all

   Result = CompileJobRoutine(Ans,JobName) ; * compile job 

[kduke]

You could still reset the jobs with:

Code: Select all

execute "DSD.RUN JobName -2"

I think this would be safer. I like all of Ken's ideas. Debug statements would help.

Kim.

[rabs]

Another way to perform debugging is to use the compiler directive:

Code: Select all

$Define DEBUG

then

Code: Select all

$IfDef DEBUG
       <do this>
$EndIf

Code: Select all

$IfNDef DEBUG
       <do that>
$EndIf

This has the same behaviour as normal if/else but the compiler does not include irrelevant branches of code in the executable. So if you are not debugging, debugging statements are not included in the executable.

(May not be a big deal, but in my C++ days there were *a lot* of debug statements for each program so every little saving counts).

To turn off debugging use:

Code: Select all

$Undefine DEBUG

[Simba]

Thanks for all of your suggestions. I have fixed minor issues and able compile and run. I an running this with DEBUG statements.

Code: Select all

Cmd = 'SELECT CATEGORY FROM DS_JOBS WHERE CATEGORY = "HashFilesTest"'
Execute Cmd capturing output
  
If DEBUG = "Y" Then
         Call DSLogInfo("Results of Cmd ":output, "DEBUG")
End

In the LogFile, variable output shows "SQL" what does it mean?

Code: Select all

Loop
         While Readnext JobName Do
            read DsJobsRec from JobFile,JobName then
            JobList<-1> = JobName
            JobCnt += 1
            End
         If DEBUG = "Y" Then
            Call DSLogInfo("I am in the loop ":JobList, "DEBUG")
         End
         Repeat

Control flow is not even going into the loop. Anything wrong in the ReadNext JobName. Kim, DS_JOBS output shows colume JobName as "Job name". Please let me know if you find anything wrong.

[kcbland]

Look at the select statement. You're selecting the CATEGORY, not the job name. Try using @ID instead.

[Simba]

Still not working. I have tried @ID,