Guru: Object Usage Statistics
July 10, 2023 Bob Cozzi
A long time ago in a system far, far away, IBM added the Date Last Used to the Object Description for all objects on the system. The term “last used” means different things to different object types; for *FILE objects it means the file was opened or its description was changed, for *PGM objects it means the program was called, for other objects it generally means what you might think it means (viewed, retrieved/read, updated, etc.) The only exceptions are Device Descriptions which get updated when a *DEVD changes from “Vary On Pending” to some other status.
The Date Last Used is updated no more than once per day. If that file is open 1 million times on any given day, only the first open causes the Date Last Used to be set to “today’s” date. While the Date Last used value is stored as an 8-byte system timestamp (date and time components) only the date portion of that timestamp is valid and the time portion should be discarded.
Let’s look at mythical program: PGMA
Creation Date: 21 JUNE 1988
Last Used Date: 07 JUNE 2023
The PGMA *PGM object was created 35 years ago, it was last used on 7 JUNE of this year. This means the object was recently used, just a few weeks ago as of this writing, so we can arguably say this object is being regularly utilized, right? What if during the well over 12,000 days since the object was created, it was called only once? What if on 7 June a developer started debug on PGMA and then called it with a breakpoint at stmt 1 to see what it was doing and then exited the program? Sure, the Date Last Used is updated, but that date isn’t telling us too much.
In some scenarios the Last Used Date is updated but doesn’t really mean the object was updated in a production environment. Date Last Used is a great property, but by itself, doesn’t tell use the whole story.
Days Used Count
Another attribute related to when objects are used is the Days Used counter. This is an integer value that is incremented whenever the Date Last Used is updated. So as mentioned, at most, once per day. This counter helps up complete the story. If we look at our mythical PGMA description again and included this Days Used counter, we see the following:
Creation Date: 21 JUNE 1988
Last Used Date: 07 JUNE 2023
Days Used Count: 2
This tells us that over the last 35 years, PGMA has been called a total of twice. A program used twice in 35 years isn’t exactly something the organization is really using. But this, too, is not the whole story.
Days Used Counter
The Days Used Counter has a companion property named “Days Used Count Reset Date.”
This is frequently blank/null or has a specific date in it. It is set by the CHGOBJD (Change Object Description) command’s USECOUNT parameter. When USECOUNT(*RESET) is specified, this Days Used Count Reset Date is set to the job’s date, and the Days Used Count is set to zero.
The Days Used Count is number of days on which the program was called (or file was open) since:
- If the Days Used Count Reset Date is not blank/null:
- The Days Used Count Reset Date
- If the Days Used Count Reset Date is blank/null:
- The object was created.
Housekeeping
We have a number of SQL Functions in our SQL Tools offering, and a client wanted to be able to extract a list of objects that aren’t used or are rarely used. But they also wanted to know how many days the object was available to be used so they could calculate a ratio of objects that are used vs available. For example, if an object was created 35 years ago and used twice, it is likely a candidate for deletion. However, if an object was created 35 days ago, and used twice, they probably want to retain it.
To solve this problem, I applied my Thinking in SQL™ methodology and solved the problem. First, I wanted to define the resulting values that provide the kind of information the client wanted.
- Object name, type, size, owner, description, etc.
- Creation Date
- Object Ages (in days)
- Last Used Date
- Days Used Count (in days)
- Days Used Count Reset Date
- Days Object has been available to be used
- Percentage of days when the object was used
I use a Common Table Expression (CTE) to create the columns and do the calculations, then a simply SELECT statement is used to access that data. I find this to be the clearest way to describe an SQL solution when derived fields are involved.
The CTE name I use is OBJUSAGE. In this example I also explicitly name each resulting column (field) that is produced in that CTE. This is an optional feature, that in some cases can simplify the naming of the resultSet columns. For example, the OBJLONGSCHEMA is cast to another form, and the casting of the dates as well as the age calculation all benefit from this option.
WITH objUsage (objlib, objname, objtype, objattr, lastUsedDate, crtdate, objAge,
usageResetDate, days_used_counter, days_Available, usage_Percentage,
objowner, objcreator, objsize, objtext) AS (
SELECT cast(left(objLongSchema,10) as varchar(10)),
objname,
objtype,
objattribute,
CAST(last_used_timestamp AS DATE) AS LastUsedDate,
CAST(objcreated AS DATE) AS CrtDate,
DAYS(current_date) - DAYS(objcreated) AS ObjAge,
CAST(last_Reset_Timestamp AS DATE) AS UsageResetDate,
days_used_count AS Days_Used,
DAYS(current_date) -
DAYS(COALESCE(last_Reset_timeStamp, objcreated)) + 1
Days_Available,
CAST(
100.00 *
(Dec(days_used_count, 7, 2) /
dec( 1 + DAYS(current_date) -
DAYS(COALESCE(last_Reset_timeStamp, objcreated)), 7, 2))
AS DEC(5, 2)) Usage_Percentage,
objowner,
objDefiner,
objsize,
objtext
-- Change the two parameters to '*ALLUSR' and '*ALL'
-- to scan your entire system, otherwise change the
-- Library and object type parameters as desired.
FROM TABLE(object_statistics('PRODLIB', '*PGM *SRVPGM')) OL
)
SELECT *
FROM objUsage
-- To speed up your initial results, omit the ORDER BY clause entirely
ORDER BY Usage_Percentage DESC,
days_used_counter DESC,
objlib,
objname
I will typically use our SQL Tools OBJECT_LIST instead of the IBM OBJECT_STATISTICS since ours returns identical results starting with IBM i V7R2 and later, but the CAST of the OBJLONGSCHEMA in this context seems to work well enough on V7R2 and later.
You can cut/paste this code directly, or pull it down from my GITHUB page at this link: https://github.com/bobcozzi/OBJUSAGE
You cut/paste it directly into IBM ACS RUNSQL Scripts or store it in a source file member and run it via Command Entry using SQL iQuery. Here’s an example output from one of my test partitions:
As you can see, we have never reset the Days Used Count Reset Date, so it is NULL in 100 percent of my objects listed. When that Reset Date is null, the SQL statement uses the object’s creation date to calculate the object’s age, its Days Available and Usage Percentage columns.
If you change the ORDER BY clause, you can list the results so that the least used objects appear first, however in my shop, we have “tons” of unused objects, so I decided descending order was a better choice. In a production environment the opposite would be true.
Object Age Versus Days Available
In the above SQL stmt, the two columns named OBJAGE (Object Age, in Days) and DAYS_AVAILABLE (Days object has been available to be used) differ by 1 day. The reason is that if an object was created “yesterday” then as of “today” its age is 1 day. However, it has been available for use over 2 dates therefore the availability is 2 days. The later of the two is used in the Percentage of Days Used calculation.
Remember, I am by no means a great SQL developer, but I am a fairly decent programmer who writes and maintains SQL functions all day. Recently I discovered I had the Days Used object property all wrong, and thanks to readers like you, we now have a better understanding of what this property is, and how it can be used to determine which objects are effectively obsolete.
That’s all there is to it.
Bob Cozzi is an IBM i contractor and consultant as well as the author of The Modern RPG Language, developer of SQL iQuery and SQL Tools, and a speaker on SQL and RPG IV topics you can actually use.
