SYSTABLEPART catalog table

The SYSTABLEPART table contains one row for each nonpartitioned table space and one row for each partition of a partitioned table space. The schema is SYSIBM.

Column name Data type Description Use
PARTITION
SMALLINT
NOT NULL
 Partition number; 0 if table space is not partitioned. G
TSNAME
VARCHAR(24)
NOT NULL
 Name of the table space. G
DBNAME
VARCHAR(24)
NOT NULL
 Name of the database that contains the table space. G
IXNAME
VARCHAR(128)
NOT NULL
 Name of the partitioning index. This column is blank unless the table uses index-controlled partitioning. G
IXCREATOR
VARCHAR(128)
NOT NULL
 The schema of the partitioning index. This column is blank unless the table uses index-controlled partitioning. G
PQTY
INTEGER
NOT NULL
 For user-managed data sets, the value is the primary space allocation in units of 4 KB storage blocks or -1.

PQTY is based on a value of PRIQTY in the appropriate CREATE or ALTER TABLESPACE statement. However, unlike PQTY, PRIQTY asks for space in 1 KB units.

A value of -1 indicates that either of the following cases is true:

  • PRIQTY was not specified for a CREATE TABLESPACE statement or for any subsequent ALTER TABLESPACE statements.
  • -1 was the most recently specified value for PRIQTY, either on the CREATE TABLESPACE statement or a subsequent ALTER TABLESPACE statement.
G
SQTY
SMALLINT
NOT NULL
 For user-managed data sets, the value is the secondary space allocation in units of 4 KB storage blocks or -1.

SQTY is based on a value of SECQTY in the appropriate CREATE or ALTER TABLESPACE statement. Unlike SQTY, however, SECQTY asks for space in 1 KB units.

A value of -1 indicates that either of the following cases is true:

  • SECQTY was not specified for a CREATE TABLESPACE statement or for any subsequent ALTER TABLESPACE statements.
  • -1 was the most recently specified value for SECQTY, either on the CREATE TABLESPACE statement or a subsequent ALTER TABLESPACE statement.

If the value does not fit into the column, the value of the column is 32767. See the description of column SECQTYI.

 G
STORTYPE
CHAR(1)
NOT NULL
 Type of storage allocation:
E
Explicit (storage group not used)
I
Implicit (storage group used)

This column is not used for rows that represent catalog table spaces. Catalog data sets are managed by Db2

 G
STORNAME
VARCHAR(128)
NOT NULL
 Name of storage group used for space allocation. Blank if storage group not used.

This column is not used for rows that represent catalog table spaces. Catalog data sets are managed by Db2

 G
VCATNAME
VARCHAR(24)
NOT NULL
 Name of integrated catalog facility catalog used for space allocation.

This column is not used for rows that represent catalog table spaces. Catalog data sets are managed by Db2

 G
CARD
INTEGER
NOT NULL
 Number of rows in the table space or partition or, if the table space is a LOB table space, the number of LOBs in the table space. The value is '2147483647' if the number of rows is greater than or equal to '2147483647'. The value is -1 if statistics were not gathered. G
FARINDREF
INTEGER
NOT NULL
 Number of rows that are relocated far from their original page. The value is -1 if statistics were not gathered. Not applicable if the table space is a LOB table space. S
NEARINDREF
INTEGER
NOT NULL
 Number of rows that are relocated near their original page. The value is -1 if statistics were not gathered. Not applicable if the table space is a LOB table space. S
PERCACTIVE
SMALLINT
NOT NULL
 Percentage of space occupied by rows of data from active tables. The value is -1 if statistics were not gathered. The value is -2 if the table space is a LOB table space.

This value is not applicable for understanding data distribution in tables that are organized for hash access.

 S
PERCDROP
SMALLINT
NOT NULL
 Percentage of space that rows of dropped tables occupy. The value is -1 if statistics were not gathered. The value is 0 for segmented table spaces. Not applicable if the table is an auxiliary table. S
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.

 G
LIMITKEY
VARCHAR(765)
NOT NULL
The high value of the partition in external format. If the table space was converted from index-controlled partitioning to table-controlled partitioning, the value is the highest possible value for an ascending key, or the lowest possible value for a descending key. If the table is not in a partition-by-range table space, the value is an empty string.
This column can contain a mixture of values with differing formats:
  • Date and time values are delimited by single quotation marks (for example, '2001-01-01'). However, values that were added before Db2 11 do not contain these delimiters.
  • When comma decimal point indicators are used, a space follows any comma delimiter in the value. The comma decimal point indicator is used when the DECIMAL POINT IS field setting is ,(comma) or a COBOL program that executes the ALTER statement uses the COMMA processing option. No spaces follow comma delimiters when period decimal point indicators are used, or for values added before Db2 11.
 S
