Chuck - 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.
If you were to specify LIB for the object type, it would be the same as
So what you suspect is, for once, not the case.
On 8/5/2014 5:28 PM, CRPence wrote:
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:
There is a UDTF for a single library, provided by IBM - you can use
Does someone have a SQL that I can run that will show the
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
*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 -
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.