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.

Start of change Similar information that includes one row for every file member, including program described files, is available with the SYSMEMBERSTAT view: QSYS2.SYSMEMBERSTAT End of change

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.
VARIABLE_LENGTH_SEGMENTS	VLSEGMENTS	BIGINT	The number of variable-length data space segments in the partition or member.
FIXED_LENGTH_EXTENTS	FLEXTENTS	BIGINT	Not applicable for Db2 for i. Will always be -1.
VARIABLE_LENGTH_EXTENTS	VLEXTENTS	BIGINT	Not applicable for Db2 for i. Will always be -1.
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.
BLOCKED_INSERT_OPERATIONS	BLKIOPS	BIGINT	Number of blocked insert operations for the partition or member since the last IPL.
BLOCKED_INSERT_ROWS	BLKIROW	BIGINT	Number of rows inserted with blocked insert 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. 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	LASTSRCUPD	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.
PARTITION_TEXT	LABEL	VARGRAPHIC(50) CCSID 1200 Nullable	Text of the partition. Contains null if text does not exist for the partition.
PARTIAL_TRANSACTION	PARTIALTX	CHAR(1)	Indicates 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.
APPLY_STARTING_RECEIVER _LIBRARY	APYRCVLIB	VARCHAR(10) Nullable	The library containing the starting journal receiver. Contains null if APPLY_STARTING_RECEIVER is null.
APPLY_STARTING_RECEIVER	APYRCVNAME	VARCHAR(10) Nullable	Indicates 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.
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.