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.
midrange.com
