Re: SQL query by object creation date

On 14-Jan-2014 09:26 -0800, bryan dietz wrote:

<<SNIP>>

I have been playing with your code and ran across the following when
in RunSQL scripts:

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword TABLE not expected.

The error appears to be around the third "table" word usage:

That was an issue for me on v5r3 also, but I figured that was a problem since resolved. The definition of a table-reference in the documentation allows for a table-function invocation. Yet the SQL seems not to allow the reference in some places; I presume, incorrectly, thus possibly a defect. Specifically, contextually the SQL is allowing the table-function as table-reference directly after a FROM, but seems not to allow the reference after a JOIN. I suppose one could infer that the LATERAL specification, appearing only in the NTE syntax for a table-reference, might make the restriction seem logical.

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzjoinedt.htm>
_joined-table

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafztabref.htm>
_table-reference_


As such, perhaps with the following revision, whereby the table-function is tucked-into a Nested Table Expression (NTE):


SELECT
obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS lib
INNER JOIN LATERAL
(
SELECT xlib.objlibrary, xobj.*
FROM
table( values (lib.objname ) ) AS xlib ( objlibrary )
CROSS JOIN LATERAL
( select zobj.*
from TABLE(QSYS2.OBJECT_STATISTICS( lib.objname
, 'PGM,SRVPGM')
) AS zobj
) 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

Still reading looking into..

to get the listing of *ALL libraries the first use needs to be
QSYS2.OBJECT_STATISTICS ( '*ALL', '*LIB' )
QSYS was incorrect, that was my mistake.

<<SNIPped doc links>>

Where is the documentation suggesting the capability for any special values other than for object-types; notably, for any special values in place of the library-name argument? The snipped doc links mentioned nothing about that. The original invocation seemed reasonable enough conceptually, though I could understand why it might not function; i.e. per the subtle distinction between the use of the name QSYS as a library name vs /Machine Context/ as the implied library.

But how are other users supposed to figure that out? How did you figure that out? I suppose the person reading the doc reference is supposed to divine that the inputs are as-defined by some other function like DSPOBJD or perhaps the QGYOLOBJ or QUSLOBJ APIs, whence they can go review those; but obviously not any of those, as each of those allows effectively QSYS/*ALL for *LIB objects.