As mentioned in my previous article, this article is all about when disk space spikes all of a sudden and how to trace the culprit.

The last few year more and more of my tasks have moved from 5250 emulation to Run SQL scripts. So finding the reason why disk space suddenly spikes is no exception. Using some of the given examples, tailoring them a bit and adding a bit myself, enables me not only to learn better SQL, but also to get results faster. So I use the button “Save as New Example…” a lot and have added a Category of my own to keep my examples apart from the IBM examples. Th SQL script I want to talk about is the one I use when disk space spikes all of a sudden. Although the script I am using can be more automated, up to now I have only used it interactively, so let me get into the details of the script

What this script does is using the Db2 for i Service called “QSYS2.USER_INFO”. By creating a table based on a few of the available fields available in that view and adding a Time Stamp field. You are able to to make a few snaps allowing you to see where the growth of space usage is going to. I am using the library QRPLOBJ for storing my data, knowing that an IPL will always cleanup what I store there.

The script itself does contain documentation, explaining what is does in every step of the script.

As always the latest greatest version of IBM i the one current with the latest PTF groups and TRs.

I do like the “IF EXISTS” and it can be compared with a CPF message you monitor for.

The field “storage_used” is in kilobytes, but having that data available in Gb makes it more easier to digest.

The with data does make sure you not only define the table but also add a record, depending on your select statement one or more are directly added to the table you create.

Having a first quick look if everything is looking as expected does not do any harm here.

Adding more data using the INSERT statement allows you to view the change in “Storage_used” by a user.

If the numbers of storage used are for different users very close, it might be better to oder first by “AUTHORIZATION_NAME” followed by “STORAGE_USED_IN_KB”.

If you want to use the “Run All” button in the Run SQL Script window, using this is way to capture more data.

If disk space is running out quickly the capture interval and the capture number can be kept to a minimum.

If a quick visual scan of the data captures does not allow you to identify the user repeat the capture process until it does.

Once you have detected the user by scanning the results of the select statement, continue with the investigation.

Just to check if this user is active. Please be aware that activity and I/O is an indication that disk space is still consumed.

If the user has a job active which does a lot of I/O it does help to know if he has open files.

Once you have detected that files are open, you want to know which.

As mentioned in the test of the image above, the Open File information can be added in this SLQ statement to limit your search.

Limiting ourselves to QSYS.LIB is not always a good idea is something I learned form the past.

As IBM i is what I call “the spider in the database web” for more and more customers, we see a growth in ad hoc SQL statements causing disk space usage to spike. In most cases temporary storage does play a role here. For that purpose IBM has developed something called the Query Supervisor. The Query Supervisor allows you to alert you whenever a temporary threshold is exceeded.

For more information have a look at the link below:

IBM Support – Query Supervisor

As this piece of functionality does not cover all spikes, the SQL code in this article has enabled me many times to find out where the disk space was used. I hope it will help you achieving the same.