MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

Re: SQL query by object creation date



fixed

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.

--
Bryan

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
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'






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact