Re: JOBD analysis

On 08 May 2013 07:41, Stone, Joel wrote:

I would like to dump all job descriptions to an *OUTFILE with their library list as a "normalized" file, ie one record for each lib in the *libl of the JOBD.

Also include last-used date of the JOBD.

The goal is to find out which JOBD's reference certain TEST libraries.

Any ideas how to accomplish?

I would use DSPOBJD *ALL/*ALL *JOBD *FULL OUTPUT(*OUTFILE) to generate the first file in a join, joined to a Table Function (UDTF) which exposes the elements of the "Initial library list" (INLLIBL) as rows along with the library name and object name of the Job Description included as columns against which to effect the join.

create function jobdLibl
( jdLibNam varchar(10), jdObjNam varchar(10) )
returns table
( jdLbNm char(10)
, jdObNm char(10)
) language RPGLE specific jobdLibl
parameter style DB2SQL not deterministic
no dbinfo external action not fenced
disallow parallel no scratchpad
final call
external name 'somelib/somepgm'
cardinality 10
; -- the RPGLE program [or whatever] is left as exercise for reader

I recall there being a magazine article doing a RTVJOBA for a SQL table function [using a CLP] which could probably be mimicked to produce the above UTDF [perhaps also as a CLP]. Should be in the list presented with the following web search:
http://www.google.com/search?q=sql+udtf+rtvjoba

See the Retrieve Job Description Information (QWDRJOBD) API for obtaining the information about a job description:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/apis/qwdrjobd.htm

Then invoke the table function in a query, such as is shown below, after a CL request to gather the object OIR for a specific set of *JOBD objects:

dspobjd aLib/*all *jobd *full output(*outfile) outfile(qtemp/jd)

select jdll.*, jd.ODLCEN, jd.ODLDAT, jd.ODLTIM
from qtemp/jd
join table( jobdLibL(odlbnm, odobnm) ) as jdll
on jd.odlbnm=jdll.jdlbnm
and jd.odobnm=jdll.jdobnm
;