Discover size of library using SQL

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

DSPOBJD OBJ(MYLIB) OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPOBJD1)

I am going to "cheat" here and display the size using a SQL statement. If I was not able to use SQL I would have written an RPG program to read the output file instead.

SELECT ODOBNM,ODOBSZ FROM QTEMP.@DSPOBJD1 Object Object Size MYLIB 196,608

And what if I wanted to know the total size of all the objects in that library. I would have used the DSPOBJD command again:

DSPOBJD OBJ(MYLIB/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPOBJD2)

This time is looking for all the objects in the library MYLIB.

This SQL statement gives me the total, SUM, of the size of all the objects.

SELECT SUM(ODOBSZ) FROM QTEMP.@DSPOBJD2 SUM ( ODOBSZ ) 10,215,424

That is a big difference in the numbers. Remember that the number for the library is just the library's size, which does not include the objects within the library.

DSPOBJD make this a two-step process, what I want is a single step method. For the size of the library I could use the Db2 for i SCHEMAS view:

SELECT SYSTEM_SCHEMA_NAME,SCHEMA_SIZE FROM QSYS2.SYSSCHEMAS WHERE SYSTEM_SCHEMA_NAME = 'MYLIB' SYSTEM_SCHEMA_NAME SCHEMA_SIZE MYLIB 196,608

I found it interesting that in the IBM documentation they do not recommend using this view for the size of the library, or schema in SQL parlance. They recommend using the OBJECT_STATISTICS table function instead. Notice how I pass the name of the library as the first parameter, and LIB to denote I only want the statistics for the library with this name.

SELECT OBJNAME,OBJSIZE FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','LIB')) AS A OBJNAME OBJSIZE MYLIB 196,608

For the total size of the objects in the library again I use the SUM function. The parameters passed to the table function are a bit counter to what I did in the previous statement just of the library's data. This time I want the details for all the objects, hence the ALL in the second parameter, and the library these objects are in has to be the first parameter.

SELECT SUM(OBJSIZE) FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','ALL')) AS A SUM ( OBJSIZE ) 10,215,424

If I wanted to I could put the last statement in a RPG program, and make the library name a parameter that would be passed to the program.

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-pr Main extpgm('TESTRPG') ; 04 *n char(10) ; //Library 05 *n uns(10) ; //Total size 06 end-pr ; 07 dcl-proc Main ; 08 dcl-pi *n ; 09 Library char(10) ; 10 TotalSize uns(10) ; 11 end-pi ; 12 exec sql SELECT SUM(OBJSIZE) INTO :TotalSize 13 FROM TABLE 14 (QSYS2.OBJECT_STATISTICS(:Library,'ALL')) 15 AS A ; 16 dsply ('Size = ' + %triml(%editc(TotalSize:'J')) ) ; 17 end-proc ;

Line 1: In 2019 I have to use totally free RPG.

Line 2: These control options mean the following:

• main(Main)  the program is not using the RPG cycle
• option(*srcstmt)  when the program is compiled it will use source statement line numbers, rather than the system generated ones
• dftactgrp(*no)  as there is a procedure in the program it cannot run in the default activation group

Lines 3 – 6: The program prototype for this program. The program name must be in the EXTPGM keyword. This program has two parameters, which I never bother to name in the prototype, but I do have to give their data types.

Line 7: Start of the Main procedure.

Lines 8 – 11: The procedure interface for the Main procedure. I never bother to name the interface, but I must the parameters their names'.

Lines 12 – 15: This is the same as the last SQL statement. I am taking to total of the sizes of all the objects and putting in the variable TotalSales. And in the table function's parameters the hard coded library name has been replaced with the parameter passed to this program, Library.

Line 16: This line is only here as this is a test program, so I can show value returned from the SQL statement. I have used the %EDTIC built in function to format the number nicely with "thousand separators", and the %TRIML removes the leading spaces in the transformed number.

Line 17: End of the Main procedure.

When I call this program, passing MYLIB to it, the following is displayed:

DSPLY Size = 10,215,424

 

This article was written for IBM i 7.3, and should work for some earlier releases too.