FREEPAGE
SMALLINT
NOT NULL
 Number of pages loaded before a page is left as free space. G
PCTFREE
SMALLINT
NOT NULL
 Percentage of each page that is left as free space. G
CHECKFLAG
CHAR(1)
NOT NULL WITH
DEFAULT
blank
The table space is not a partition, or does not contain rows that might violate referential constraints, check constraints, or both.
C
The table space partition is in a check-pending status and the table contains rows that can violate referential constraints, check constraints, or both.
D
The inline length of the LOB column that is associated with this LOB table space was decremented when the inline length was altered.
I
The inline length of the LOB column that is associated with this LOB table space was incremented when the inline length was altered.
 G
CHECKRID
CHAR(4)
NOT NULL WITH
DEFAULT
FOR BIT DATA
 Not used. N
SPACE
INTEGER
NOT NULL WITH
DEFAULT
 Number of kilobytes of DASD storage that is allocated to the table space partition, as determined by the last execution of the STOSPACE utility or RUNSTATS utility.
0
The STOSPACE or RUNSTATS utility was not run.
-1
The table space was defined with the DEFINE NO clause, which defers the physical creation of the data sets until data is first inserted into one of the partitions, and data has yet to be inserted.
-2
The value exceeds the maximum size for an integer value. See the SPACEF column value.
Nonzero or nonnegative value
An auxiliary table in the LOB table space.
The STOSPACE utility updates this value if the table space is related to a storage group. The RUNSTATS utility updates this value when RUNSTATS TABLESPACE is run with UPDATE(ALL) or UPDATE(SPACE).		 G
COMPRESS
CHAR(1)
NOT NULL WITH
DEFAULT
  • For a table space partition, indicates the COMPRESS attribute for the partition.
  • For a nonpartitioned table space, indicates the COMPRESS attribute for the table space.
Values for the column can be:
Y
The table space or partition is defined to use compression. If the table space is not a LOB table space, the compression algorithm is determined by the TS_COMPRESSION_TYPE subsystem parameter.
F
The table space or partition is defined to use fixed-length compression.
H
The table space or partition is defined to use Huffman compression.
blank
The table space or partition is not defined to use compression.
 G
PAGESAVE
SMALLINT
NOT NULL WITH
DEFAULT
 Percentage of pages that are saved in the table space or partition as a result of defining the table space with compression. For example, a value of 25 indicates a savings of 25 percent, so that the pages required are only 75 percent of what would be required without data compression. The calculation includes overhead bytes for each row, the bytes required for dictionary, and the bytes required for the current FREEPAGE and PCTFREE specification for the table space or partition. This calculation is based on an average row length, and the result varies depending on the actual lengths of the rows. The value is 0 if there are no savings from using data compression, or if statistics were not gathered. The value can be negative, if for example, data compression causes an increase in the number of pages in the data set. S
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT

If RUNSTATS or another utility with inline statistics updated the statistics, the date and time when the last utility invocation updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected.

 G
GBPCACHE
CHAR(1)
NOT NULL WITH
DEFAULT
 Group buffer pool cache option that is specified for this table space or table space partition.
A
Changed and unchanged pages are cached in the group buffer pool.
N
No data is cached in the group buffer pool.
S
Only changed system pages, such as space map pages that do not contain actual data values, are cached in the group buffer pool.
blank
Only changed pages are cached in the group buffer pool.
 G
CHECKRID5B
CHAR(5)
NOT NULL WITH
DEFAULT
FOR BIT DATA
 Blank if the table or partition is not in a check-pending status (CHECKFLAG is blank), or if the table space is not partitioned. Otherwise, the RID of the first row of the table space partition that can violate referential constraints, check constraints, or both; or the value is X'0000000000', indicating that any row can violate referential constraints. S
TRACKMOD
CHAR(1)
NOT NULL WITH
DEFAULT
 Whether to track the page modifications in the space map pages:
N
No
blank
Yes

Start of changeThis column is not applicable for LOB table spaces.End of change

 G
EPOCH
INTEGER
NOT NULL WITH
DEFAULT
 A number that increments whenever a utility operation that changes the location of rows in a table occurs. G
