SYSDISKSTAT view
The SYSDISKSTAT view contains information about spinning disk and solid-state drives (SSD).
The information returned is similar to the detail seen from the Work with Disk Status (WRKDSKSTS) command and from the Open List of ASPs (QYASPOL) API.
The view contains one or more rows for every disk unit on the system, including non-configured (unallocated) disk units. For non-configured units, the UNIT_NUMBER is 0. For a disk which has multiple paths to the disk unit, there will be a row for each unique path to the disk unit. For such disks, the MULTIPLE_PATH_UNIT column will be YES and each RESOURCE_NAME column will identify a different path to the disk unit.
Authorization: None required.
The following table describes the columns in the view. The system name is SYSDISKS. The schema is QSYS2.
Column Name | System Column Name | Data Type | Description |
---|---|---|---|
ASP_NUMBER | ASP_NUMBER | SMALLINT | Specifies the independent auxiliary storage pool (IASP) number. |
DISK_TYPE | DISK_TYPE | VARCHAR(4) | Disk type number of the disk. |
DISK_MODEL | DISK_MODEL | VARCHAR(4) | Model number of the disk. |
UNIT_NUMBER | UNITNBR | SMALLINT | Unit number of the disk. |
SERIAL_NUMBER | SERIALNBR | VARCHAR(15) | The serial number of the disk unit. |
RESOURCE_NAME | RESOURCE | VARCHAR(10) | The unique system-assigned name of the disk unit. |
RESOURCE_STATUS | PATHSTATUS | VARCHAR(7) Nullable
|
The status of the resource.
Contains the null value if the path status is not known. |
MULTIPLE_PATH_UNIT | MULTI_PATH | VARCHAR(3) | A disk unit may have multiple resource names. Each resource name
represents a unique connection to the disk unit. All active connections are used to communicate with
the disk unit. This attribute indicates whether the disk unit has more than one connection.
|
UNIT_TYPE | UNIT_TYPE | SMALLINT | Indicates the type of disk unit:
|
UNIT_STORAGE_CAPACITY | UNITSCAP | BIGINT | Unit storage capacity has the same value as the unit media capacity for configured disk units. This value is 0 for non-configured units. |
UNIT_SPACE_AVAILABLE | UNITSPACE | BIGINT | Space (in bytes) available on the unit for use. |
UNIT_SPACE_AVAILABLE_GB | UNITSPCGB | BIGINT | Space, in billions of bytes, available on the unit for use. |
PERCENT_USED | PERCENTUSE | DECIMAL(7,3) Nullable
|
The percentage that the disk unit has been consumed. |
UNIT_MEDIA_CAPACITY | UNITMCAP | BIGINT | Storage capacity (in bytes) of the unit. |
UNIT_MEDIA_CAPACITY_GB | UNITMCAPGB | BIGINT | Storage capacity, in billions of bytes, of the unit. |
STORAGE_FOR_SYSTEM | STORAGESYS | BIGINT | The amount of auxiliary storage on the disk unit, in millions of bytes, reserved for use by the system. |
STORAGE_ALLOCATION_ALLOWED | NEW_ALLOC | VARCHAR(3) | An indicator of whether new storage allocations are allowed on the
disk unit.
|
PROTECTION_TYPE | PROTECTION | VARCHAR(8) Nullable
|
The type of protection that has been assigned to this disk unit.
Contains the null value if no storage protection has been set up for this disk unit. |
PROTECTION_STATUS | STATUS | VARCHAR(21) Nullable
|
The disk protection status for the disk unit, when the unit is
under device parity protection or the ASP is under mirrored protection.
Contains the null value if PROTECTION_TYPE is null. |
RAID_TYPE | RAID_TYPE | VARCHAR(6) Nullable
|
The type of RAID protection that has been assigned to this disk unit.
Contains the null value if PROTECTION_TYPE is not PARITY or no storage protection has been set up for this disk unit. |
MIRRORED_SUBUNIT | SUBUNIT | CHAR(1) Nullable
|
Whether the disk unit is for subunit A or B of a mirrored pair.
Contains the null value if the unit is not a mirrored pair or if the information is not available. |
LOGICAL_MIRRORED_PAIR_
STATUS |
MIRRORPS |
CHAR(1)
Nullable |
Indicates the status of a mirrored pair of disks:
Contains the null value if PROTECTION_TYPE is not MIRRORED or no storage protection has been set up for this disk unit. |
MIRRORED_UNIT_STATUS | MIRRORUS |
CHAR(1)
Nullable |
Indicates the status of a mirrored unit:
Contains the null value if PROTECTION_TYPE is not MIRRORED. |
AVAILABILITY_PARITY_SET_UNIT | PARITY | VARCHAR(3) | Whether the disk unit is in an availability parity set.
|
HYPERSWAP | HYPERSWAP | VARCHAR(3) | Whether unit is using HyperSwap.
|
FIRMWARE_LEVEL | FIRMWARE | VARCHAR(8) Nullable
|
The level of code running in the SSD device. Contains the null value if this disk is not SSD or if the information is not available. |
SSD_PART_NUMBER | SSD_PART | VARCHAR(12) Nullable
|
The part number as reported by the SSD device. Contains the null value if this disk is not SSD or if the information is not available. |
SSD_POWER_ON_DAYS | SSD_DAYS | BIGINT Nullable
|
The number of days that the SSD device has been active in a system.
Contains the null value if this disk is not SSD or if the information is not available. |
SSD_LIFE_REMAINING | SSD_LIFE | INTEGER Nullable
|
The percentage of the lifetime remaining for the SSD device. This
estimates the percentage of usable function remaining for the drive before it should be replaced.
Calculations for this percentage include more than just the number of bytes written and
supported. Contains the null value if this disk is not SSD or if the information is not available. |
SSD_READ_WRITE_PROTECTED | SSD_PROT | VARCHAR(3) Nullable
|
Whether the device is read/write protected.
Contains the null value if this disk is not SSD or if the information is not available. |
SSD_BYTES_WRITTEN | SSD_WRITE | DECIMAL(20,0) Nullable
|
The lifetime number of bytes, in gigabytes, that have been
physically written to the NAND memory in this particular SSD disk unit. This is strongly related to
bytes written by the applications using the drive, but will not match. Contains the null value if this disk is not SSD or if the information is not available. |
SSD_SUPPORTED_BYTES_WRITTEN | SSD_MAX_W | DECIMAL(20,0) Nullable
|
The lifetime number of bytes, in gigabytes, that the SSD is
expected to be able to physically write at a minimum. Additional writes beyond this number may start
to fail due to the limited write endurance of a Read Intensive drive. Contains the null value if this disk is not SSD or if the information is not available. |
SSD_PFA_WARNING | SSD_PFA | VARCHAR(3) Nullable
|
Whether the Predictive Failure Analysis warning has been logged.
Contains the null value if this disk is not SSD or if the information is not available. |
TOTAL_SAMPLE_COUNT | SAMPLED | BIGINT | The number of times the disk queue was checked to determine whether or not the queue is empty. |
TOTAL_NOT_BUSY_COUNT | NOT_BUSY | BIGINT | The number of times the disk queue was empty during the same time period that the sample count was taken. The busy count can be calculated as TOTAL_SAMPLE_COUNT - TOTAL_NOT_BUSY_COUNT. |
TOTAL_READ_REQUESTS
|
REQ_IN | BIGINT | The number of input data transfer requests processed for the disk unit since the last IPL. This value is not directly related to the number of blocks transferred for the disk unit because the number of blocks to be transferred for a given transfer request can vary greatly. This value will wrap back to 1 when 2,147,483,647 is reached. |
TOTAL_WRITE_REQUESTS
|
REQ_OUT | BIGINT | The number of output data transfer requests processed for the disk unit since the last IPL. This value is not directly related to the number of blocks transferred for the disk unit because the number of blocks to be transferred for a given transfer request can vary greatly. This value will wrap back to 1 when 2,147,483,647 is reached. |
TOTAL_BLOCKS_READ
|
BLOCK_IN | BIGINT | The number of 512-byte blocks transferred from the disk unit since the last IPL. This value will wrap back to 1 when 2,147,483,647 is reached. |
TOTAL_BLOCKS_WRITTEN
|
BLOCK_OUT | BIGINT | The number of 512-byte blocks transferred to the disk unit since the last IPL. This value will wrap back to 1 when 2,147,483,647 is reached. |
TOTAL_PERMANENT_BLOCKS_WRITTEN
|
BLOCK_PERM | BIGINT | The number of 512-byte blocks of permanent storage transferred to the disk unit since the last IPL. This value will wrap back to 1 when 2,147,483,647 is reached. |
TOTAL_PERMANENT_WRITE_REQUESTS | REQ_PERM | BIGINT | The number of output permanent data transfer requests processed for the disk unit since the last IPL. This value is not directly related to the permanent blocks transferred from main storage for the disk unit because the number of blocks transferred for a given transfer request can vary greatly. This value will wrap back to 1 when 2,147,483,647 is reached. |
ELAPSED_TIME | ELAP_TIME | INTEGER | The time that has elapsed, in seconds, between the measurement start time and the current system time. |
ELAPSED_IO_REQUESTS | ELAP_IO | DECIMAL(6,1) Nullable
|
The average number of I/O requests for read and write operations that occurred per second during the elapsed time. |
ELAPSED_REQUEST_SIZE | ELAP_SIZE | DECIMAL(6,1) Nullable
|
The average size of an I/O request in KB during the elapsed time. |
ELAPSED_READ_REQUESTS | ELAP_REQ_R | DECIMAL(6,1) Nullable
|
The average number of requests per second to transfer data from the disk unit during the elapsed time. |
ELAPSED_WRITE_REQUESTS | ELAP_REQ_W | DECIMAL(6,1) Nullable
|
The average number of requests per second to transfer data to the disk unit during the elapsed time. |
ELAPSED_DATA_READ | ELAP_DTA_R | DECIMAL(6,1) Nullable
|
The average amount of data, in KB, transferred from the disk unit, per request, during the elapsed time. |
ELAPSED_DATA_WRITTEN | ELAP_DTA_W | DECIMAL(6,1) Nullable
|
The average amount of data, in KB, transferred to the disk unit, per request, during the elapsed time. |
ELAPSED_PERCENT_BUSY | ELAP_BUSY | DECIMAL(4,1) Nullable
|
The estimated percentage of time the disk unit is being used during the elapsed time. |
Notes
The values in the ELAPSED_ columns are based on the TOTAL_ columns. When an ELAPSED calculation notices that the ending value is less than the value at the start of the time interval, it adds 2,147,483,647 to the ending value for an accurate result. When this happens, a warning SQLSTATE '01687' is issued. It is recommended that the statistics get reset using the QSYS2.SYSDISKSTAT table function before the counters can wrap more than once. The frequency needed for this action depends on the size and activity of the disk units.
The ELAPSED_ column information is derived from the values reported in the TOTAL_ columns as shown in the following table. These formulas can be used to calculate identical statistics if you want to save historical disk statistics in a permanent table.
Elapsed column name | TOTAL_ columns used to calculate the elapsed value | Notes |
---|---|---|
ELAPSED_IO_REQUESTS | (delta_READ_REQUESTS + delta_WRITE_REQUESTS) / delta_time | |
ELAPSED_REQUEST_SIZE | ((delta_BLOCKS_READ + delta_BLOCKS_WRITTEN) / 2) (delta_READ_REQUESTS + delta_WRITE_REQUESTS) | Divide by 2 to convert value from 512 byte blocks to KB. |
ELAPSED_READ_REQUESTS | delta_READ_REQUESTS / delta_time | |
ELAPSED_WRITE_REQUESTS | delta_WRITE_REQUESTS / delta_time | |
ELAPSED_DATA_READ | (delta_BLOCKS_READ / 2) / delta_READ_REQUESTS | Divide by 2 to convert value from 512 byte blocks to KB. |
ELAPSED_DATA_WRITTEN | (delta_BLOCKS_WRITTEN / 2) / delta_WRITE_REQUESTS | Divide by 2 to convert value from 512 byte blocks to KB. |
ELAPSED_PERCENT_BUSY | ((delta_SAMPLE_COUNT - delta_NOT_BUSY_COUNT) / delta_SAMPLE_COUNT) * 100 |
Examples
Return information about all disks.
SELECT * FROM QSYS2.SYSDISKSTAT
Return information for all SSD units.
SELECT * FROM QSYS2.SYSDISKSTAT WHERE UNIT_TYPE = 1