SYSPARTITIONSTAT

The SYSPARTITIONSTAT view contains one row for every table partition or table member. If the table is a distributed table, the partitions that reside on other database nodes are not contained in this catalog view. They are contained in the catalog views of the other database nodes.

The following table describes the columns in the SYSPARTITIONSTAT view:

Table 1. SYSPARTITIONSTAT view
Column name System Column Name Data Type Description
TABLE_SCHEMA TABSCHEMA VARCHAR(128) Name of the SQL schema that contains the table.
TABLE_NAME TABNAME VARCHAR(128) Name of the table.
TABLE_PARTITION TABPART VARCHAR(128) Name of the table partition or member.
PARTITION_TYPE PARTTYPE CHAR(1) The type of the table partitioning:
blank
The table is not partitioned.
H
This is data hash partitioning.
R
This is data range partitioning.
D
This is distributed database hash partitioning.
PARTITION_NUMBER PARTNBR INTEGER

Nullable

 The partition number of this partition. If the table is a distributed table, contains null.
NUMBER_DISTRIBUTED_PARTITIONS DSTPARTS INTEGER

Nullable

 If the table is a distributed table, contains the total number of partitions. If the table is not a distributed table, contains null.
NUMBER_ROWS CARD BIGINT Number of valid rows in the table partition or member.
NUMBER_ROW_PAGES NPAGES BIGINT Number of 64K pages in the partition's data.
NUMBER_PAGES FPAGES BIGINT Same as NUMBER_ROW_PAGES.
OVERFLOW OVERFLOW BIGINT The estimated number of rows that have overflowed to variable length segments. If the table does not contain variable length or LOB columns, contains 0.
CLUSTERED CLUSTERED CHAR(1)

Nullable

 Not applicable for DB2® for i. Will always be null.
ACTIVE_BLOCKS ACTBLOCKS BIGINT Not applicable for DB2 for i. Will always be -1.
AVGCOMPRESSEDROWSIZE ACROWSIZE BIGINT Not applicable for DB2 for i. Will always be -1.
AVGROWCOMPRESSIONRATIO ACROWRATIO REAL Not applicable for DB2 for i. Will always be -1.
AVGROWSIZE AVGROWSIZE BIGINT Average length (in bytes) of a row in this table. If the table has variable length or LOB columns, contains -1.
PCTROWSCOMPRESSED PCTCROWS REAL Not applicable for DB2 for i. Will always be -1.
PCTPAGESSAVED PCTPGSAVED SMALLINT Not applicable for DB2 for i. Will always be -1.
NUMBER_DELETED_ROWS DELETED BIGINT Number of deleted rows in the table partition or member.
DATA_SIZE SIZE BIGINT Total size (in bytes) of the data space in the partition or member.
VARIABLE_LENGTH_SIZE VLSIZE BIGINT Size (in bytes) of the variable-length data space segments in the partition or member.
FIXED_LENGTH_EXTENTS FLEXTENTS BIGINT Start of changeNot applicable for DB2 for i. Will always be -1.End of change
VARIABLE_LENGTH_EXTENTS VLEXTENTS BIGINT Start of changeNot applicable for DB2 for i. Will always be -1.End of change
COLUMN_STATS_SIZE CSTATSSIZE BIGINT Size (in bytes) of the column statistics in the partition or member.
MAINTAINED_TEMPORARY_INDEX_SIZE MTISIZE BIGINT Size (in bytes) of all maintained temporary indexes over the partition or member.
NUMBER_DISTINCT_INDEXES DISTINCTIX INTEGER The number of distinct indexes built over the partition or member. This does not include maintained temporary indexes.
OPEN_OPERATIONS OPENS BIGINT Number of full opens of the partition or member since the last IPL.
CLOSE_OPERATIONS CLOSES BIGINT Number of full closes of the partition or member since the last IPL.
INSERT_OPERATIONS INSERTS BIGINT Number of inserts operations for the partition or member since the last IPL.
UPDATE_OPERATIONS UPDATES BIGINT Number of update operations for the partition or member since the last IPL.
DELETE_OPERATIONS DELETES BIGINT Number of delete operations for the partition or member since the last IPL.
CLEAR_OPERATIONS DSCLEARS BIGINT Number of clear operations (CLRPFM operations) for the partition or member since the last IPL.
COPY_OPERATIONS DSCOPIES BIGINT Number of data space copy operations (certain CPYxxx operations) for the partition or member since the last IPL.
REORGANIZE_OPERATIONS DSREORGS BIGINT Number of data space reorganize operations (non-interruptible RGZPFM operations) for the partition or member since the last IPL.
INDEX_BUILDS DSINXBLDS BIGINT Number of creates or rebuilds of indexes that reference the partition or member since the last IPL. Start of changeThis does not include maintained temporary indexes.End of change
LOGICAL_READS LGLREADS BIGINT Number of logical read operations for the partition or member since the last IPL.
PHYSICAL_READS PHYREADS BIGINT Number of physical read operations for the partition or member since the last IPL.
Start of changeSEQUENTIAL_READSEnd of change Start of changeSEQREADSEnd of change Start of changeBIGINTEnd of change Start of changeNumber of sequential read operations for the partition or member since the last IPL.End of change
Start of changeRANDOM_READSEnd of change Start of changeRANREADSEnd of change Start of changeBIGINTEnd of change Start of changeNumber of random read operations for the partition or member since the last IPL.End of change
Start of changeCREATE_TIMESTAMPEnd of change Start of changeCREATEDEnd of change Start of changeTIMESTAMPEnd of change Start of changeCreate timestamp of the partition or member.End of change
LAST_CHANGE_TIMESTAMP LASTCHG TIMESTAMP Timestamp of the last change that occurred to the partition or member.
LAST_SAVE_TIMESTAMP LASTSAVE TIMESTAMP

