On 13-Jan-2014 14:46 -0800, Bdietz400 wrote:
I have been playing with this new function. Making my own generic
DSPOBJD. Since it's SQL I can use better *generic* selections for
the object.
I have bee trying to figure out how to, in one SQL statement, use
the output from
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS x
As input to a second statement for the object selection.
Any ideas?
My interpretation is that the desire is to effect a second invocation
of the UDTF, not a second statement. That is, the 2nd invocation would
be used to obtain a list of objects for each library obtained from the
prior\first invocation, against which selection then can be performed
across all of those libraries... in the single-statement.? If so, then
perhaps the following which asks for all of the /statistics/ for all
program and service program objects that have a non-blank program
attribute and that have never been used... or possibly the last-used
information was reset [sorry, I can not test]:
SELECT
lib.objname as lib_name
/* lib_name is redundant; i.e. lib.objname=obj.objlibrary */
, obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS lib
INNER JOIN LATERAL
( /* While seemingly worthless included in return table...
Per lack of the OBJLIBRARY as a column in the UDTF,
the column is generated here from the prior results
in a Cartesian product, to enable the inner join */
SELECT xlib.objlibrary, xobj.*
FROM
table( values (lib.objname ) ) AS xlib ( objlibrary )
CROSS JOIN LATERAL
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; omitted
Had the UDTF OBJECT_STATISTICS included additionally in its RETURNS
TABLE column-list, a column named OBJLIBRARY that was just a reflection
of the value passed as the first argument, then the query is much simpler:
SELECT
obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','LIB')) AS lib
INNER JOIN LATERAL
TABLE(QSYS2.OBJECT_STATISTICS(lib.objname,'PGM,SRVPGM')) AS obj
ON lib.objname = obj.objlibrary
WHERE obj.objattribute <> ''
AND obj.last_used_timestamp is null
/* and obj.objtype in ('*PGM', '*SRVPGM') */ -- redundant; omitted
midrange.com
