Pages

Wednesday, February 19, 2020

Additions to Object Statistics table function

Changes to the OBJECT_STATISTICS table function

One of my favorite SQL table functions, OBJECT_STATISTICS, was introduced in IBM i 7.2 and it is something I use on a regular basis to identify unused objects that can be deleted. Over the last two releases of IBM i and numerous Technology Refreshes new columns have been added to this table function making it, in my opinion, better. In this post I am going to describe three of these enhancements, and how I can see them helping me.

 

LAST_USED_OBJECT column

One of the things I found using OBJECT_STATISTICS was certain object types' Last Used Timestamp was not updated when the object is used. This would fool me into thinking that it was an unused object that could be deleted as it was not being used. Then I would find all of the other objects of the same type were missing the Last Used Timestamp too. I ended up building a complicated select statement, referencing certain object types and conditioning the values in columns, to make an output table I could then use to find obsolete objects.

The new LAST_USED_OBJECT column means I can simplify my statement, by removing the part of the statement checking object types.

If LAST_USED_OBJECT contains "YES" then the Last Used Timestamp column will be updated when the object is used. So let's see what I find when I look at the objects in my library.

01  SELECT OBJNAME AS "Name",
02         OBJTYPE AS "Type",
03         DATE(LAST_USED_TIMESTAMP) AS "Last",
04         LAST_USED_OBJECT,
05         DATE(CHANGE_TIMESTAMP) AS "Changed"
06    FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*ALL'))
07   ORDER BY OBJTYPE,OBJNAME

Lines 1 – 5: I am renaming the column headings for the results so they will fit on this page, apart from the new column.

Lines 3 and 5: I don't want the full timestamp value as I don't really care what time of the day the object was changed or used. I just care about the date. I am using the DATE function to perform the conversion from timestamp to date.

Line 6: I am passing to the table function the name of my library, MYLIB, and *ALL so that all the details of all the objects will be returned. In previous releases I would have to give a letter at the end of this line. If I failed to do so the statement would not be executed. With IBM i 7.4 and recent 7.3 TRs the letter has become optional.

Line 7: I want the results to be sorted by object type and then name.

Rather than list all of the objects in my library here are some examples:

Name     Type    Last       LAST_USED_OBJECT Changed
-------- ------- ---------- ---------------- ----------
BNDDIR   *BNDDIR 2019-12-07 YES              2019-12-01

WM       *CMD    -          YES              2019-08-25

TESTFILE *FILE   2019-12-08 YES              2019-12-08

MYJOBD   *JOBD   2019-12-27 YES              2019-08-25

MYMENU   *MENU   2019-12-27 YES              2019-08-25

MODULE1  *MODULE 2019-12-07 YES              2019-12-07

TESTMSGF *MSGF   2019-12-27 YES              2019-08-25

MYOUTQ   *OUTQ   2019-12-28 YES              2019-08-25

PROGRAM1 *PGM    2019-12-28 YES              2019-12-07

SRVPGM1  *SRVPGM 2019-12-28 YES              2019-12-07

I have to confess I am worried that the WM command proxy, that I use a lot, does not have a Last Used date.

The above is a bit of a disappointment as all of the results returned "YES" in the Last Used Object column. I decided to look at all of the objects on the IBM i partition I use to write these stories, and see what values are in the Last Used Object column for each object type.

01  SELECT OBJTYPE,LAST_USED_OBJECT
02    FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*ALL'))
03   WHERE LAST_USED_OBJECT IS NOT NULL 
04   GROUP BY OBJTYPE,LAST_USED_OBJECT
05   ORDER BY OBJTYPE,LAST_USED_OBJECT

Line 1: I only need the Object Type and Last Used Object columns in my results.

Line 2: I can use "*ALL" as the library name to get the information for every object in this IBM i partition.

Line 3: I found that some of the objects in the library QSYS have null in the Last Used Object column. I want to ignore those.

Line 4: By using the GROUP BY I get one row of results for each combination of the columns listed.

Line 5: If the results are not sorted by the same columns as the Group By I can get repeating data.

Rather than show the "raw data" I have put the returned results into a table to make it easier to understand:

OBJTYPE LAST-USED_OBJECT Object description
*ALRTBL NO Alert table
*AUTL NO Authorization list
*BNDDIR YES Binding directory
*CLD YES C locale description
*CLS YES Class
*CMD YES Command
*COSD NO Class of service description
*CSI YES Communications side information
*CTLD YES Controller description
*DEVD YES Device description
*DOC YES Document
*DTAARA YES Data area
*DTAQ YES Data queue
*EDTD NO Edit description
*EXITRG NO Exit registration
*FILE YES File
*FLR NO Folder
*FNTRSC YES Font resource
*FORMDF YES Form definition
*GSS YES Graphics symbol set
*IGCDCT NO DBCS character set conversion dictionary
*IGCSRT NO DBCS sort table
*IGCTBL NO DBCS font table
*JOBD YES Job description
*JOBQ YES Job queue
*JOBSCD YES Job schedule
*JRN NO Journal
*JRNRCV NO Journal receiver
*LIB NO Library
*LIND YES Line description
*LOCALE YES Locale
*MENU YES Menu description
*MGTCOL YES Management collection
*MODD NO Mode description
*MODULE YES Module
*MSGF YES Message file
*MSGQ YES Message queue
*NTBD NO NetBIOS description
*OUTQ YES Output queue
*PAGDFN YES Page definition
*PDG YES Print descriptor group
*PGM YES Program
*PNLGRP YES Panel group definition
*PRDAVL YES Product availability
*PRDDFN NO Product definition
*PRDLOD YES Product load
*RCT NO Reference code translate table
*SBSD YES Subsystem description
*SCHINDX YES Search index
*SQLPKG YES SQL package
*SQLUDT NO User defined SQL type
*SRVPGM YES Service program
*S36 NO System/36 machine description
*TBL YES Table
*TIMZON YES Time zone description
*USRIDX YES User index
*USRPRF YES User profile
*USRSPC YES User space
*WSCST YES Workstation user customization object

This is not a complete list of all the object types, click here for a full list and description of the all the possible object types in IBM i 7.4 .

Now let me put this into a statement to create an output table.

01  CREATE TABLE MYLIB.OLD_OBJECTS
02         FOR SYSTEM NAME "OLDOBJS"
03  AS (SELECT OBJNAME,OBJTYPE,
04             DATE(LAST_USED_TIMESTAMP) AS "LAST_USED_DATE",
05             LAST_USED_OBJECT,
06             DATE(CHANGE_TIMESTAMP) AS "CHANGE_DATE",
07             CASE WHEN LAST_USED_TIMESTAMP IS NULL
08                   THEN DATE(CHANGE_TIMESTAMP)
09             ELSE DATE(LAST_USED_TIMESTAMP)
10             END AS "CHECK_DATE"       
11        FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*ALL'))
12       WHERE ((LAST_USED_TIMESTAMP IS NULL 
13                 AND CHANGE_TIMESTAMP < '2019-12-08')
14             OR LAST_USED_TIMESTAMP < '2019-12-08'))
15  WITH DATA

Line 2: The name of this file is longer than ten characters, therefore, I am using the SYSTEM NAME to give the table a system compatible name, rather than the system giving this table the name OLD_OB0001.

Lines 7 – 10: This section of code builds what I call a "derived column", one where its contents are determined by the values in other columns. In this column if the Last Used Timestamp is null I want the Change Date, when the Last Used Timestamp is not null I want it.

Lines 12 – 14: The WHERE may look overly complicated, but it is not. The first part, lines 12 and 13, check if the Last Used Timestamp is null then check the Change Timestamp. Or, line 14, if it is not null check the Last Used Timestamp.

When I have the file I can use the simplest of SQL statements to display its contents:

SELECT * FROM MYLIB.OLD_OBJECTS

You can see how the Check Date column contains the greater of the two dates.

OBJNAME  OBJTYPE LAST_USED_DATE LAST_USED_OBJECT CHANGE_DATE CHECK_DATE
-------- ------- -------------- ---------------- ----------- ----------
PROGRAM2 *PGM    -              YES              2019-12-07  2019-12-07

MODULE1 *MODULE 2019-12-07      YES              2019-12-07  2019-12-07
MODULE2 *MODULE 2019-12-07      YES              2019-12-01  2019-12-07

BNDDIR  *BNDDIR 2019-12-07      YES              2019-12-01  2019-12-07

I can then go through the contents of this table and determine which objects to delete. I do this manually as for those objects that do not update the Last Used Date I need to be more careful to determine how they are used before deleting them.

 

Has someone changed any of the commands' defaults?

I have been burned by this several times. Someone changes a command's defaults, then when another release is installed the new version overlays the existing changed command, and someone gets angry that his/her change is now missing. For those programmers who let me know the commands they have changed, and what they changed, I have a CL program that can be run after a new release to change the defaults to be the way desired.

But there is always someone who will make a change and "forget" to let anyone else know what the change is.

Now I can use OBJECT_STATISTICS to check which commands have been changed. The column APAR_ID will contain "CHGDFT" for any command where the command's defaults have been changed. With this simple statement I can see which commands have been changed:

01  SELECT OBJNAME,OBJTEXT,
02         CHANGE_TIMESTAMP,APAR_ID
03    FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','*CMD'))
04   WHERE APAR_ID = 'CHGDFT'

On this IBM i partition only one command has had its defaults changed:

OBJNAME OBJTEXT        CHANGE_TIMESTAMP           APAR_ID
------- -------------- -------------------------- -------
CRTLIB  Create Library 2019-08-14 09:13:20.000000 CHGDFT

But it does not tell me what has changed. I still need to look at the command to work that out.

 

Quickest results

I am not sure when this was introduced, but if I just need a list of objects in a library, and not any of the rest of the information, OBJECT_STATISTICS has a "bare bones" version that will return the results quicker than the full version. It is controlled by the third parameter passed to the table function.

01  SELECT *
02    FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','*ALL','*ALLSIMPLE'))

If I pass *ALLSIMPLE I will only following columns will contain information, all others are returned as null.

  • OBJNAME:  Object name
  • OBJTYPE:  Object type
  • IASP_NAME:  Device description name of the independent auxiliary storage pool (IASP)
  • OBJLONGSCHEMA:  Long schema name (library)
  • OBJLIB:  Object library

Why would I bother to include the columns that contain nulls? Below I am just going to get the columns that contain information.

01  SELECT OBJNAME,OBJTYPE,IASP_NAME,
02         OBJLONGSCHEMA,OBJLIB
03    FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*ALL','*ALLSIMPLE'))

A sample of the results from my library looks like:

OBJNAME  OBJTYPE IASP_NAME OBJLONGSCHEMA OBJLIB
-------- ------- --------- ------------- ------
PROGRAM1 *PGM    *SYSBAS   MYLIB         MYLIB
RPG3PGM  *PGM    *SYSBAS   MYLIB         MYLIB

SRVPGM1  *SRVPGM *SYSBAS   MYLIB         MYLIB

MODULE1  *MODULE *SYSBAS   MYLIB         MYLIB

I would use the OBJLIB, and not the OBJLONGNAME, as they both contain the same information.

 

Other added columns

I am just going to list the other added columns to OBJECT_STATISTICS below:

  • IASP_NAME:  Device description name of the independent auxiliary storage pool, IASP
  • OBJECT_CONTROL_LEVEL:  Object control level for the object
  • PTF_NUMBER:  PTF that resulted in the creation of this object
  • APAR_ID:  Authorized Program Analysis Report, APAR, with this identification number associated with the last change
  • ALLOW_CHANGE_BY_PROGRAM:  Whether or not any changes other than the text or the days used count and reset date can be made to the object's description by the Change Object Description API, QLICOBJD
  • CHANGED_BY_PROGRAM:  Whether the object has been modified by the Change Object Description (QLICOBJD) API
  • PRIMARY_GROUP:  User profile that is the primary group for the object
  • STORAGE_FEED:  Storage status of the object data
  • ASSOCIATED_SPACE_SIZE:  Size, in bytes, of the primary associated space of the object
  • OPTIMUM_SPACE_ALIGNMENT:  Whether the primary associated space for the object has been optimally aligned
  • OVERFLOW_STORAGE:  If the object has overflowed the auxiliary storage pool it resides in
  • AUTHORITY_COLLECTION_VALUE:  Authority collection value used for the object when authority collection for objects is active on the partition

 

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

 

This article was written for IBM i 7.4 TR1 and 7.3 TR7.

1 comment:

  1. Thank you for yet another great post Simon! One minor comment:

    01 SELECT OBJTYPE,LAST_USED_OBJECT
    02 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*ALL'))
    03 WHERE LAST_USED_OBJECT IS NOT NULL
    04 GROUP BY OBJTYPE,LAST_USED_OBJECT
    05 ORDER BY OBJTYPE,LAST_USED_OBJECT

    Line 4: By using the GROUP BY I get one row of results for each combination of the columns listed.

    While "group by" will definitely cause your result set to have unique records, I personally prefer using DISTINCT for that and only user GROUP BY for scalar functions (count, min, max etc.). To me, DISTINCT is more explicit that we want unique records, not to mention that it says that right at the start of the statement and it's less typing and if I add any fields I don't have to do do it in 2 places:

    01 SELECT DISTINCT OBJTYPE,LAST_USED_OBJECT
    02 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*ALL'))
    03 WHERE LAST_USED_OBJECT IS NOT NULL
    05 ORDER BY OBJTYPE,LAST_USED_OBJECT

    Another option would be to just add COUNT(*) to that statement since we're doing all the work anyway and that comes with the added benefit that we'll it'll tell us many objects of each type we have, which might be nice to know:

    01 SELECT OBJTYPE,LAST_USED_OBJECT, COUNT(*)
    02 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*ALL'))
    03 WHERE LAST_USED_OBJECT IS NOT NULL
    04 GROUP BY OBJTYPE,LAST_USED_OBJECT
    05 ORDER BY OBJTYPE,LAST_USED_OBJECT

    Dan D

    ReplyDelete

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.