SYSTABLESTAT
The SYSTABLESTAT view contains one row for every table that has at least one partition or member. If the table has more than one partition or member, the statistics include all partitions and members. If the table is a distributed table, the partitions that reside on other database nodes are not included. They are contained in the catalog views of the other database nodes.
The following table describes the columns in the SYSTABLESTAT 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. |
| PARTITION_TYPE | PARTTYPE | CHAR(1) | The type of the table partitioning:
|
| NUMBER_PARTITIONS | NBRPARTS | INTEGER | Number of partitions or members of the table. |
| 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 all partitions or members of the table. |
| NUMBER_ROW_PAGES | NPAGES | BIGINT | Number of 64K pages in all partitions or members of the table. |
| 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 all partitions or members of the table. |
| DATA_SIZE | SIZE | BIGINT | Total size (in bytes) of the data spaces in all partitions or members of the table. |
| VARIABLE_LENGTH_SIZE | VLSIZE | BIGINT | Size (in bytes) of the variable-length data space segments in all partitions or members of the table. |
 VARIABLE_LENGTH_SEGMENTS |
VLSEGMENTS |
BIGINT |
The number of variable-length data space segments in all partitions
or members of the table. |
| 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 all partitions or members of the table. |
| MAINTAINED_TEMPORARY_INDEX_SIZE | MTISIZE | BIGINT | Size (in bytes) of all maintained temporary indexes over any partitions or members of the table. |
| NUMBER_DISTINCT_INDEXES | DISTINCTIX | INTEGER | The number of distinct indexes built over any partitions or members of the table. This does not include maintained temporary indexes. |
| OPEN_OPERATIONS | OPENS | BIGINT | Number of full opens of all partitions or members of the table since the last IPL. |
| CLOSE_OPERATIONS | CLOSES | BIGINT | Number of full closes of all partitions or members of the table since the last IPL. |
| INSERT_OPERATIONS | INSERTS | BIGINT | Number of insert operations of all partitions or members of the table 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 of all partitions or members of the table since the last IPL. |
| DELETE_OPERATIONS | DELETES | BIGINT | Number of delete operations of all partitions or members of the table since the last IPL. |
| CLEAR_OPERATIONS | DSCLEARS | BIGINT | Number of clear operations (CLRPFM operations) of all partitions or members of the table since the last IPL. |
| COPY_OPERATIONS | DSCOPIES | BIGINT | Number of data space copy operations (certain CPYxxx operations) of all partitions or members of the table since the last IPL. |
| REORGANIZE_OPERATIONS | DSREORGS | BIGINT | Number of data space reorganize operations (non-interruptible RGZPFM operations) of all partitions or members of the table since the last IPL. |
| INDEX_BUILDS | DSINXBLDS | BIGINT | Number of creates or rebuilds of indexes that reference any partition or member of the table since the last IPL. This does not include maintained temporary indexes. |
| LOGICAL_READS | LGLREADS | BIGINT | Number of logical read operations of all partitions or members of the table since the last IPL. |
| PHYSICAL_READS | PHYREADS | BIGINT | Number of physical read operations of all partitions or members of the table since the last IPL. |
| SEQUENTIAL_READS | SEQREADS | BIGINT | Number of sequential read operations of all partitions or members of the table since the last IPL. |
| RANDOM_READS | RANREADS | BIGINT | Number of random read operations of all partitions or members of the table since the last IPL. |
| LAST_CHANGE_TIMESTAMP | LASTCHG | TIMESTAMP | Maximum timestamp of the last change that occurred to any partition or member of the table. |
| LAST_SAVE_TIMESTAMP | LASTSAVE | TIMESTAMP Nullable
|
Minimum timestamp of the last save of any partition or member of the table. If no partition or member has been saved, contains null. |
| LAST_RESTORE_TIMESTAMP | LASTRST | TIMESTAMP Nullable
|
Maximum timestamp of the last restore any partition or member of the table. If no partition or member has been restored, contains null. |
| LAST_USED_TIMESTAMP | LASTUSED | TIMESTAMP Nullable
|
Maximum timestamp of the last time any partition or member was used directly by an application for native record I/O or SQL operations. If no partition or member has ever been used, contains null. |
| DAYS_USED_COUNT | DAYSUSED | INTEGER | Maximum number of days any 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 no partition or member has been used since the last time the usage statistics were reset, contains 0. |
| LAST_RESET_TIMESTAMP | LASTRESET | TIMESTAMP Nullable
|
Maximum timestamp of the last time the usage statistics were reset for the table. For more information see the Change Object Description (CHGOBJD) command. If no partition or member's last used timestamp has ever been reset, 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. |
| VOLATILE | VOLATILE | CHAR(1) | Indicates whether the table is volatile.
|
| SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System schema name. |
| SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name. |