Chuck, this version worked also, many thanks again.
for the '*ALL',*LIB' portion, I experimented and found that *ALL,
*ALLUSR and *IBM are valid. *LIBL,*USRLIBL, *CURLIB did not work.
CRPence said the following on Wed, 1/15/2014 3:22 PM:
Try this revision instead [incorporating the later noted updates from
your reply; even though IMO, having to make the changes is indicative of
TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*LIB')) AS lib
INNER JOIN LATERAL
SELECT lib.objname as objlibrary, xobj.*
, 'PGM SRVPGM')
) AS xobj
) AS obj
ON lib.objname = obj.objlibrary
AND obj.last_used_timestamp is null
/* and obj.objtype in ('*PGM', '*SRVPGM') */ -- redundant
only two updates
: *ALL in place of QSYS for all library listing.
For an apparent lack of effect from having used 'QSYS', presumably
producing an empty result set, that may be required. However that
clearly contradicts the documentation which says a library-name is the
first argument, and the second argument is the object-type-list, which
obviously can include '*LIB'. QSYS is a library name [albeit like
QTEMP, nuanced], and *LIB is an object-type; presumably the restriction
for which '*ALL' must be specified, is true for all object types that
reside only in the Machine Context; e.g. configuration objects like DEVD
and LIND, user profiles USRPRF, and authorization lists AUTL.? IMO, a
failure of the QSYS2.OBJECT_STATISTICS('QSYS','*LIB') to produce the
list of libraries is a defect. But given the dearth of docs...
QSYS2.OBJECT_STATISTICS( lib.objname, 'PGM SRVPGM CMD FILE')
: the "parm" for object type is just a space delimited list (go
figure) and the object type can either be *PGM or PGM
Hmmm. Not according to the "documentation". If the comma-delimited
values does not function properly, then that is an apparent defect.
That documentation clearly states that the object-type special values
can be denoted instead without the asterisk as prefix. And also that
comma-delimited vs space-delimited are supported. Thus according to
what doc is available, any of the following variations should suffice to
effect the same results:
'PGM SRVPGM CMD FILE'
'*PGM *SRVPGM *CMD *FILE'
And the following variations may or may not suffice to effect the
same results, depending upon one's interpretation [for which a syntax
diagram probably could clarify better than words]:
'PGM, SRVPGM, CMD, FILE'
'PGM SRVPGM CMD FILE'
'PGM SRVPGM, CMD FILE'