Wednesday, September 1, 2021

Getting information about the partition's disk drives using SQL

new columns added to sysdiskstat view and table function

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:

 

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

3 comments:

  1. Simon, thanks for sharing . Great information about getting disk information.. Again, thanks for sharing..

    ReplyDelete
  2. Harold Adolfo Mendoza AvendañoSeptember 1, 2021 at 10:18 AM

    Gracias simon

    ReplyDelete
  3. Very useful, nice one! Thank you!

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.