Finding really big objects in my libraries quickly

The shout goes out: "The IBM i is at 92% of disk capacity! Delete as much test and work stuff as you can!"

We don't have time to run the best way to find the largest objects on this IBM i. At this point I am just concerned with the objects in my personal, work and test libraries.

I don't want to use the Display object Description command, DSPOBJD, as I will have to build a file, to then query, and that will only add to the amount of used disk space.

Fortunately Db2 for i provides me with a solution I can get data from immediately. In a prior post written about using the OBJECT_STATISTICS table function to find objects that have not been used for a long time. I can use the same table function to identify find the largest objects in my personal, work, and test libraries.

For now the only columns I care about are:

• OBJNAME:  Object name
• OBJATTRIBUTE:  Attribute of object
• OBJSIZE:  Object size
• Last used date

This can be put into a SQL Select statement:

01 SELECT A.OBJNAME AS OBJECT, 02 A.OBJATTRIBUTE AS ATTRIBUTE, 03 A.OBJSIZE AS SIZE, 04 CAST(A.LAST_USED_TIMESTAMP AS DATE) AS LAST_DATE 05 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','ALL')) A 06 ORDER BY A.OBJSIZE DESC

I have decided to change the result column headings into something I like, rather than display the column names.

Line 4: The last used date is contained with the last used timestamp column. By using a CAST I can change the column to a date. My default date format is *MDY, so I will see the date in that format.

Line 5: I am passing to the table function the name of the library I want to get results from, MYLIB, and which object types, ALL.

As I have several personal, work, and test libraries running this Select statement for each library is, in my opinion, not efficient. I want to get a list of the largest objects from these libraries all together.

Fortunately this table function offers me a "work around". I can use several special values in the first parameter, including *USRLIBL which will return the objects from the libraries in the user part of my library list only. Therefore, if the user part of my library list only contain my personal, work, and test libraries they will all be queried by the same SQL statement using this table function.

Edit Library List Type new/changed info Sequence Number Library 10 MYLIB 20 MYLIB2 30 MYTEST1 40 MYTEST2 50 MYTEST3 60 MYTEST4 70

I am going to change my Select statement to include the object's library name too:

01 SELECT A.OBJNAME AS OBJECT, 02 CAST(A.OBJLONGSCHEMA AS CHAR(10)) AS LIBRARY, 03 A.OBJATTRIBUTE AS ATTRIBUTE, 04 A.OBJSIZE AS SIZE, 05 CAST(A.LAST_USED_TIMESTAMP AS DATE) AS LAST_DATE 06 FROM TABLE(QSYS2.OBJECT_STATISTICS('*USRLIBL','ALL')) A 07 ORDER BY A.OBJSIZE DESC

Line 2: This is where I convert the "long schema name", 128 characters long, into a library name, of 10 characters.

Line 6: I have replaced the library name with *USRLIBL, so the table function will query the libraries I entered into the user portion of the library list.

Depending on the number of libraries, and objects within them, this can take some time to generate the results, which would look something like:

OBJECT LIBRARY ATTRIBUTE SIZE LAST_DATE ---------- ---------- --------- ----------- --------- DEVSRC MYLIB PF 15,642,624 01/06/19 I******** MYTEST1 PF 2,854,912 01/24/17 I******_X MYTEST1 PF 2,830,336 01/24/17 M******** MYTEST2 CBLLE 1,228,800 06/07/18 TWOSRC MYLIB2 PF 1,032,192 01/06/19 V******** MYTEST1 PF 745,472 01/06/19 I******_2 MYTEST1 PF 593,920 07/20/17 V******** MYTEST2 RPGLE 479,232 -

Using the results I can determine which objects in my libraries I can delete or clear.

 

You can learn more about the OBJECT_STATISTICS Db2 for i table function from the IBM website here.

 

This article was written for IBM i 7.3, and should work for the later 7.1 Technology Refreshes and newer.