Getting information about the partition's disk drives using SQL

Update August 13, 2024: New columns added to the SYSDISKSTAT View and Table function. Read about it here.

---

Back in IBM i release 7.2 the SYSDISKSTAT SQL View was added. It was introduced to give similar information to what can be see using the Work with Disk Status command, WRKDSKSTS, and allow us to do more with that information. Over the next couple of releases there have been new columns added to it making it ever more useful. I have to admit I have been remiss not writing about it until now.

In the latest Technology Refreshes, IBM i 7.4 TR4 and 7.3 TR10, 37 new columns were added. There is also now a SYSDISKSTAT SQL Table Function, more about that later. In the examples I am going to give I am not going to use all 37 of the new columns, therefore, if you want to learn about them all click to the links to the IBM documentation at the bottom of this post.

With this statement I am going to get a snapshot of the which disks are the top five when it comes to the percent of the disk is used:

01 SELECT RESOURCE_NAME AS "Resource", 02 SERIAL_NUMBER AS "Serial No.", 03 RESOURCE_STATUS AS "Status", 04 UNIT_TYPE AS "Type", 05 CASE UNIT_TYPE 06 WHEN 0 THEN 'No' 07 WHEN 1 THEN 'Yes' 08 END AS "SSD?", 09 PERCENT_USED AS "% used", 10 UNIT_SPACE_AVAILABLE_GB "Avail (GB)", 11 TO_CHAR(TOTAL_READ_REQUESTS,'999G999G999') As "Tot reads", 12 TO_CHAR(TOTAL_WRITE_REQUESTS,'999G999G999') AS "Tot writes" 13 FROM QSYS2.SYSDISKSTAT 14 ORDER BY PERCENT_USED DESC,RESOURCE 15 LIMIT 5 ;

Line 1: RESOURCE_NAME is one of the new columns that will show the system assigned name of the disk unit. I am giving all these columns headings to reduce the width of the results to fit on this page.

Line 2: SERIAL_NUMBER is another new column that shows the serial number of the disk unit.

Line 3: RESOURCE_STATUS is not a new column, it shows whether the resource is active or not active/passive.

Line 4: UNIT_TYPE is another old column. This shows whether the drive is a solid state disk (SSD), "1", or not, "0".

Lines 5 – 8: In my opinion "0" and "1" are not very helpful. Therefore, I am going to use a CASE statement to translate the "0" and "1" to "No" and "Yes".

Line 9: PERCENT_USED is an old column that gives me the percent of the disk unit that has been used.

Line 10: UNIT_SPACE_AVAILABLE_GB is another new column that gives me in GB the amount of disk space available for this drive. There is an old column UNIT_SPACE_AVAILABLE that gives the same information but in bytes.

Line 11: TOTAL_READ_REQUESTS is a new column that gives the number of read requests made to the drive since the last IPL. I am using the TO_CHAR SQL Function to add thousand separators to format the number to make it easier to understand.

Line 12: TOTAL_WRITE_REQUESTS is a new column to show the number of write requests made of the drive since the last IP. I am using the TO_CHAR Function to format the number too.

Line 13: The SQL View SYSDISKSTAT is found in the library QSYS2.

Line 14: The order clause will return the results with the highest used disk drives coming first.

Line 15: I only want to return the first five results.

What do those results look like?

Resource Serial No. Status Type SSD? % used Avail (GB) Tot reads Tot writes -------- ------------ ------- ---- ---- ------ ----------- --------- ---------- DMP013 Y047800000E1 ACTIVE 0 No 34.837 57 6,931,707 4,072,450 DMP014 Y047800000E1 PASSIVE 0 No 34.837 57 4 1 DMP019 Y047800000E1 ACTIVE 0 No 34.837 57 7,138,584 4,079,590 DMP021 Y047800000E1 PASSIVE 0 No 34.837 57 4 1 DMP017 Y047800000E3 PASSIVE 0 No 31.420 60 4 1

Another group of new columns are ones that display the elapsed time, input/output. reads, write, etc. for the disks. I can use this SQL statement to view the elapsed data:

01 SELECT RESOURCE_NAME, 02 ELAPSED_TIME, 03 ELAPSED_IO_REQUESTS, 04 ELAPSED_REQUEST_SIZE, 05 ELAPSED_READ_REQUESTS, 06 ELAPSED_DATA_READ, 07 ELAPSED_DATA_WRITTEN, 08 ELAPSED_PERCENT_BUSY 09 FROM QSYS2.SYSDISKSTAT 10 WHERE RESOURCE_NAME LIKE 'DMP01%' 11 ORDER BY 3 ;

All of the columns in this statement are new.

Line 1: I have already explained what the RESOURCE_NAME column contains.

Line 2: ELAPSED_TIME the number of seconds that have passed since when the gathering of the data was started and now.

All the columns below are measurements taken during the elapsed time.

Line 3: ELAPSED_IO_REQUESTS Average number of I/O requests, in KB.

Line 4: ELAPSED_REQUEST_SIZE Average size of I/O requests, in KB.

Line 5: ELAPSED_READ_REQUESTS Average number of requests to transfer data from the disk.

Line 6: ELAPSED_DATA_READ Average amount of data, in KB, transferred from the disk unit per request.

