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
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
On Tue, Jan 14, 2014 at 2:36 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:
On 14-Jan-2014 09:26 -0800, bryan dietz wrote:
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.
As such, perhaps with the following revision, whereby the
table-function is tucked-into a Nested Table Expression (NTE):
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS lib
INNER JOIN LATERAL
SELECT xlib.objlibrary, xobj.*
table( values (lib.objname ) ) AS xlib ( objlibrary )
CROSS JOIN LATERAL
( select zobj.*
from TABLE(QSYS2.OBJECT_STATISTICS( lib.objname
) AS zobj
) as xobj
) AS obj
ON lib.objname = obj.objlibrary
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.
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives