Guru: Find Unused Objects On IBM i Using SQL

May 15, 2023 Bob Cozzi

I have a client that uses SQL iQuery for nearly all “Report” type applications since SQL iQuery Version 2. They asked if they could create a report that listed all the unused objects in their user libraries. They also wanted the option to output to Excel-compatible format if desired.

Since SQL iQuery allows you to output the results of your SELECT statements to any of its supported formats, Excel-compatible is given.

Note: the code for this article is avaialble on GitHub at this link.

There were a couple of approaches. I could use our other product, SQL Tools OBJECT_LIST which returns the Last Used Date and most other Object Description attributes. But I decided to use only the IBM OBJECT_STATISTICS function, and the IBM i V7R2 compatible implementation of it.

The code below accomplishes the task at hand. It first creates a list of User library names using the *ALLUSR and *ALLSIMPLE parameters. This is about the fastest user-facing way to create a list of library names on the system. I further filter it by omitting library names that start with the letter ‘Q’ and pound sign (“hashtag” if you’re under 40).

Then using a Lateral Join I generate a list of objects in each of those libraries and, using the MONTHS_BETWEEN SQL function, I select only objects that have NOT been used in more than 24 months.

You may want to adjust that 24-month option, but this client has a “new” Power9 and it is only 2 years old presently. So they’re really looking for stuff they haven’t used since the upgrade occurred. You can also include a test for the Last Used Date to be NULL in which case you would include “never used” objects as well.

Here’s the original, full SQL statement to accomplish the requirement.

SELECT od.objname
       od.objtype,
       od.objsize,
       od.objowner,
       od.objdefiner AS OBJCREATOR,
       CAST(od.objcreated AS DATE) crtdate,
       CAST(od.last_used_timestamp AS DATE) AS LASTUSEDDATE,
       CASE
         WHEN od.LAST_USED_TIMESTAMP IS NULL THEN '*UNKNOWN'
         ELSE
           LPAD(
             CAST(
               CAST(
                 MONTHS_BETWEEN(current_timestamp, od.last_used_timestamp) AS
                 DEC(7, 1)) AS VARCHAR(10)), 10)
       END AGE_MONTHS,
       od.objtext
  FROM TABLE (
         object_statistics('*ALLUSR', '*LIB', '*ALLSIMPLE')
       ) LL,
       LATERAL (
         SELECT *
           FROM TABLE (
               object_Statistics(LL.OBJNAME, '*ALL')
             ) D
           WHERE MONTHS_BETWEEN(current_timestamp, D.last_used_timestamp) > 24
       ) OD
  WHERE LEFT(LL.OBJNAME, 1) NOT IN ('Q','#');

Here is a look at the output, when run within IBM ACS. (Looks like Gabrielle creates a lot of archived files!)

To run this statement in SQL iQuery, you can simply save it to a source file member, for example OLDOBJ (Old Objects) and then run the CL command RUNiQRY to execute the SQL statement, as follows.

RUNiQRY SRCFILE(myscripts/qsqlsrc) SRCMBR(OLDOBJ) OUTPUT(*EXCEL)
          EMAIL(bob@sqliquery.com)

This command produces the same resultSet as the ACS session. It can be run from Command Entry or the Job Scheduler or wherever. The output can be routed to our Excel-compatible format (we use SpreadSheetML) and then it can be automatically emailed to the User. You’re welcome!

You can enhance the SQL source member to include more SQL iQuery Script stuff. This would give the end-user the ability to pass in the desired period of months to use in the SQL statement itself. For example, you can update the source code as follows:

  -- Initialize the Session Var to 24 months 
1) #default &PERIOD = 24;
SELECT od.objname,
       od.objtype,
       od.objsize,
       od.objowner,
       od.objdefiner AS OBJCREATOR,
       CAST(od.objcreated AS DATE) crtdate,
       CAST(od.last_used_timestamp AS DATE) AS LASTUSEDDATE,
       CASE
         WHEN od.LAST_USED_TIMESTAMP IS NULL THEN '*UNKNOWN'
         ELSE
           LPAD(
             CAST(
               CAST(
                 MONTHS_BETWEEN(current_timestamp, od.last_used_timestamp) AS
                 DEC(7, 1)) AS VARCHAR(10)), 10)
       END AGE_MONTHS,
       od.objtext
  FROM TABLE (
         object_statistics('*ALLUSR', '*LIB', '*ALLSIMPLE')
       ) LL,
       LATERAL (
         SELECT *
           FROM TABLE (
               object_Statistics(LL.OBJNAME, '*ALL')
             ) D
2)      WHERE MONTHS_BETWEEN(current_timestamp, D.last_used_timestamp) > &PERIOD
       ) OD
  WHERE LEFT(LL.OBJNAME, 1) NOT IN ('Q','#');

I have made two modifications:

1) Adds a directive that if the end-user does not pass in a value for the &PERIOD session variable, it uses 24 as its value.
2) Uses the session variable &PERIOD in the SQL statement to control the WHERE clause.

The end user or CL program can specify a variable value to pass to the script using the SETVAR parameter of the RUNiQRY command.

RUNiQRY SRCFILE(myscripts/qsqlsrc) SRCMBR(OLDOBJ) OUTPUT(*EXCEL)
         SETVAR(( PERIOD 36 )) EMAIL(bob@sqliquery.com)

In this RUNiQRY command, I’ve indicated that a Session Variable named PERIOD is to be created and assigned the value of 36. Since PERIOD is created by the RUNiQRY command itself, the #DEFAULT function does not execute and &PERIOD represents 36 instead of 24. So the result of the SQL statement is objects older than 36 months are returned.

SQL iQuery Script also supports 5250 prompting. You can simply prompt the end-user for a value. The Prompter in SQL iQuery is pretty cool and is a bit much for this article, so perhaps I’ll cover it another time. For now, enjoy locating your old objects!

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.