Start of change

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.

Read syntax diagramSkip visual syntax diagramSYSDISKSTAT(RESET_STATISTICS => reset-statistics)
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.
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.
Start of changeSERIAL_NUMBEREnd of change Start of changeVARCHAR(15)End of change Start of changeThe serial number of the disk unit.End of change
Start of changeRESOURCE_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe unique system-assigned name of the disk unit.End of change
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.

Start of changeHARDWARE_STATUSEnd of change Start of changeVARCHAR(21)End of change Start of changeThe 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.
End of change
Start of changeIS_ZEROEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeIOP_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the I/O processor.End of change
Start of changeHOST_WWPNEnd of change Start of changeCHAR(16)End of change Start of changeA 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.

End of change
Start of changeREMOTE_WWPNEnd of change Start of changeCHAR(16)End of change Start of changeA 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.

End of change
Start of changeMULTIPLE_PATH_UNITEnd of change Start of changeVARCHAR(3)End of change Start of changeA 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.
End of change
UNIT_TYPE SMALLINT Indicates the type of disk unit:
0
Not solid state disk
1
Solid state disk (SSD)
Start of changeUNIT_NVMEEnd of change Start of changeSMALLINTEnd of change Start of changeWhether this is a Non-Volatile Memory Express (NVMe) unit.
0
Not NVMe
1
NVMe
End of change
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.
Start of changeUNIT_SPACE_AVAILABLE_GBEnd of change Start of changeBIGINTEnd of change Start of changeSpace, in billions of bytes, available on the unit for use.End of change
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.
Start of changeUNIT_MEDIA_CAPACITY_GBEnd of change Start of changeBIGINTEnd of change Start of changeStorage capacity, in billions of bytes, of the unit.End of change
Start of changeSTORAGE_FOR_SYSTEMEnd of change Start of changeBIGINTEnd of change Start of changeThe amount of auxiliary storage on the disk unit, in millions of bytes, reserved for use by the system. End of change
Start of changeSTORAGE_ALLOCATION_ALLOWEDEnd of change Start of changeVARCHAR(3)End of change Start of changeAn 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.
End of change
Start of changePROTECTION_TYPEEnd of change Start of changeVARCHAR(8) End of change Start of changeThe 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.

End of change
Start of changePROTECTION_STATUSEnd of change Start of changeVARCHAR(21)End of change Start of changeThe 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.

End of change
Start of changeRAID_TYPEEnd of change Start of changeVARCHAR(6)End of change Start of changeThe 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.

End of change
Start of changeMIRRORED_SUBUNITEnd of change Start of changeCHAR(1)
Nullable
End of change
Start of changeWhether 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.

End of change
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.

Start of changeAVAILABILITY_PARITY_SET_UNITEnd of change Start of changeVARCHAR(3)End of change Start of changeWhether 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.
End of change
HYPERSWAP VARCHAR(3) Whether unit is using HyperSwap®.
NO
Unit is not using HyperSwap.
YES
Unit is using HyperSwap.
Start of changeFIRMWARE_LEVELEnd of change Start of changeVARCHAR(8)
Nullable
End of change
Start of changeThe 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.

End of change
Start of changeSSD_PART_NUMBEREnd of change Start of changeVARCHAR(12)
Nullable
End of change
Start of changeThe 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.

End of change
Start of changeSSD_POWER_ON_DAYSEnd of change Start of changeBIGINT
Nullable
End of change
Start of changeThe 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.

End of change
Start of changeSSD_LIFE_REMAININGEnd of change Start of changeINTEGER
Nullable
End of change
Start of change 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.

End of change
Start of changeSSD_READ_WRITE_PROTECTEDEnd of change Start of changeVARCHAR(3)
Nullable
End of change
Start of changeWhether 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.

End of change
Start of changeSSD_BYTES_WRITTENEnd of change Start of changeDECIMAL(20,0)
Nullable
End of change
Start of changeThe 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.

End of change
Start of changeSSD_SUPPORTED_BYTES_WRITTENEnd of change Start of changeDECIMAL(20,0)
Nullable
End of change
Start of changeThe 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.

End of change
Start of changeSSD_PFA_WARNINGEnd of change Start of changeVARCHAR(3)
Nullable
End of change
Start of changeWhether 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.

End of change
Start of changeTOTAL_SAMPLE_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe number of times the disk queue was checked to determine whether or not the queue is empty.End of change
Start of changeTOTAL_NOT_BUSY_COUNTEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
TOTAL_READ_REQUESTS
End of change
Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
TOTAL_WRITE_REQUESTS
End of change
Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
TOTAL_BLOCKS_READ
End of change
Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
TOTAL_BLOCKS_WRITTEN
End of change
Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of change
TOTAL_PERMANENT_BLOCKS_WRITTEN
End of change
Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of changeTOTAL_PERMANENT_WRITE_REQUESTSEnd of change Start of changeBIGINTEnd of change Start of changeThe 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.End of change
Start of changeELAPSED_TIMEEnd of change Start of changeINTEGEREnd of change Start of changeThe time that has elapsed, in seconds, between the measurement start time and the current system time. End of change
Start of changeELAPSED_IO_REQUESTSEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average number of I/O requests for read and write operations that occurred per second during the elapsed time. End of change
Start of changeELAPSED_REQUEST_SIZEEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average size of an I/O request in KB during the elapsed time. End of change
Start of changeELAPSED_READ_REQUESTSEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average number of requests per second to transfer data from the disk unit during the elapsed time. End of change
Start of changeELAPSED_WRITE_REQUESTSEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average number of requests per second to transfer data to the disk unit during the elapsed time. End of change
Start of changeELAPSED_DATA_READEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average amount of data, in KB, transferred from the disk unit, per request, during the elapsed time. End of change
Start of changeELAPSED_DATA_WRITTENEnd of change Start of changeDECIMAL(6,1)End of change Start of changeThe average amount of data, in KB, transferred to the disk unit, per request, during the elapsed time. End of change
Start of changeELAPSED_PERCENT_BUSYEnd of change Start of changeDECIMAL(4,1)End of change Start of changeThe estimated percentage of time the disk unit is being used during the elapsed time. End of change

Example

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