On 05-Aug-2014 17:36 -0500, Vernon Hamberg wrote:
<<SNIP>> this UDTF gives you a table of ALL the objects in a library
of a certain object type
As in my example, you can specify *ALL for the object type and get
everything in the library.
So you can do a sum of the object size column in the UDTF to quickly
get the size of the object in the library.
Unless the *LIB object is inside itself, then the SUM(OBJSIZE) of
*ALL objects _in_ the named library is not an aggregate of _both_ the
*LIB object and the objects within that *LIB; only the latter would be
included in the summary.?
Additionally, one of my points was that generating row data [with an
effective record format of ~256 bytes] in order to then use those
records as a result set from which to produce a single\scalar aggregate
is somewhat indirect and of _unnecessary_ overhead; i.e. the same
information is available as a single value directly from a single API
invocation. That the information is already indirectly available from a
result set obtained via a routine, already registered with the SQL as
the UDTF QSYS2.OBJECT_STATISTICS, is a valid counterpoint; more valid,
when the explanation\example produces the /correct/ output.
If you were to specify LIB for the object type, it would be the same
as SYSSCHEMAS.
Yes. But then that is a separate invocation of the UDTF? And of
another [sub]query? And just out of curiosity, what is, or is there
even, a specification that will obtain the QSYS2.OBJECT_STATISTICS
information for just one library [what arguments can be specified to
effect that] without also including a WHERE clause on the SELECT from
the TABLE()?
Thus to get the aggregate of both the object sizes within the library
*and* the library object itself, the summation would require operating
against "a UNION with the SCHEMA statistics" *and* the object statistics
from *ALL objects within that SCHEMA.? Whether that additional size
information is obtained via a separate query of SYSSCHEMAS for the
SCHEMA_SIZE or of the UDTF for the OBJSIZE is inconsequential. Or
perhaps...
Hmmm. Although, having since looked back on an old topic [wherein I
commented on the crappy attempt at documenting that UDTF; still crappy,
seriously lacking, although I did not check if the KC has anything
more\new], I wonder if by *additionally* specifying "LIB for the object
type" the UDTF might actually include the library and the objects
within. Of course as additionally specified beyond the specification of
ALL object types, that request would seem somewhat illogical, given the
conventional effect of /ALL/ being a /single-value/ special value.
Reference topic is "SQL query by object creation date":
<
http://archive.midrange.com/midrange-l/201401/threads.html#00271>
So what you suspect is, for once, not the case.
Hah! I had "once" trounced decades ago!
I speculated\suspected "that the UDTF OBJECT_STATISTICS does *not*
include the size of the library object [with the given\example
invocation]." If the result set of OBJECT_STATISTICS('VHTEST','*ALL')
includes a row for the library named VHTEST, then I stand corrected.
Though I also would be disappointed in the effects, given inclusion of a
row for the library object would be confusing to me [and I surmise would
also be, to others]; I doubt anyone would predict a requirement to add a
predicate to omit the library object when requesting the statistics for
all objects\types _within_ the named library.
I would be surprised if any but the third of the following three SQL
requests would suffice to provide the same as using the "Retrieve
Library Description (QLIRLIBD) API [that] returns the aggregate size of
both the Library object and the objects within the library"; these are
all using the OBJECT_STATISTICS UDTF purposely, instead of SYSSCHEMAS,
for the library object size:
SELECT SUM(x.OBJSIZE)
FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL','*ALL')) as x
-- a row for OBJNAME=QGPL OBJTYPE=*LIB seems improbable
SELECT SUM(x.OBJSIZE)
FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL','*LIB *ALL')) as x
-- ¿maybe OBJNAME=QGPL OBJTYPE=*LIB will be included?, but
-- the past archived discussion seems not to imply as much
SELECT SUM(x.OBJSIZE)
FROM
( SELECT x.OBJSIZE
FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL','*ALL')) as x
UNION ALL
SELECT x.OBJSIZE
FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLUSR','*LIB')) as x
WHERE x.OBJNAME = 'QGPL'
) as x
-- surely this will include the size of the *LIB and /all/ of
-- the objects, but this is hardly intuitive [or efficient]
midrange.com
