What can you do to determine what is the cause behind a sudden fast-growing use of disk space? I'll explain it here.
The trigger
Friday 29th January 2021 I am in a Teams meeting which is drawing to an end, when the phone is ringing. A quick text message is the response, telling the caller that I will call back within 10 minutes. The moment I prepare myself for calling back, another customer is calling. This call takes some time and again the phone is ringing, I recognize the number from the first call. I quickly finish my call and call back. Soon I understand their urgency, the system is filling up disk speed at an alarming speed.
They ask me for advice. Based on previous experience, it is likely that an ad hoc query is filling up the system. I give them the advice to look at the SQL tasks on the system, look at disk I/O to determine the cause. I offer to have a look myself, but the caller tells me that their system administrators will look into it.
A call in the afternoon, tells me that it took those administrators two and half hours to find the cause. A task from IBM i Web Access had created an XML file in the IFS of nearly half a TB in size. This task itself was initiated by an ad hoc query request of a user.
What happened that day, is the inspiration for this article. What can you do to determine what is the cause behind a sudden fast-growing use of disk space. It is here when DB2 for i Services can be of great help, without costing you time by looking at the 5250 green screen.
Why DB2 for i Services is a lifesaver
Disk space is used by users, so when disk space is used, the storage consumption of at least one user is growing. The SQL script shown below will help you to bring that information to the daylight.
—
— Inventory of Users with their Storage Cunsumption
SELECT AUTHORIZATION_NAME, PREVIOUS_SIGNON, STATUS, STORAGE_USED, LAST_USED_TIMESTAMP
FROM QSYS2.USER_INFO ORDER by STORAGE_USED DESC;
— Create a table with the information allowing to review the growth of Storage Used
CREATE TABLE QRPLOBJ.USER_Storage_Usage AS
(SELECT TIMESTAMP, AUTHORIZATION_NAME, PREVIOUS_SIGNON, STATUS, STORAGE_USED,
LAST_USED_TIMESTAMP
FROM QSYS2.USER_INFO)
WITH DATA;
— Now you have the table sort it in the right order
SELECT *
FROM QRPLOBJ.USER_Storage_Usage
ORDER BY STORAGE_USED DESC, AUTHORIZATION_NAME, CREATION_TIMESTAMP;
—
— Dertermine the Interval needed to collect the data allowing for enough time to be able to measure a change
CL:DLYJOB DLY(90);
insert into QRPLOBJ.USER_Storage_Usage
(SELECT TIMESTAMP, AUTHORIZATION_NAME, PREVIOUS_SIGNON, STATUS, STORAGE_USED,
LAST_USED_TIMESTAMP
FROM QSYS2.USER_INFO);
— Collect an extra set of data
CL:DLYJOB DLY(90);
insert into QRPLOBJ.USER_Storage_Usage
(SELECT TIMESTAMP, AUTHORIZATION_NAME, PREVIOUS_SIGNON, STATUS, STORAGE_USED,
LAST_USED_TIMESTAMP
FROM QSYS2.USER_INFO);
— Review the table to detect the User with the most growth
SELECT *
FROM QRPLOBJ.USER_Storage_Usage
ORDER BY STORAGE_USED DESC, AUTHORIZATION_NAME, CREATION_TIMESTAMP;
— Now you have the user, have a look at his tasks if disk space usage is still growing
SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_CPU_PERCENTAGE
FROM TABLE (
QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => ‘ALL’)
) X
WHERE X.AUTHORIZATION_NAME = ‘RUDI’;
— If you are current with the latest DB2 PTF GROUP => Open File information is available
SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_CPU_PERCENTAGE, X.OPEN_FILES
FROM TABLE (
QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => ‘ALL’)
) X
WHERE X.AUTHORIZATION_NAME = ‘RUDI’;
— The last thing you need to know is the size of the object owned by the user
— For that IBM has an example ready for you to use
— All the text below is taken from the example
— category: IBM i Services
— description: Storage – Storage details for a specific user
—
— Retrieve the details of objects owned by a specific user
— Note: replace user-name with the user profile name of interest
SELECT b.objlongschema, b.objname, b.objtype, b.objattribute, b.objcreated, b.objsize, b.objtext, b.days_used_count, b.last_used_timestamp,b.* FROM
TABLE (QSYS2.OBJECT_STATISTICS(‘ALLUSRAVL ‘, ‘LIB’) ) as a,
TABLE (QSYS2.OBJECT_STATISTICS(a.objname, ‘ALL’) ) AS b
WHERE b.OBJOWNER = ”
ORDER BY b.OBJSIZE DESC
FETCH FIRST 100 ROWS ONLY;
— If it is not an object in a library, the example below will help you further
— again all text below is taken from an example ready for you to use
— you do need to replace the value by the correct user name
— category: IBM i Services
— description: IFS – IFS storage consumed for a specific user
— minvrm: V7R3M0
with ifsobjs (path, type) as (
select path_name, object_type
from table(qsys2.object_ownership(”)) a
where path_name is not null
)
select i., data_size, z.
from ifsobjs i, lateral (
select * from
table(qsys2.ifs_object_statistics(
start_path_name => path,
subtree_directories => ‘NO’))) z
order by data_size desc;
Disclaimer: This script is by no means perfect and not the solution for every Disk Space growth issue you can run into, but I do hope it does get you started by diving into the world called SQL and IBM i Service in particular.
It does give you enough clues, to start investigation yourself. I say welcome in the world of IBM i Services!