SECQTYI
INTEGER
NOT NULL WITH
DEFAULT
 Secondary space allocation in units of 4KB storage. For user-managed data sets, the value is the secondary space allocation in units of 4KB blocks. G
CARDF
FLOAT
NOT NULL WITH
DEFAULT -1
 Number of rows in the table space or partition, or if the table space is a LOB table space, the number of LOBs in the table space. The value is -1 if statistics were not gathered. G
IPREFIX
CHAR(1)
NOT NULL WITH
DEFAULT 'I'
 The first character of the instance qualifier for the data set name for the table space or partition. 'I' or 'J' are the only valid characters for this field. The default is 'I'. G
ALTEREDTS
TIMESTAMP
NOT NULL WITH
DEFAULT
 Time when the most recent ALTER TABLESPACE statement was executed for the table space or partition. If no ALTER TABLESPACE statement was applied, the value is '0001-01-01.00.00.00.000000'. G
SPACEF
FLOAT(8)
NOT NULL WITH
DEFAULT -1
 DASD storage in KB. The value is -1 if statistics were not gathered. The value might be nonzero for an auxiliary table in the LOB table space. This column can be updated. G
DSNUM
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of data sets. The value is -1 if statistics were not gathered. This column can be updated. G
EXTENTS
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of data set extents. The value is -1 if statistics were not gathered. This column can be updated. This value is only for the last DSNUM for the object. G
LOGICAL_PART
SMALLINT
NOT NULL WITH
DEFAULT      
 The logical partition number (logical ascending or descending order) for partitioned table spaces. The physical partition number is kept in column PARTITION. LOGICAL_PART is 0 for nonpartitioned table spaces. For partitioned table spaces that were created before Version 8, LOGICAL_PART is originally 0, but might be changed later to a nonzero value by processes such as conversion to table-controlled partitioning. G
LIMITKEY_INTERNAL
VARCHAR(512)
NOT NULL WITH
DEFAULT
FOR BIT DATA
 The highest value of the limit key of the partition in an internal format. If the table uses index-controlled partitioning instead of table-controlled partitioning or the table is not partitioned, the value is an empty string. If the table space was converted from index-controlled partitioning to table-controlled partitioning, the value is the highest possible value for an ascending key, or the lowest possible value for a descending key. If any column of the key has a field procedure, the internal format is the encoded form of the value. S
OLDEST_VERSION
SMALLINT
NOT NULL WITH
DEFAULT
 The version number of the oldest format of data in the table part and any image copies at the part level. G
CREATEDTS
TIMESTAMP
NOT NULL WITH
DEFAULT
Time when the partition was created. G
AVGROWLEN
INTEGER
NOT NULL WITH
DEFAULT -1
Average length of rows for the tables in the table space or part. If the table space or part is compressed, the value is the compressed row length. If the table space or part is not compressed, the value is the uncompressed row length. The value is -1 if statistics were not gathered. G
FORMAT
CHAR(1)
NOT NULL WITH
DEFAULT
 Indicates the format of the rows in the table space or partition:
R
Indicates reordered row format.
blank
Indicates basic row format or a LOB table space.
 G
RELCREATED
CHAR(1)
NOT NULL
 The release of Db2 that is used to create the object. Blank if created before Version 9. See Release dependency indicators for all other values. G
REORG_LR_TS
TIMESTAMP
NOT NULL WITH
DEFAULT
 The time when the REORG or LOAD REPLACE utility last occurred. The default value is '0001-01-01.00.00.00.000000'. G
HASHSPACE
BIGINT
NOT NULL WITH
DEFAULT
 For partition-by-range (UTS) table spaces, the amount of space, in KB, specified at the partition level to override the space specification at the table level. If no override is provided it is the same as the value of HASHSPACE in the SYSIBM.SYSTABLEPSACE catalog table.

For partition-by-growth table spaces, this value is zero.

 G
HASHDATAPAGES
BIGINT
NOT NULL WITH
DEFAULT
 For partition-by-range table spaces, the number of hash data pages that correspond to the value of the HASHSPACE column for each partition. The value is 0 for table spaces that are changed to use hash access but not reorganized.

For partition-by-growth table spaces, the value is zero.

 G
RBA_FORMAT
CHAR(1)
NOT NULL WITH
DEFAULT
 The RBA and LRSN format for the page sets of the table partition:
