SYSDISKSTAT table function

The SYSDISKSTAT table function contains information about disks. It provides an option to reset the baseline for collecting statistical information.

Authorization: None required.

The schema is QSYS2.

reset-statistics: A character or graphic string expression that contains a value of YES or NO.
If this parameter has a value of YES, statistics are reset such that the time of this query execution is used as the new baseline. The columns that contain this statistical data have names that are prefixed with ELAPSED_. Future invocations of SYSDISKSTAT within this connection will return statistical detail relative to the new baseline. If this parameter has a value of NO, statistics are not reset for the invocation. If this parameter is not specified, the default is NO.

The result of the function is a table containing multiple rows with the format shown in the following table. All the columns are nullable.

Table 1. SYSDISKSTAT table function
Column Name	Data Type	Description
ASP_NUMBER	SMALLINT	Specifies the independent auxiliary storage pool (IASP) number. 1 indicates the system ASP.
DISK_TYPE	VARCHAR(4)	Disk type number of the disk.
DISK_MODEL	VARCHAR(4)	Model number of the disk.
UNIT_NUMBER	SMALLINT	Unit number of the disk.
SERIAL_NUMBER	VARCHAR(15)	The serial number of the disk unit.
RESOURCE_NAME	VARCHAR(10)	The unique system-assigned name of the disk unit.
RESOURCE_STATUS	VARCHAR(7)	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	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	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.
BLOCK_SIZE	INTEGER	The size (in bytes) of each page on the disk unit.
UNMAP_CAPABLE	VARCHAR(3)	Indicates whether the disk unit can perform the unmap function to return unused pages to the unallocated area. NO The disk unit cannot perform the unmap function. YES The disk unit can perform the unmap function.
IOP_NAME	VARCHAR(10)	The name of the I/O processor.
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	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	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	SMALLINT	Indicates the type of disk unit: 0 Not solid state drive 1 Solid state drive (SSD)
UNIT_NVME	SMALLINT	Whether this is a Non-Volatile Memory Express (NVMe) unit. 0 Not NVMe 1 NVMe
UNIT_STORAGE_CAPACITY	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	BIGINT	Space (in bytes) available on the unit for use.
UNIT_SPACE_AVAILABLE_GB	BIGINT	Space, in billions of bytes, available on the unit for use.
PERCENT_USED	DECIMAL(7,3)	The percentage that the disk unit has been consumed.
UNIT_MEDIA_CAPACITY	BIGINT	Storage capacity (in bytes) of the unit.
UNIT_MEDIA_CAPACITY_GB	BIGINT	Storage capacity, in billions of bytes, of the unit.
STORAGE_FOR_SYSTEM	BIGINT	The amount of auxiliary storage on the disk unit, in millions of bytes, reserved for use by the system.
STORAGE_ALLOCATION_ALLOWED	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	VARCHAR(8)	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	VARCHAR(21)	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	VARCHAR(6)	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	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	CHAR(1)	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	CHAR(1)	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	VARCHAR(3)	Whether the disk unit is in an availability parity set. NO The disk unit is not in an availability parity set. YES The disk unit is in an availability parity set.
HYPERSWAP	VARCHAR(3)	Whether unit is using HyperSwap®. NO Unit is not using HyperSwap. YES Unit is using HyperSwap.
FIRMWARE_LEVEL	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	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	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	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	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	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	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	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	BIGINT	The number of times the disk queue was checked to determine whether or not the queue is empty.
TOTAL_NOT_BUSY_COUNT	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	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	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	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	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	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	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	INTEGER	The time that has elapsed, in seconds, between the measurement start time and the current system time.
ELAPSED_IO_REQUESTS	DECIMAL(6,1)	The average number of I/O requests for read and write operations that occurred per second during the elapsed time.
ELAPSED_REQUEST_SIZE	DECIMAL(6,1)	The average size of an I/O request in KB during the elapsed time.
ELAPSED_READ_REQUESTS	DECIMAL(6,1)	The average number of requests per second to transfer data from the disk unit during the elapsed time.
ELAPSED_WRITE_REQUESTS	DECIMAL(6,1)	The average number of requests per second to transfer data to the disk unit during the elapsed time.
ELAPSED_DATA_READ	DECIMAL(6,1)	The average amount of data, in KB, transferred from the disk unit, per request, during the elapsed time.
ELAPSED_DATA_WRITTEN	DECIMAL(6,1)	The average amount of data, in KB, transferred to the disk unit, per request, during the elapsed time.
ELAPSED_PERCENT_BUSY	DECIMAL(4,1)	The estimated percentage of time the disk unit is being used during the elapsed time.

Example

Return information about all disks, resetting the statistical information shown for the current job.
```
SELECT * FROM TABLE(QSYS2.SYSDISKSTAT(RESET_STATISTICS=>'YES'));
```