MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

Re: SQL query by object creation date



fixed

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'






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