Nullable

 Timestamp of the last save of the partition or member. If the partition or member has never been saved, contains null.
LAST_RESTORE_TIMESTAMP LASTRST TIMESTAMP

Nullable

 Timestamp of the last restore of the partition or member. If the partition or member has never been restored, contains null.
LAST_USED_TIMESTAMP LASTUSED TIMESTAMP

Nullable

 Timestamp of the last time the partition or member was used directly by an application for native record I/O or SQL operations. If the partition or member has never been used, contains null.
DAYS_USED_COUNT DAYSUSED INTEGER The number of days the partition or member was used directly by an application for native record I/O or SQL operations since the last time the usage statistics were reset. If the partition or member has never been used since the last time the usage statistics were reset, contains 0.
LAST_RESET_TIMESTAMP LASTRESET TIMESTAMP

Nullable

 The timestamp of the last time the usage statistics were reset for the table. For more information see the Change Object Description (CHGOBJD) command. If the partition or member's last used timestamp has never been reset, contains null.
NEXT_IDENTITY_VALUE NEXTVALUE DECIMAL(31,0)

Nullable

 The next identity value. In some cases, this value may be an estimate. If the table does not have an identity value, contains null.
Start of changeLOWINCLUSIVEEnd of change Start of changeLOWINCLEnd of change Start of changeCHAR(1)

Nullable

End of change		 Start of changeIndicates whether the low key value for the partition is inclusive.
N
The low key value is not inclusive.
Y
The low key value is inclusive.
If the table is not partitioned by range, contains null.End of change
Start of changeLOWVALUEEnd of change Start of changeLOWVALUEEnd of change Start of changeVARGRAPHIC(1024) CCSID 1200

Nullable

End of change		 Start of changeA string representation of the low key value for a range partition. If the table is not partitioned by range, contains null.End of change
Start of changeHIGHINCLUSIVEEnd of change Start of changeHIGHINCLEnd of change Start of changeCHAR(1)

Nullable

End of change		 Start of changeIndicates whether the high key value for the partition is inclusive.
N
The high key value is not inclusive.
Y
The high key value is inclusive.
If the table is not partitioned by range, contains null.End of change
Start of changeHIGHVALUEEnd of change Start of changeHIGHVALUEEnd of change Start of changeVARGRAPHIC(1024) CCSID 1200

Nullable

End of change		 Start of changeA string representation of the high key value for a range partition. If the table is not partitioned by range, contains null.End of change
Start of changeNUMBER_PARTITIONING_KEYSEnd of change Start of changeNBRPKEYSEnd of change Start of changeINTEGER

Nullable

End of change		 Start of changeThe number of partitioning keys. If the table is not partitioned, contains null.End of change
Start of changePARTITIONING_KEYSEnd of change Start of changePARTKEYSEnd of change Start of changeVARCHAR(2880)

Nullable

End of change		 Start of changeThe list of partitioning keys. If the table is not partitioned, contains null.End of change
Start of changeKEEP_IN_MEMORYEnd of change Start of changeKEEPINMEMEnd of change Start of changeCHAR(1)End of change Start of changeIndicates whether the index should be kept in memory:
0
No memory preference.
1
The index should be kept in memory, if possible.
End of change
Start of changeMEDIA_PREFERENCEEnd of change Start of changeMEDIAPREFEnd of change Start of changeSMALLINTEnd of change Start of changeIndicates the media preference of the index:
0
No media preference.
255
The index should be allocated on Solid State Disk (SSD), if possible.
End of change
Start of changeLAST_SOURCE_UPDATE_TIMESTAMPEnd of change Start of changeLASRSRCUPDEnd of change Start of changeTIMESTAMP

Nullable

End of change		 Start of changeLast source change timestamp to a source member. If the table is not a source file, contains null.End of change
Start of changeSOURCE_TYPEEnd of change Start of changeSRCTYPEEnd of change Start of changeVARCHAR(10)

Nullable

End of change		 Start of changeSource type of a source member. If the table is not a source file, contains null.End of change
Start of changeVOLATILEEnd of change Start of changeVOLATILEEnd of change Start of changeStart of changeCHAR(1)End of changeEnd of change Start of changeIndicates whether the table is volatile.End of change
Start of changePARTITION_TEXTEnd of change Start of changeLABELEnd of change Start of changeStart of changeVARGRAPHIC(50) CCSID 1200

Nullable

End of changeEnd of change		 Start of changeText of the partition. Contains null if text does not exist for the partition.End of change
SYSTEM_TABLE_SCHEMA SYS_DNAME CHAR(10) System schema name.
SYSTEM_TABLE_NAME SYS_TNAME CHAR(10) System table name.
SYSTEM_TABLE_MEMBER SYS_MNAME CHAR(10) System member name.
Parent topic: IBM i catalog tables and views