Parameters on Execute Command Activity in a Sequence

[dsnovice]

Hello,

In the Job sequence level we use Excecute Command to create done files. These done files act as check points for other sequences by the wait for file activity.
Our problem is we have lot of sequences and cross reference of the file, now we want to compile a list of done files created by each sequence and which sequence uses these files.

I tried the report assistant and it doesn't support sequence level information.
Also searched the forum found some help on accessing DSLink or DS_JOBOBJECTS Information but was not able to nail down on which table or column carries the parameters of File Activities and its routines. Any help or suggestions would be greatly appreciated

thank you,

a novice

[kduke]

You are correct. This is probably not available without hacking DS_JOBOBJECTS. Not a recommended practice for a novice. I am sure one of the top posters could do it in a few minutes if you could convice them it was worth their effort. Not sure how you can do that.

[ray.wurlod]

Are the done files' names parameterized?

If so you would need to extract the parameter reference (see below) then determine the parameter value that was used by inspection of the "job started" event in the job log (DSGetLog... functions).

Otherwise you should be able to extract the command used and the arguments field contents from the Execute Command activity record in DS_JOBOBJECTS and parse the file name from whichever of those it is in.

[dsnovice]

Ray, neither the done files nor the wait for files are parameterized. it is a good suggestion we will implement it in the future.

Kim - Will your DwNav will be able to provide this info? I noticed you and Ray are the top posters when it comes to hacking the backend of DataStage. I tried extracting info from DS_JOBOBJECTS from the UV stage using the following SQL:

Code: Select all

SELECT @ID, READONLY, OLETYPE, NAME, OBJTYPE, INSTANCECAT, OBJNAME, OBJID, STAGETYPE, OBJIDNO, DEPENDS, EVAL "@RECORD<1>" FMT '60T' , EVAL "@RECORD<2>" FMT '60T' , EVAL "@RECORD<3>" FMT '60T' , EVAL "@RECORD<4>" FMT '60T' , EVAL "@RECORD<5>" FMT '60T' , EVAL "@RECORD<6>" FMT '60T' , EVAL "@RECORD<7>" FMT '60T' , EVAL "@RECORD<8>" FMT '60T' , EVAL "@RECORD<9>" FMT '60T' , EVAL "@RECORD<10>" FMT '60T', EVAL "@RECORD<11,12>" FMT '60T' FROM DS_JOBOBJECTS

but none of the output seems to have what I am looking for. please help.

Thank you,

a novice, Though a quick and willing learner.

[kduke]

It is easier to do this at TCL. You need to search for TCL commands. TCL is the way to talk to Universe directly or what is now called the DataStage Engine. You get a TCL prompt ">" when you telnet into a DataStage server. This is equivalent to sqlplus for Oracle.

The quickest way to find these records is to use SEARCH. The SEARCH TCL command will find any string in a hashed file. SEARCH will look for multiple strings so it will keep prompting till you give it a blank line.

SEARCH DS_JOBOBJECTS
Kim.txt

3 records found.
>>

The double prompt ">>" says you have a selectlist active. This type of selectlist is just a subset of key fields to process against the hashed file instead of the whole hashed file. Much faster. After you get an active selectlist then you can save it and reuse it.

>>SAVE.LIST KIM
>GET.LIST KIM
>LIST DS_JOBOBJECTS NAME

To use a selectlist in a SQL SELECT is a little tricky. Ray is probably the only one who does this on a regular basis. I always have to look it up.

Code: Select all

select
   DS_JOBOBJECTS.NAME AS OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   EVAL DS_JOBOBJECTS."@RECORD<10>" AS FIELD10 FMT '40L'
from 
   DS_JOBOBJECTS SLIST 0
;

The SLIST 0 says use selectlist 0. This will limit the records to only those with the string found if you do the SEARCH command right before this.

You can also use this SQL.

Code: Select all

SELECT 
   DS_JOBS.NAME AS JOB_NAME, 
   DS_JOBS.CATEGORY, 
   DS_JOBOBJECTS.NAME AS OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),':1',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'
FROM 
   DS_JOBS, 
   DS_JOBOBJECTS 
WHERE 
   DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
   and FOUND = 'FOUND'
GROUP BY
   JOB_NAME, 
   DS_JOBS.CATEGORY, 
   OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   FOUND
;

Change :1 to the uppercase of the string you are looking for so in my example it would be KIM.TXT.

[kduke]

By the way DwNav only brings in sequences to get the relationship between sequences and the jobs they run. It always annoys me to try to figure out what sequence controls the job I need to change. Usually you have to do a search like the above which I have a cool routine JR Hines wrote or I go to production and look at the logs. The first record in a run will show the controller job.

[kduke]

I have not tried this but it should give you the field number:

Code: Select all

SELECT 
   DS_JOBS.NAME AS JOB_NAME, 
   DS_JOBS.CATEGORY, 
   DS_JOBOBJECTS.NAME AS OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),':1',1) = 0 then 0 else dcount(@RECORD[1, index(upcase(@RECORD),':1',1)], @FM)" AS FOUND FMT '5R'
FROM 
   DS_JOBS, 
   DS_JOBOBJECTS 
WHERE 
   DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
   and FOUND > 0
GROUP BY
   JOB_NAME, 
   DS_JOBS.CATEGORY, 
   OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   FOUND
;

[dsnovice]

Kim,

Thank you for the codes I will publish my methods and approach at the earliest.

thank you,

a novice

[dsnovice]

Kim,
All the SQL you gave me worked properly, I just had to customise it for my environment.

Initially I used the index sql to find the index number it turned out to be 12, hence the 12 th field of record holds the parameters of Execute command. Then I used the below sql in uvsh prompt to extract the parameters used in the Execute Command Activity stage in job sequences.

Code: Select all

SELECT 
DS_JOBS.NAME AS JOB_NAME, 
DS_JOBOBJECTS.NAME AS OBJECT_NAME,
DS_JOBOBJECTS.STAGETYPE, 
EVAL DS_JOBOBJECTS."@RECORD<12>" AS FILENAME FMT '40L' 
FROM DS_JOBS, DS_JOBOBJECTS 
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
AND DS_JOBOBJECTS.OLETYPE = 'CJSExecCmdActivity' 

For Extracting the File names and Wait times used in the Wait for File Routine activity(Custom Routine) the below code was used.

Code: Select all

SELECT 
         DS_JOBS.NAME AS JOB_NAME, 
         DS_JOBOBJECTS.NAME AS OBJECT_NAME,   
         DS_JOBOBJECTS.STAGETYPE,
   EVAL DS_JOBOBJECTS."@RECORD<11>" AS ROUTINE_NAME FMT '50L', 
   EVAL DS_JOBOBJECTS."@RECORD<12>" AS FILENAME2 FMT '50L',
   EVAL DS_JOBOBJECTS."@RECORD<13>" AS FILENAME3 FMT '50L',
   EVAL DS_JOBOBJECTS."@RECORD<14>" AS FILENAME4 FMT '50L' FROM 
    DS_JOBS,DS_JOBOBJECTS 
WHERE 
    DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
   AND ROUTINE_NAME = 'FileWait'

This gave me all the ".done" files and wait for Files.

thank you all,

a novice