Re: SQL query by object creation date

Chuck you are correct:
"My interpretation is that the desire is to effect a second invocation of
the UDTF, not a second statement. "
my original question shows my weakness with SQL.

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


-----------


here is the link from developerworks describing

http://goo.gl/0GxRr6
https://www.ibm.com/developerworks/community/wikis/home/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.Object_Statistics%20table%20function%20ease%20of%20use


--and link to all new(?) db2 services:
http://goo.gl/wqzyAx

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20-%20Services


Bryan


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

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

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