Find Unused Objects on IBM i using SQL

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.

[Editors note: Due to the popularity of this article, I've enhanced the code included below to a full-fledged SQL Function. That source code is now available over on my GitHub page, linked below.]

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

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.

NOTE: Remember LinkedIn code examples, such as the below source code, can scroll left/right using the mouse or pointing device or the scrollbar on the bottom of the code window.

?select?
????od.objLONGSCHEMA as OBJLIB,  -- V7R2
    -- od.OBJLIB,  -- V7R3+
????od.objname,
????od.objtype,
????od.objAttribute?as?objAttr,
????od.objsize,
????od.objowner,
????od.objdefiner?AS?OBJCREATOR,
????CAST(od.objcreated?AS?DATE)?crtdate,
??????--?last-used-timestamp's?time?component?is?garbage,
??????--?so?we?throw?it?away?with?this?CAST?as?DATE
????CAST(od.last_used_timestamp?AS?DATE)?AS?LASTUSEDDATE,
????CASE?WHEN?od.LAST_USED_TIMESTAMP?IS?NULL?THEN?'*UNKNOWN'
?????????ELSE?LPAD(?CAST(
???????????????--?Check?if?the?object?is?"old"?using?the
???????????????--?MONTHS_BETWEEN?function?(introduced?in?V7R2)
?????CAST(MONTHS_BETWEEN(current_timestamp,?od.last_used_timestamp)?AS
??????????????DEC(7,?1))?AS?VARCHAR(10)),?10)
?????END?IDLE_MONTHS,??--?The?result?is?the?idle?period

?????--?If?you?are?on?V7R3?and?the?Created-on?System?name?is?useful,
?????--?then?include?these?two?additional?columns?that?are
?????--?not?available?on?V7R2
???????--???OD.CREATED_SYSTEM?,
???????--???OD.CREATED_SYSTEM_VERSION,
??????od.objtext
      -- Change library and object type as desired
????FROM?TABLE?(object_Statistics('*ALLUSR',?'*ALL'))?OD
????WHERE?OD.Last_used_timestamp?<?current_date?-?24?months?
          and LEFT(OD.OBJLONGSCHEMA,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([email protected])        

This command produces the same resultSet as the ACS session. It can be run from Command Entry or the Job Scheduler or where ever. 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.objLONGSCHEMA as OBJLIB,  -- V7R2
    -- od.OBJLIB,  -- V7R3+
????od.objname,
????od.objtype,
????od.objAttribute?as?objAttr,
????od.objsize,
????od.objowner,
????od.objdefiner?AS?OBJCREATOR,
????CAST(od.objcreated?AS?DATE)?crtdate,
??????--?last-used-timestamp's?time?component?is?garbage,
??????--?so?we?throw?it?away?with?this?CAST?as?DATE
????CAST(od.last_used_timestamp?AS?DATE)?AS?LASTUSEDDATE,
????CASE?WHEN?od.LAST_USED_TIMESTAMP?IS?NULL?THEN?'*UNKNOWN'
?????????ELSE?LPAD(?CAST(
???????????????--?Check?if?the?object?is?"old"?using?the
???????????????--?MONTHS_BETWEEN?function?(introduced?in?V7R2)
?????CAST(MONTHS_BETWEEN(current_timestamp,?od.last_used_timestamp)?AS
??????????????DEC(7,?1))?AS?VARCHAR(10)),?10)
?????END?IDLE_MONTHS,??--?The?result?is?the?idle?period

?????--?If?you?are?on?V7R3?and?the?Created-on?System?name?is?useful,
?????--?then?include?these?two?additional?columns?that?are
?????--?not?available?on?V7R2
???????--???OD.CREATED_SYSTEM?,
???????--???OD.CREATED_SYSTEM_VERSION,
??????od.objtext
      -- Change library and object type as desired
????FROM?TABLE?(object_Statistics('*ALLUSR',?'*ALL'))?OD
2)??WHERE?OD.Last_used_timestamp?<?current_date?-?&PERIOD?months?
          and LEFT(OD.OBJLONGSCHEMA,1) NOT IN ('#','Q');?        

I've 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([email protected])        

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!