Wednesday, February 27, 2019

Finding really big objects in my libraries quickly

quickly finding size of objects in library using object_statistics

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.

8 comments:

  1. Very useful, Thank you for sharing

    ReplyDelete
  2. I’ve recently found sql view and schemas and I am amazed on how it makes my automations easier

    ReplyDelete
    Replies
    1. You are so right.
      In my opinion if you, as a programmer, are not using SQL like this you are doing yourself a disservice and making your work unnecessarily over complicated.

      Delete
  3. Very Helpful Simon. Million thanks for sharing. This make things more easier.

    ReplyDelete
  4. Does the profile that runs this need *ALLOBJ? If say the largest object on the system is one the profile running the query doesn't have authority to, will it still show on the report since it's coming from a system table?

    ReplyDelete
    Replies
    1. Quoting IBM's KnowledgeCenter for OBJECT_STATISTICS:
      "If the user has *EXECUTE authority to the library, and both *OBJOPR and *READ authority to an object, full details are returned."

      If you are looking at the objects in your libraries I would hope you have that authority.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.