How to query for jobs that are not using $PROJDEF

[vaby]

Is anyone know an easy way to get a list of jobs in a project that do not use $PROJDEF?

Thanks!

Vladimir

[ray.wurlod]

It is possible. It is not easy - you need to query on undocumented columns (in particular the parameters collection in DS_JOBOBJECTS in ROOT records) - with a join to DS_JOBS to get the job name.
I might get time on the weekend to post something, if no-one has done so before then.

[kduke]

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),'PROJDEF',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
;

[ray.wurlod]

Might be more efficient were DS_JOBOBJECTS additionally constrained only to process ROOT records, as it is only these that contain parameter collections. (Actually I need to check that - shared containers also have parameters - do they have ROOT records in DS_JOBOBJECTS?)
And wouldn't SELECT DISTINCT have avoided the need for GROUP BY clause?

[vimali balakrishnan]

Can you explain the query.

Thanks,
Vimali Balakrishnan.

[ray.wurlod]

It's fairly standard SQL - which pieces aren't you certain about?

EVAL is a UniVerse SQL extension to allow BASIC expressions to be used in much the same way that regular SQL expressions are. FMT is a keyword that overrides the default column width and justification, which is 10 characters left-justified for EVAL).

In the BASIC expression @RECORD means "the entire record", so that the test determines whether the string "PROJDEF" appears anywhere in the job objects record.

Note: @RECORD can NOT be used to mean "the entire record" in DataStage jobs - only in UniVerse/SQL queries.