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.

Table 1. SYSDISKSTAT view
Column Name	System Column Name	Data Type	Description
ASP_NUMBER	ASP_NUMBER	SMALLINT	Specifies the storage pool (ASP) 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. ACTIVE RESOURCE_NAME is active. PASSIVE RESOURCE_NAME is not active. Contains the null value if the path status is not known.
HARDWARE_STATUS	HDW_STATUS	VARCHAR(21)	The hardware status for the disk unit. ACTIVE The disk unit is operational and ready to accept input or output operations. BUSY The disk unit is being overwhelmed by a large number of read and write operations and the disk unit is busy. DEGRADED The disk unit is part of a disk unit subsystem that has RAID-5 device parity protection. A decrease in performance has occurred because a component that is not critical has failed. FAILED The disk unit is part of a disk unit subsystem that has device parity protection. This unit has failed. If another unit in the disk unit subsystem fails, data could be lost. HARDWARE FAILURE A hardware-related failure has occurred in a redundant component. The failure does not affect data or performance. However, the system may become unusable if another failure of a redundant component occurs. NOT READY The disk unit is not ready to perform read or write operations. PARITY REBUILD The disk unit is part of a disk unit subsystem that has device parity protection. The data on this unit is being rebuilt from other units in the disk unit subsystem. If two more disk units in the disk unit subsystem fail, data could be lost. POWER LOSS The disk unit cannot communicate with its I/O adapter. READ WRITE PROTECTED The disk unit is unable to perform read or write operations. UNKNOWN The hardware status is not known. UNPROTECTED The disk unit is part of a disk unit subsystem that has device parity protection. This unit is operational. However, another unit in the disk unit subsystem has failed. If another unit in the disk unit subsystem fails, data could be lost. WRITE PROTECTED The disk unit is unable to perform write operations.
IS_ZERO	IS_ZERO	VARCHAR(3)	Indicates whether all the pages on the disk unit are zero. NO All the pages on the disk unit are not zero. YES All the pages on the disk unit are zero.
IOP_NAME	IOP_NAME	VARCHAR(10)	The name of the I/O processor.
HOST_WWPN	HOST_WWPN	CHAR(16)	A hexadecimal string representing the resource’s host world wide port name. Contains the null value if no host world wide port name is available for this device.
REMOTE_WWPN	RMT_WWPN	CHAR(16)	A hexadecimal string representing the resource’s remote world wide port name. Contains the null value if no remote world wide port name is available for this device.
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. NO The disk unit has only one connection. YES The disk unit has more than one connection.
UNIT_TYPE	UNIT_TYPE	SMALLINT	Indicates the type of disk unit: 0 Not solid state disk 1 Solid state disk (SSD)
UNIT_NVME	UNIT_NVME	SMALLINT	Whether this is a Non-Volatile Memory Express (NVMe) unit. 0 Not NVMe 1 NVMe
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. NO The disk unit does not allow new storage allocations. YES The disk unit allows new storage allocations.
PROTECTION_TYPE	PROTECTION	VARCHAR(8) Nullable	The type of protection that has been assigned to this disk unit. MIRRORED The ASP is under system mirrored protection provided by the system software. PARITY This disk unit is part of a parity protection array. 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. ACTIVE The disk unit is active. BUSY The disk unit is busy. DEGRADED There is a hardware failure within the disk subsystem that affects performance, but does not affect the function of the disk unit. FAILED The disk unit has failed. HARDWARE FAILURE There is a hardware failure within the disk subsystem that does not affect the function or performance of the disk unit. NOT READY The disk unit is not ready. PARITY REBUILD The disk unit's parity protection is being rebuilt. POWER LOSS The disk unit is not operational. READ WRITE PROTECTED The disk unit is read/write protected. RESUME The unit is part of a mirrored ASP and mirroring is in the process of being resumed on this unit. RESUME PENDING The unit is part of a mirrored independent ASP which is varied off. Mirror synchronization will resume when the independent ASP is varied on. SUSPEND The unit is part of a mirrored ASP and mirroring is suspended on this unit. UNKNOWN The disk unit has returned a status that is not recognizable by the system. UNPROTECTED Some other disk unit in the disk subsystem has failed. WRITE PROTECTED The disk unit is write protected. 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. RAID5 This disk unit has been set up with RAID 5 protection. RAID6 This disk unit has been set up with RAID 6 protection. RAID10 This disk unit has been set up with RAID 10 protection. 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. A This entry is for subunit A. B This entry is for subunit B. 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: 0 Indicates that one mirrored unit of a mirrored pair is not active. 1 Indicates that both mirrored units of a mirrored pair are active. 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: 1 Indicates that this mirrored unit of a mirrored pair is active (online with current data). 2 Indicates that this mirrored unit is being synchronized. 3 Indicates that this mirrored unit is suspended. Contains the null value if PROTECTION_TYPE is not MIRRORED.
AVAILABILITY_PARITY_SET_UNIT	PARITY	VARCHAR(3)	Whether the disk unit is in a parity set which is optimized for availability. NO The disk unit is not in an availability parity set. YES The disk unit is in an availability parity set.
HYPERSWAP	HYPERSWAP	VARCHAR(3)	Whether unit is using HyperSwap®. NO Unit is not using HyperSwap. YES 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 a mainstream SSD or if the information is not available. See Mainstream solid-state drives for a description of mainstream solid-state drives.
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 a mainstream 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 a mainstream 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 a mainstream SSD or if the information is not available.
SSD_READ_WRITE_PROTECTED	SSD_PROT	VARCHAR(3) Nullable	Whether the device is read/write protected. NO The SSD device is not read/write protected YES The SSD device is read/write protected Contains the null value if this disk is not a mainstream 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 a mainstream 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 a mainstream 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. NO The Predictive Failure Analysis warning has not been logged. YES The Predictive Failure Analysis warning has been logged. Contains the null value if this disk is not a mainstream 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.

For clarity, the values prefixed by delta_ indicate the difference between two rows in the corresponding TOTAL_ columns. For example, delta_READ_REQUESTS means TOTAL_READ_REQUESTS(time2) - TOTAL_READ_REQUESTS(time1). The delta_time value means the time in seconds between time1 and time2.

Table 2. Calculating elapsed data
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