Retrieving information about a library using SQL

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

1. Library name:  No surprise that this is the mandatory parameter
2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

SELECT * FROM TABLE(QSYS2.LIBRARY_INFO('MYLIB'))

Even though I have selected all the columns in my statement with the asterisk ( * ), I am only going to list the columns I find interesting. For a full list of the available columns click on the link to IBM's documentation at the bottom of this post.

OBJECT_COUNT LIBRARY_SIZE LIBRARY_SIZE_COMPLETE LIBRARY TYPE ------------ ------------ --------------------- ------------ 32 9269248 YES PROD TEXT_DESCRIPTION ---------------- SIMON`S LIBRARY IASP_NAME IASP_NUMBER CREATE_AUTHORITY JOURNALED --------- ----------- ---------------- --------- *SYSBAS 1 *EXCLUDE NO

What does this table function mean by the value of "Yes" in the LIBRARY_SIZE_COMPLETE column?

• YES:  Size of all the objects was determined
• NO:  The size for some objects was omitted as they were locked by other jobs, or I do not have authority to the object

I am the only person using my library, MYLIB, therefore, I know that there are no other jobs locking any of the objects in it. But what about the library QSYS there are bound to be some jobs locking objects within it.

SELECT LIBRARY_SIZE_COMPLETE FROM TABLE(QSYS2.LIBRARY_INFO('QSYS')) LIBRARY_SIZE_COMPLETE --------------------- NO

If that column is ever NO I know to be wary of the returned results as they do not contain all the information from all of the objects.

The first two columns caught my eye:

• OBJECT_COUNT:  Total number of objects in the library, even those I am not authorized to
• LIBRARY_SIZE:  Size of the library in bytes, including the size of all of the objects within the library and of the library itself

Both of these, in my opinion, are very useful information. In the past I would have to use the DSPOBJD command to retrieve this information.

If I wanted to get the count of the number of objects in MYLIB I could do the following:

01 CL:DSPOBJD OBJ(MYLIB/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/OUTFILE1) ; 02 SELECT COUNT(*) AS "No. objects" FROM QTEMP.OUTFILE1 ;

Line 1: All of these statements I have executed in ACS's "Run SQL Scripts" tool. For a few releases it has been possible to execute CL commands in this tool, and in the RUNSQLSTM command, just by starting the line with CL:. On this line I am running the DSPOBJD command to make a list of all the objects in the library MYLIB, and output to an output file QTEMP/OUTFILE1.

Line 2: I am using this SQL to return a count of the number of objects in the library.

The results are:

No. objects ----------- 32

Which matches the value from the new LIBRARY_INFO table function.

Determining the total size of the library was always a two-step process:

1. Determine the size of all of the objects in the library
2. Determine the size of the library itself

Having already used the DSPOBJD command for the number of objects I can use it again for the total size of those objects. I still need to get the size of the library using the DSPOBJD command:

03 CL:DSPOBJD OBJ(*LIBL/MYLIB) OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(QTEMP/OUTFILE2) ;

I can use a UNION clause to combine the results from OUTFILE1 and OUTFILE2 to determine the total size of the library:

04 SELECT SUM(SIZE) AS "Total size" FROM 05 (SELECT SUM(ODOBSZ) AS SIZE FROM QTEMP.OUTFILE1 06 UNION 07 SELECT ODOBSZ AS SIZE FROM QTEMP.OUTFILE2) ;

Line 4: Here I define the one column that will be returned in the results.

Line 5: Calculate the sum of all size columns, ODOBSZ, for all the objects. I use the AS to give this result a new column name.

Line 6: I need the UNION to combine the results.

Line 7: The file for the library itself contains just one row/record. I still need to rename the object size column using the AS to match the column name from the first part of this Union statement.

The result matches the total library size returned by LIBRARY_INFO:

Total size ---------- 9269248

Now if I need to know the total size of a library I can just go to one source to get it.

What about getting results for a group of libraries? LIBRARY_INFO only returns the results for one library, if I want to return the results from multiple libraries I need to be a bit creative.

What I want is a list of all the libraries that start with the characters "MY" and their total size. To get the list of libraries I am going to use the OBJECT_STATISTICS table function to get a list of all the libraries that start with "MY", and join that to the LIBRARY_INFO table function.

01 SELECT A.OBJNAME, 02 (SELECT LIBRARY_SIZE FROM TABLE(QSYS2.LIBRARY_INFO(A.OBJNAME))) 03 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLSIMPLE','LIB')) A 04 WHERE A.OBJNAME LIKE 'MY%'

Line 1: The column OBJNAME will contain the name of the library. I have prefixed the column with an "A" so that Db2 will know that this column come from the OBJECT_STATISTICS table function.

Line 2: This is a subselect that gets the result from LIBRARY_INFO. Notice that the library name from OBJECT_STATISTICS is used as the parameter for LIBRARY_INFO.

Line 3: This is where I pass to OBJECT_STATISTICS what I want returned in the results. *ALLSIMPLE just returns the Object system name, Object long name, and Object type columns. This is a faster approach and as I only need the Object name this is preferred.

Line 4: I only want in the results the libraries that start with "MY".

OBJNAME LIBRARY_SIZE ------- ------------ MYLIB 9269248 MYLIB2 1073152 MYLIB3 4800512 MYLIB4 2723840

A simple and effective way to get the total size of libraries just in one SQL statement.

 

You can learn more about the LIBRARY_INFO SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.