MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

Re: I need a quick little SQL to list out library size?



fixed

I can state that
the sum of RTVDSKINF is equal
to the sum of QSYS2.OBJECT_STATISTICS...

(which doesn't answer the question of accounting for the size of
the LIB object)




On 8/5/2014 9:49 PM, CRPence wrote:
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]







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact