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.
Thank you for yet another great post Simon! One minor comment:
ReplyDelete01 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