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

On 05-Aug-2014 14:08 -0500, Vernon Hamberg wrote:

On 8/5/2014 10:33 AM, CRPence wrote:

On 05-Aug-2014 08:36 -0500, Gqcy wrote:

Does someone have a SQL that I can run that will show the
library size?

A procedure [or ILE or OPM program] that gathers the result from
requesting Size via the Retrieve Library Description (QLIRLIBD) API
can be encapsulated in a scalar User Defined Function (UDF). The
routine would be defined to the SQL via CREATE FUNCTION [external
scalar].

There is a UDTF for a single library, provided by IBM - you can use
*ALL for the object types - here is an example of its use:

SELECT * FROM TABLE (QSYS2/OBJECT_STATISTICS('VHTEST ','*ALL')) as x

It can do only one library. Oh, yeah, I said that already - heh!

Brief docs for this and other services are at -
<https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20-%20Services>

On that page, click at "attachments" near the bottom for a link to a
Powerpoint showing the various services, both DB2 and system -

Very cool stuff being provided by the database team at IBM.

But just as "SYSSCHEMAS does NOT give the total of the contents, it gives only the size of the library object", I suspect that the UDTF OBJECT_STATISTICS does *not* include the size of the library object [with the given\example invocation]; likely, that invocation generates only a row for each object in the library, including the object size of each. So FWiW...

Just as an inquiry of the "library size" usually *is not about* just the Library (*LIB) object, the inquiry of the "library size" usually *is not about* just the size of the objects within; i.e. typically, the interest is in obtaining the combined sizes of the library object and the objects within. The noted Retrieve Library Description (QLIRLIBD) API returns the aggregate size of both the Library object and the objects within the library while not _unnecessarily generating_ intermediate rows that would be utilized only in a SUM aggregate [possibly requiring also a UNION with the SCHEMA statistics]. Thus the result with that API can be obtained as a scalar value [scalar UDF] answering the inquiry directly, instead of as rows [from a table UDF] that probably does not even include the library object size.