Matt Seeberger

IBM i Run SQL Scripts – Find a File Consuming Storage

I recently encountered a situation where storage on a system climbed by 12% within a 24 hour period. I wanted to find a direct and easy way to locate the problem object(s) so of course I turned to ACS Run SQL Scripts because it makes life easy. I was pleasantly surprised by how easy it was to run a query to examine the data. The one challenge when performing a search like this is that you have “System” Objects and IFS Objects. Thanks to Scott Forstie and team we have two resources available: System Objects = QSYS2.OBJECT_STATISTICS and IFS Objects = QSYS2.OBJECT_STATISTICS. For both of these you will need to be running 7.2 or later of the IBM i OS.

More information on what DB Level you have to be for these options is found here: IBM i Services (SQL)
More information about the QSYS2.OBJECT_STATISTICS service is found here: OBJECT_STATISTICS

Fortunately for me I found the results I was looking for in my first search; which I ran against QSYS2.OBJECT_STATISTICS.

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALL ', '*ALL') ) as a
    where OBJCREATED >= '2020-03-04 04:00:00.000000';

In this SQL statement I am looking at first *ALL libraries and then *ALL file types within the libraries. I further refined my search by adding the OBJCREATED filter and selecting the specific time frame. Here were my results:

You can see in the screen shot that a file was created at 10:50am that was fairly large. I was able to delete the file from a command line and the storage went back to normal.
NOTE: This file may have been deleted on it’s own when I closed out and disconnected from all of my SQL connections but I thought this was a good example to share this tool

Other Ways to Refine this SQL

Refine by date: I could have handled the date differently, since I knew I needed to look at the last 24 hours, and it would make the query more dynamic by typing it this way:

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALL', '*ALL') ) as a
    where OBJCREATED >= current date - 1 day;

Refine by Library and File Type: Further I could have specified the Library and the object type I was looking for by typing it this way:

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL', '*FILE') ) as a
    where OBJCREATED >= current date - 1 day;

Refine by Library and Multiple File Types: You can even include multiple file types like this:

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL', '*PGM *FILE') ) as a
    where OBJCREATED >= current date - 1 day;

Retrieve results quickly: Use *ALLSIMPLE to return the list quickly, omitting the detail information. 

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL','*PGM *FILE','*ALLSIMPLE')) as a
    where OBJCREATED >= current date - 1 day;

Thanks for reading and drop me a comment if you have thoughts or suggestions. I hope to create a post soon using the QSYS2.IFS_OBJECT_STATISTICS service.

Related