Re: SQL query by object creation date

Chuck, your NTE version work perfectly. Many thanks for the education, I
still have lots more SQL to learn.

only two updates

QSYS2.OBJECT_STATISTICS('*ALL','*LIB') : *ALL in place of QSYS for all
library listing.
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

thanks again,
Bryan




On Tue, Jan 14, 2014 at 2:36 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.