B
The page sets are still in the basic 6-byte format.
E
The page sets are converted to the extended 10-byte format.
U
The format is undefined because DEFINE NO was specified when the table space was created.
blank
The page sets are still in the basic 6-byte format, the table partition was created before Db2 11 new-function mode (NFM), and no utility that sets the RBA_FORMAT value was run for the table partition in Db2 11 NFM or higher.
 G
PCTFREE_UPD
SMALLINT
NOT NULL
WITH DEFAULT
 The percentage of free space that is reserved for updates to variable length records, as defined when the object as created or altered. G
PCTFREE_UPD_CALC
SMALLINT
NOT NULL
WITH DEFAULT
 The percentage of free space that is reserved for updates to variable length records, which are calculated by Db2 or utilities. G
TYPE
CHAR(1)
WITH DEFAULT
NULL
The type of partition.
blank
The table space was created without the LOB or MEMBER CLUSTER options. If the DSSIZE column is zero, the table space is not greater than 64 gigabytes.
G
The table space was defined with the MAXPARTITIONS option (a partitioned-by-growth table space) with the underlying structure of a universal table space.
L
The table space can be greater than 64 gigabytes.
O
The table space was defined with the LOB option (the table space is a LOB table space).
P
Implicit table space created for XML columns.
R
partition-by-range table space.
This value might be NULL for table spaces that were created before Db2 12. In that case, Db2 uses the value from the SYSTABLESPACE.TYPE column.		 G
PAGENUM
CHAR(1)
NOT NULL
WITH DEFAULT
'A'
Format of pages for the table space and indexes created on tables in the table space, indicating absolute or relative page numbering.
A
Indicates absolute addressing so that PAGENUM contains the embedded partition number.
R
Indicates relative addressing so that PAGENUM contains only the relative page number.
G
BPOOL
CHAR(8)
WITH DEFAULT
NULL
 Name of the buffer pool used for the partition.

The value might be NULL for table spaces that were created before Db2 12. In that case, Db2 uses the value in the SYSTABLESPACE.BPOOL column.

 G
PGSIZE
SMALLINT
WITH DEFAULT
NULL
 Size of pages in the table space in kilobytes.

The value might be NULL for table spaces that were created before Db2 12. In that case, Db2 uses the value in the SYSTABLESPACE.PGSIZE column.

 G
DSSIZE
INTEGER
WITH DEFAULT
NULL
 Maximum size on a partitioned table space data set. 0 for a nonpartitioned table space.

The value might be NULL for table spaces that were created before Db2 12. In that case, Db2 uses the value in SYSTABLESPACE.DSSIZE column.

 G
MEMBER_CLUSTER
CHAR(1)
WITH DEFAULT
NULL
Indicates whether MEMBER CLUSTER is specified for the table space.
Y
MEMBER CLUSTER is specified for the table space.
blank
MEMBER CLUSTER is not specified for the table space.

The value might be NULL for table spaces that were created before Db2 12. In that case, Db2 uses the value in the SYSTABLESPACE.MEMBER_CLUSTER column.

 G
COMPRESSRATIO
SMALLINT
NOT NULL
WITH DEFAULT
 Average percentage of bytes saved by compression on each compressed data record in the partition when the table space is defined with compression. This calculation includes overhead bytes for each row. The value is based on an average row length and varies depending on the actual length of the data rows.

For example, a value of 25 indicates that the average compressed record size is approximately 75% the size of the uncompressed record.

The value is -1 or 0 in the following cases:
-1
This value is not collected.
0
No compression exists or the average compressed record length is the same as or longer than the uncompressed record length.
 G
COMPRESS_USED
CHAR(1)
WITH DEFAULT
NULL
For a partitioned table space, this column indicates whether the partition is compressed. If the partition is compressed, the column indicates which compression algorithm is used.

For a nonpartitioned table space, this column indicates whether the table space is compressed. If the table space is compressed, the column indicates which compression algorithm is used.

In rare cases, this column is not updated when compression occurs. For more information, see Determining the effectiveness of compression.

F
Indicates that the table space or partition is compressed with fixed-length compression
H
Indicates that the table space or partition is compressed with Huffman compression
blank
If the table space is a LOB table space and COMPRESS is Y, zEnterprise® data compression (zEDC) hardware manages compression, if the hardware is available. Otherwise the table space or partition is not compressed.
NULL
The object was created before catalog level V12R1M509, and the value is unknown. The LOAD, REORG, RUNSTATS, RECOVER, or REPAIR CATALOG utility can be used to update this column for objects with a COMPRESS_USED value of NULL.
 G