Line 7: ELAPSED_DATA_WRITTEN Average amount of data, in KB, transferred to the disk unit per request.

Line 8: ELAPSED_PERCENT_BUSY Estimated percentage of time the disk is used.

Line 10: I am only interested in the resources that start with the characters DMP01.

Line 11: I am sorting the results by the third field in the results, ELAPSED_IO_REQUESTS.

When I first connect to the partition all the elapsed fields are null, as no time has passed.

ELAPSED_ ELAPSED_ ELAPSED ELAPSED ELAPSED ELAPSED_ RESOURCE ELAPSED IO_ REQUEST _READ_ _DATA_ _DATA_ PERCENT_ _NAME _TIME REQUESTS _SIZE REQUESTS READ WRITTEN BUSY -------- ------- -------- -------- -------- ------- ------- -------- DMP019 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP014 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP013 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP015 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP016 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP017 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP018 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL>

After making myself a cup of tea and grabbing a couple of cookies, enough time had passed to see some results when I ran the SQL statement again:

ELAPSED_ ELAPSED_ ELAPSED ELAPSED ELAPSED ELAPSED_ RESOURCE ELAPSED IO_ REQUEST _READ_ _DATA_ _DATA_ PERCENT_ _NAME _TIME REQUESTS _SIZE REQUESTS READ WRITTEN BUSY -------- ------- -------- -------- -------- ------- ------- -------- DMP014 6909 0.0 0.0 0.0 0.0 0.0 0.0 DMP016 6909 0.0 0.0 0.0 0.0 0.0 0.0 DMP017 6909 0.0 0.0 0.0 0.0 0.0 0.0 DMP015 6909 1.5 12.6 0.7 24.3 5.6 0.1 DMP018 6909 1.7 12.5 0.5 25.1 6.1 0.1 DMP013 6909 1.7 13.7 0.6 25.3 6.2 0.1 DMP019 6909 1.9 14.2 0.6 26.2 6.4 0.1

In the past if I wanted to reset the elapsed values I had to disconnect from the partition and then connect again.

Now I can use the SYSDISKSTAT Table Function to reset the elapsed values.

The Table Function has one parameter:

SELECT * FROM TABLE(QSYS2.SYSDISKSTAT( RESET_STATISTICS => 'YES' )) ;

As the name suggests the RESET_STATISTICS when "YES" will reset the statistics. When it is omitted or "NO" it will not.

The columns returned from the Table Function are identical to those returned by the View.

If I wanted to do I could use the following statement to reset the elapsed time:

01 SELECT RESOURCE_NAME, 02 ELAPSED_TIME,ELAPSED_IO_REQUESTS,ELAPSED_REQUEST_SIZE, 03 ELAPSED_READ_REQUESTS,ELAPSED_DATA_READ, 04 ELAPSED_DATA_WRITTEN,ELAPSED_PERCENT_BUSY 05 FROM TABLE(QSYS2.SYSDISKSTAT( 06 RESET_STATISTICS => 'YES' 07 )) 08 WHERE RESOURCE_NAME LIKE 'DMP01%' 09 ORDER BY 3 ;

Lines 1 – 4: These are the same columns I selected when I used the View.

Lines 5 – 7: This the Table Function definition, and I want to reset the statistics.

Lines 8 and 9: Same as the View statement.

ELAPSED_ ELAPSED_ ELAPSED ELAPSED ELAPSED ELAPSED_ RESOURCE ELAPSED IO_ REQUEST _READ_ _DATA_ _DATA_ PERCENT_ _NAME _TIME REQUESTS _SIZE REQUESTS READ WRITTEN BUSY -------- ------- -------- -------- -------- ------- ------- -------- DMP019 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP014 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP013 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP015 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP016 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP017 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> DMP018 0 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL>

Now if I want to see the elapsed statistics I would not run the Table Function again, I would run the View statement I showed before.

Playing with the Table Function I am sure I have found that it was quicker to reset the statistics with the following statement, rather than the one above:

01 SELECT * 02 FROM TABLE(QSYS2.SYSDISKSTAT( 03 RESET_STATISTICS => 'YES' 04 )) ;

After a sip of tea and a cookie I ran the View SQL statement again, and the elapsed statistics are now gathering again:

ELAPSED_ ELAPSED_ ELAPSED ELAPSED ELAPSED ELAPSED_ RESOURCE ELAPSED IO_ REQUEST _READ_ _DATA_ _DATA_ PERCENT_ _NAME _TIME REQUESTS _SIZE REQUESTS READ WRITTEN BUSY -------- ------- -------- -------- -------- ------- ------- -------- DMP014 154 0.0 0.0 0.0 0.0 0.0 0.0 DMP016 154 0.0 0.0 0.0 0.0 0.0 0.0 DMP017 154 0.0 0.0 0.0 0.0 0.0 0.0 DMP013 154 0.1 7.3 0.0 4.5 7.6 0.0 DMP015 154 0.1 6.6 0.0 6.0 6.7 0.0 DMP019 154 0.2 8.2 0.0 15.7 4.5 0.0 DMP018 154 0.2 7.0 0.0 40.5 5.3 0.0

 

You can learn more about this from the IBM website:

• SYSDISKSTAT View
• SYSDISKSTAT Table Function

 

This article was written for IBM i 7.4 TR4 and 7.3 TR10.