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(email@example.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(firstname.lastname@example.org)
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.
I tried running the original SQL, but gave up after it churned for 30 minutes with no output. Should it take that long?
Use the GitHub version. it runs very, very fast.
This code uses IBM i table function qsys2.object_statistics which generally takes long to run (even in a fast IBM i machine) when it obtains information from all objects in your system such as in this particular case, especially when you have only one CPU core or less and your disk response time is not that great. It also has date time arithmetic to do as well. Its run-time depend on how many objects it access for information. You can speed up its performance by deploying DB2 SMP feature which is now free of charge for all IBM i V7.x.
In my case, I have S914 machine with 0.3 core CPU allocation, 16GB memory, with SSD LUNs from FlashSystem5200. This query takes about 20 minutes for me : Statement ran successfully, with warnings (1,060,811 ms = 17.68 min)
Kent Milligan provides performance considerations when using IBM i Services with SQL in this blog post of his : IBM i Services are Great, but they’re NOT Magic at https://db2fori.blogspot.com/2021/03/ibm-i-services-are-great-but-theyre-not.html
That’s correct. It takes “centuries” if you do all libraries at once. I recommend doing it over a single library, and then submitting it to batch if you want to scan the entire system. And yes, a more granular approach to this function would be nice (vs all or nothing it is today) but it does work. 🙂 Also, thanks for the tip about performance differences with SMP. I still have yet to deploy that feature.
Since I have DB2 SMP, when I run SET CURRENT DEGREE = ‘3’; and then the main query, it finishes in less than a minute! DB2 SMP magic! But I suspect it also has to do with the repeating run as well (I change the value of month each time I run it.)
I get the following message when I attempt to run this in ACS:
SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token . was not valid. Valid tokens: , FROM INTO. Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is , FROM INTO. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: — Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. — If the error token is , correct the SQL statement because it does not end with a valid clause. Processing ended because the highlighted statement did not complete successfully
I has flagged the “.objtype,” in the second line.
I run it in Run SQL Script tool also (I use the latest release of ACS) and it works fine but I have to remove the entire first line (#default &PERIOD = 24;) and then change &PERIOD to 24 (or whatever value you want) in the WHERE clause (Bob’s comment no. 2).
I attempted to cut/paste the code and I was correct, there is a missing COMMA just after the SELECT od.objname line. It should have a trailing comma, as in:
SELECT OD.OBJNAME ,
missing a comma after “select od.objname”
I’d recommend you use the UDTF source code on my GitHub page.
Sometimes when people cut/paste from LinkedIn to other programs, LinkedIn leaves off or adds characters. That’s why we’ve updated this article to point at my GitHub which has the thing nicely wrapped up in an SQL Function that you can use by doing:
Select * from table(sqltools.oldObj_list(‘library-name’, ‘object-name’,’obj-type’))
thanks. Personally, I prefer a compact sql , avoiding many nesting, more readable IMHO, like this (months_between will be null if undefined, no need to case).
od.objdefiner AS OBJCREATOR,
DATE(od.last_used_timestamp) AS LASTUSEDDATE,
MONTHS_BETWEEN(current_timestamp, od.last_used_timestamp) AGE_MONTHS,
FROM TABLE (object_statistics(‘*ALLUSR’, ‘*LIB’, ‘*ALLSIMPLE’)) LL
CROSS JOIN TABLE (object_Statistics(LL.OBJNAME, ‘ALL’)) OD
WHERE LEFT(LL.OBJNAME, 1) NOT IN (‘Q’,’#’)
AND MONTHS_BETWEEN(current_timestamp, OD.last_used_timestamp) > 24
Another possible application, to monitor daily the “changed or introduced programs” or “introduced” in my production system by my team, I’ve scheduled a simple daily report, with the LIBs I need to monitor, simple as
OBJLIB, OBJNAME, OBJTYPE, OBJDEFINER, OBJCREATED, CHANGE_TIMESTAMP,
LAST_USED_TIMESTAMP, OBJTEXT, DAYS_USED_COUNT, SOURCE_FILE, SOURCE_LIBRARY,
SOURCE_MEMBER, CASE WHEN OBJTYPE = ‘*PGM’ THEN 0 ELSE 1 END ISPGM
FROM ( VALUES
(‘PGMLIB1’, ‘PGM SRVPGM’ ),
) t(lib, o)
CROSS JOIN TABLE(QSYS2.OBJECT_STATISTICS(lib, o)) b
WHERE OBJCREATED > CURRENT_DATE – 7 DAYS
ORDER BY ISPGM ASC, OBJTYPE DESC, OBJCREATED DESC