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.
Start of changeVARIABLE_LENGTH_SEGMENTSEnd of change Start of changeVLSEGMENTSEnd of change Start of changeBIGINTEnd of change Start of changeThe number of variable-length data space segments in the partition or member.End of change
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 insert operations for the partition or member since the last IPL.
Start of changeBLOCKED_INSERT_OPERATIONSEnd of change Start of changeBLKIOPSEnd of change Start of changeBIGINTEnd of change Start of changeNumber of blocked insert operations for the partition or member since the last IPL.End of change
Start of changeBLOCKED_INSERT_ROWSEnd of change Start of changeBLKIROWEnd of change Start of changeBIGINTEnd of change Start of changeNumber of rows inserted with blocked insert operations for the partition or member since the last IPL.End of change
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. This does not include maintained temporary indexes.
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.
SEQUENTIAL_READS SEQREADS BIGINT Number of sequential read operations for the partition or member since the last IPL.
RANDOM_READS RANREADS BIGINT Number of random read operations for the partition or member since the last IPL.
CREATE_TIMESTAMP CREATED TIMESTAMP Create timestamp of the partition or member.
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.
LOWINCLUSIVE LOWINCL CHAR(1)
Nullable
Indicates 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.
LOWVALUE LOWVALUE VARGRAPHIC(1024) CCSID 1200
Nullable
A string representation of the low key value for a range partition. If the table is not partitioned by range, contains null.
HIGHINCLUSIVE HIGHINCL CHAR(1)
Nullable
Indicates 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.
HIGHVALUE HIGHVALUE VARGRAPHIC(1024) CCSID 1200
Nullable
A string representation of the high key value for a range partition. If the table is not partitioned by range, contains null.
NUMBER_PARTITIONING_KEYS NBRPKEYS INTEGER
Nullable
The number of partitioning keys. If the table is not partitioned, contains null.
PARTITIONING_KEYS PARTKEYS VARCHAR(2880)
Nullable
The list of partitioning keys. If the table is not partitioned, contains null.
KEEP_IN_MEMORY KEEPINMEM CHAR(1) Indicates whether the partition should be kept in memory:
0
No memory preference.
1
The partition should be kept in memory, if possible.
MEDIA_PREFERENCE MEDIAPREF SMALLINT Indicates the media preference of the partition:
0
No media preference.
255
The partition should be allocated on Solid State Disk (SSD), if possible.
LAST_SOURCE_UPDATE_TIMESTAMP LASRSRCUPD TIMESTAMP
Nullable
Last source change timestamp to a source member. If the table is not a source file, contains null.
SOURCE_TYPE SRCTYPE VARCHAR(10)
Nullable
Source type of a source member. If the table is not a source file, contains null.
VOLATILE VOLATILE CHAR(1) Indicates whether the table is volatile.
0
Table is not volatile.
1
Table is volatile.
Start of changePARTITION_TEXTEnd of change Start of changeLABELEnd of change Start of changeVARGRAPHIC(50) CCSID 1200
Nullable
End of change
Start of changeText of the partition. Contains null if text does not exist for the partition.End of change
Start of changePARTIAL_TRANSACTIONEnd of change Start of changePARTIALTXEnd of change Start of changeCHAR(1)End of change Start of changeIndicates whether the partition contains a partial transaction:
N
The partition does not contain a partial transaction.
Y
The partition was saved while active with a partial transaction.
A subsequent restore of the partition contains the partial transaction. The user should apply changes from the journal to complete the transaction.
R
A rollback abnormally ended prior to completion.
This left the partition with a partial set of rolled back rows.
End of change
Start of change
APPLY_STARTING_RECEIVER
_LIBRARY
End of change
Start of changeAPYRCVLIBEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeThe library containing the starting journal receiver.

Contains null if APPLY_STARTING_RECEIVER is null.

End of change
Start of changeAPPLY_STARTING_RECEIVEREnd of change Start of changeAPYRCVNAMEEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeIndicates that the partition was saved and subsequently restored. If the table was journaled when the partition was saved, the starting journal receiver name will indicate the journal receiver to start with if APYJRNCHG is then used.

Contains null if PARTIAL_TRANSACTION has a value of R. Once an APYJRNCHG is performed, the apply information is cleared.

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.