RTVDSKINF / PRTDSKINF Goodbye!

Let’s summarize the operation of these two commands:

• RTVDSKINF is the command that collects information relating to the disk space occupied by objects, libraries, … Once launched, it is necessary to wait a while to allow the operating system to extract the information and is also able to run with an integer input ASP (* SYSBAS or IASP)
• PRTDSKINF generates a spoolfile containing the list of objects / libraries with indication of owner, weight, last use, …

In the absence of anything more flexible and dynamic, most system administrators have used them over time to monitor disk occupancy.

Fortunately IBM starting from V7R2 has enriched the QSYS2 library with functions capable of retrieving information on the health of the server including the OBJECT_STATISTICS.

This UDTF allows you to quickly trace the content of a library or a part of it simply with a SQL selection query and therefore allows a more detailed and clearly faster control of what happens inside the libraries.

Syntax

The syntax for using this function is as follows

OBJECT_STATISTICS (libraryName, ObjectType list, ObjectName)

In detail the parameters for the use of this UDTF are:

• libraryname: can contain the name of a library present on the disk or one of the following special values (* ALL, * ALLUSR, * ALLUSRAVL, * CURLIB, * LIBL, * USRLIBL)
• listTypeObject: may contain one or more object types (even without the * character in front) separated by a space or a comma
• objectName (optional): may contain the name of an object

Examples

Here are some usage examples:

• Space occupied by bytes from a library

SELECT SUM (OBJSIZE) FROM TABLE (QSYS2.OBJECT_STATISTICS ('PETRIS', 'ALL')) T 

• List objects of file type for owner contained in a library

SELECT OBJNAME, OBJTEXT, OBJOWNER FROM TABLE (QSYS2.OBJECT_STATISTICS ('PETRIS', 'FILE')) T WHERE OBJATTRIBUTE = 'PF' ORDER BY OBJOWNER

• List of unused objects present in the user part of the job library list used at most once sorted by decreasing weight

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS ('USRLIBL', 'ALL')) T WHERE DAYS_USED_COUNT <= 1 ORDER BY OBJSIZE DESC

For a complete control of all that is installed on the server it is also possible to create a table to be populated with the contents of each individual library and then also reason with Business Intelligence tools to create a dashboard of control of our IBMi.

We create a table that will contain all the information

CREATE TABLE PETRIS.OBJECTS (                      
   NAME VARCHAR (10) NOT NULL DEFAULT '',     
   TYPE VARCHAR (8) NOT NULL DEFAULT '',      
   OWNER VARCHAR (10) NOT NULL DEFAULT '',     
   CREATOR VARCHAR (10) NOT NULL DEFAULT '',     
   CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  
   SIZE DECIMAL (15, 0) NOT NULL DEFAULT 0,             
   TEXT VARCHAR (50) DEFAULT NULL,            
   SQL_NAME VARCHAR (128) DEFAULT NULL,           
   USED TIMESTAMP DEFAULT NULL,                        
   DAYS_USED DECIMAL (10, 0) NOT NULL DEFAULT 0,             
   RESET TIMESTAMP DEFAULT NULL,                        
   ASP_NUMBER DECIMAL (5, 0) NOT NULL DEFAULT 0,              
   ATTRIBUTE VARCHAR (10) NOT NULL DEFAULT '',     
   SQL_SCHEMA VARCHAR (128) NOT NULL DEFAULT '',    
   LIBRARY_TEXT VARCHAR (50) DEFAULT NULL,            
   SQL_TYPE VARCHAR (9) DEFAULT NULL)           

Then we create a new program that:

• clean the contents of the table

TRUNCATE TABLE PETRIS.OBJECTS 

• open a cursor and initially loop over the SYSSCHEMAS view

SELECT SCHEMA_NAME FROM QSYS2.SYSSCHEMAS

and for each library read perform the population by extracting the data from OBJECT_STATISTICS

INSERT INTO PETRIS.OBJECTS (SELECT OBJNAME, OBJTYPE, OBJOWNER, OBJDEFINER, OBJCREATED, 
 OBJSIZE, OBJTEXT, OBJLONGNAME, LAST_USED_TIMESTAMP, DAYS_USED_COUNT, LAST_RESET_TIMESTAMP, 
 IASP_NUMBER, OBJATTRIBUTE, OBJLONGSCHEMA, TEXT, SQL_OBJECT_TYPE 
 FROM TABLE (QSYS2.OBJECT_STATISTICS ('libraryname', '* ALL')))

NB: at the limit we can consider excluding the libraries owned by the user QSYS to avoid extracting those of the operating system (SELECT SCHEMA_NAME FROM QSYS2.SYSSCHEMAS WHERE SCHEMA_OWNER <> ‘QSYS’)

Once the complete list of objects is obtained, it is easy to find objects that occupy more space, heavier libraries, etc.

SELECT SQL_SCHEMA, SUM (SIZE) FROM PETRIS.OBJECTS GROUP BY SQL_SCHEMA ORDER BY 2 DESC

References

For more information, the official IBM documentation is available (https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/rzajq/rzajqudfobjectstat.htm)