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.
Start of changeSERIAL_NUMBEREnd of change Start of changeSERIALNBREnd 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 changeRESOURCEEnd 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 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.

Start of changeIOP_NAMEEnd 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 changeMULTIPLE_PATH_UNITEnd of change Start of changeMULTI_PATHEnd 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 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 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 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.
Start of changeUNIT_SPACE_AVAILABLE_GBEnd of change Start of changeUNITSPCGBEnd 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 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.
Start of changeUNIT_MEDIA_CAPACITY_GBEnd of change Start of changeUNITMCAPGBEnd 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 changeSTORAGESYSEnd 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 changeNEW_ALLOCEnd 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 changePROTECTIONEnd of change Start of changeVARCHAR(8)
Nullable
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 changeSTATUSEnd of change Start of changeVARCHAR(21)
Nullable
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 changeRAID_TYPEEnd of change Start of changeVARCHAR(6)
Nullable
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 changeSUBUNITEnd 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
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.

Start of changeAVAILABILITY_PARITY_SET_UNITEnd of change Start of changePARITYEnd of change Start of changeVARCHAR(3)End of change Start of change 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.
End of change
HYPERSWAP 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 changeFIRMWAREEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_PART_NUMBEREnd of change Start of changeSSD_PARTEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_POWER_ON_DAYSEnd of change Start of changeSSD_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 SSD or if the information is not available.

End of change
Start of changeSSD_LIFE_REMAININGEnd of change Start of changeSSD_LIFEEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_READ_WRITE_PROTECTEDEnd of change Start of changeSSD_PROTEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_BYTES_WRITTENEnd of change Start of changeSSD_WRITEEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_SUPPORTED_BYTES_WRITTENEnd of change Start of changeSSD_MAX_WEnd 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 SSD or if the information is not available.

End of change
Start of changeSSD_PFA_WARNINGEnd of change Start of changeSSD_PFAEnd 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 SSD or if the information is not available.

End of change
Start of changeTOTAL_SAMPLE_COUNTEnd of change Start of changeSAMPLEDEnd 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 changeNOT_BUSYEnd 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 changeREQ_INEnd 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 changeREQ_OUTEnd 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 changeBLOCK_INEnd 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 changeBLOCK_OUTEnd 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 changeBLOCK_PERMEnd 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 changeREQ_PERMEnd 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 changeELAP_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 changeELAP_IOEnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_SIZEEnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_REQ_REnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_REQ_WEnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_DTA_REnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_DTA_WEnd of change Start of changeDECIMAL(6,1)
Nullable
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 changeELAP_BUSYEnd of change Start of changeDECIMAL(4,1)
Nullable
End of change
Start of changeThe estimated percentage of time the disk unit is being used during the elapsed time. End of change
Start of change

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

Examples

  • Return information about all disks.

    SELECT * FROM QSYS2.SYSDISKSTAT
  • Return information for all SSD units.

    SELECT * FROM QSYS2.SYSDISKSTAT WHERE UNIT_TYPE = 1