On 15-Jan-2014 07:26 -0800, bryan dietz wrote:
Chuck, your NTE version work perfectly. Many thanks for the
education, I still have lots more SQL to learn.
I was confident that there was something way too complicated about
the way I had written the query; that there was something simpler, but I
failed to "see it" earlier, due to my mind concentrating on stupid
errors I could never get past on v5r3. Having reviewed my query again,
but without actually trying to perform the query, it seems so obvious
now. Very odd I did not "see it", because I had used effectively the
identical query to revise my UDTF to include the library name; there
using a variable, instead of the lateral-reference to a field in the
prior table-reference.
Try this revision instead [incorporating the later noted updates from
your reply; even though IMO, having to make the changes is indicative of
defects]:
SELECT
obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*LIB')) AS lib
INNER JOIN LATERAL
(
SELECT lib.objname as objlibrary, xobj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS( lib.objname
, 'PGM SRVPGM')
) AS xobj
) AS obj
ON lib.objname = obj.objlibrary
WHERE obj.objattribute<>''
AND obj.last_used_timestamp is null
/* and obj.objtype in ('*PGM', '*SRVPGM') */ -- redundant
only two updates
QSYS2.OBJECT_STATISTICS('*ALL','*LIB')
: *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...
and
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'
'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'
midrange.com
