SYSTABLESPACE catalog table

The SYSTABLESPACE table contains one row for each table space. The schema is SYSIBM.

Column name	Data type	Description	Use
NAME	VARCHAR(24) NOT NULL	Name of the table space.	G
CREATOR	VARCHAR(128) NOT NULL	Authorization ID of the owner of the table space.	G
DBNAME	VARCHAR(24) NOT NULL	Name of the database that contains the table space.	G
DBID	SMALLINT NOT NULL	Internal identifier of the database which contains the table space.	S
OBID	SMALLINT NOT NULL	Internal identifier of the table space file descriptor.	S
PSID	SMALLINT NOT NULL	Internal identifier of the table space page set descriptor.	S
BPOOL	CHAR(8) NOT NULL	Name of the buffer pool used for the table space.	G
PARTITIONS	SMALLINT NOT NULL	Number of partitions of the table space; 0 if the table space is not partitioned.	G
LOCKRULE	CHAR(1) NOT NULL	Lock size of the table space: A Any L Large object (LOB) P Page R Row S Table space T Table X implicitly created XML table space	G
PGSIZE	SMALLINT NOT NULL	Size of pages in the table space in kilobytes.	G
ERASERULE	CHAR(1) NOT NULL	Whether the data sets are to be erased when dropped. The value is meaningless if the table space is a partition-by-range table space. N No erase Y Erase	G
STATUS	CHAR(1) NOT NULL	Availability status of the table space: A Available C Definition is incomplete because the table space does not use table-controlled partitioning and a partitioning index has not been created. P Table space is in a check pending status. S Table space is in a check pending status with the scope less than the entire table space. T Definition is incomplete because no table has been created.	G
IMPLICIT	CHAR(1) NOT NULL	Whether the table space was created implicitly: N No Y Yes	G
NTABLES	SMALLINT NOT NULL	Number of tables defined in the table space.	G
NACTIVE	INTEGER NOT NULL	Number of active pages in the table space. A page is termed active if it is formatted for rows, even if it currently contains none. The value is 0 if statistics have not been gathered. This is an updatable column.	S
	VARCHAR(24) NOT NULL	Not used	N
CLOSERULE	CHAR(1) NOT NULL	Whether the data sets are candidates for closure when the limit on the number of open data sets is reached. N No Y Yes	G
SPACE	INTEGER NOT NULL	Number of kilobytes of DASD storage allocated to the table space, as determined by the last execution of the STOSPACE utility. The value is 0 if the table space is not related to a storage group, or if STOSPACE has not been run. If the table space is partitioned, the value is the total kilobytes of DASD storage allocated to all partitions that are storage group defined.	G
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. If ALTER TABLESPACE changes the DSSIZE value to 128G or 256G, this column value is changed to O, which is the release dependency indicator for Version 10. The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.	G
—	VARCHAR(54) NOT NULL	Internal use only.	I
—	VARCHAR(24) NOT NULL	Internal use only.	I
SEGSIZE	SMALLINT NOT NULL WITH DEFAULT	Number of pages in each segment of a segmented table space. The value is 0 if the table space is not segmented.	G
CREATEDBY	VARCHAR(128) NOT NULL WITH DEFAULT	Primary authorization ID of the user who created the table space.	G
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. This column can be updated.	G
LOCKMAX	INTEGER	The maximum number of locks per user to acquire for the table or table space before escalating to the next locking level. 0 Lock escalation does not occur. `n` `n`, where `n` > 0, is the maximum number of locks (row, page, or LOB locks for the table or table space) an application process can acquire before lock escalation occurs. -1 Indicates that LOCKMAX SYSTEM is in effect for the table space.	G
TYPE	CHAR(1) NOT NULL WITH DEFAULT	The type of table space: G Partition-by-growth table space (PBG UTS) R Partition-by-range table space (PBR UTS) O LOB table space P XML table space L Partitioned (non-UTS) table space created with the LARGE option (deprecated) blank One of the following deprecated types: Partitioned (non-UTS) table space Segmented (non-UTS) table space Simple table space	G
CREATEDTS	TIMESTAMP NOT NULL WITH DEFAULT	Time when the CREATE statement was executed for the table space. If the table space was created in a Db2 release prior to Version 5, the value is '0001-01-01.00.00.00.000000'.	G
ALTEREDTS	TIMESTAMP NOT NULL WITH DEFAULT	Time when the most recent ALTER TABLESPACE statement was executed for the table space. If no ALTER TABLESPACE statement has been applied, ALTEREDTS has the value of CREATEDTS. If the index was created in a Db2 release prior to Version 5, the value is '0001-01-01.00.00.00.000000'.	G
ENCODING_SCHEME	CHAR(1) NOT NULL WITH DEFAULT 'E'	Default encoding scheme for the table space: E EBCDIC A ASCII U Unicode blank For table spaces in a work file database or a TEMP database (a database that was created AS TEMP, which is for declared temporary tables.) The value is 'E' for tables in non work file databases and blank for tables in work file databases created prior to Version 5 or the default database, DSNDB04.	G
SBCS_CCSID	INTEGER NOT NULL WITH DEFAULT	Default SBCS CCSID for the table space. For a table space in a work file database, a TEMP database, or a database created in a Db2 release prior to Version 5, the value is 0.	G
DBCS_CCSID	INTEGER NOT NULL WITH DEFAULT	Default DBCS CCSID for the table space. For a table space in a work file database, a TEMP database, or a database created in a Db2 release prior to Version 5, the value is 0.	G
MIXED_CCSID	INTEGER NOT NULL WITH DEFAULT	Default mixed CCSID for the table space. For a table space in a work file database, a TEMP database, or a database created in a Db2 release prior to Version 5, the value is 0.	G
MAXROWS	SMALLINT NOT NULL DEFAULT 255	The maximum number of rows that Db2 will place on a data page. The default value is 255. For a LOB table space, the value is 0 to indicate that the column is not applicable.	G
LOCKPART	CHAR(1) NOT NULL WITH DEFAULT	Not used.	N
LOG	CHAR(1) NOT NULL WITH DEFAULT 'Y'	Whether the changes to a table space are to be logged. N This table space has the NOT LOGGED attribute. Undo and redo logging for the table space and all indexes for tables in the table space is suppressed. Logging is also suppressed for the auxiliary indexes for all auxiliary tables associated with tables in the table space. Y This table space has the LOGGED attribute. Normal logging is associated with modifications to this table space, all indexes for tables in this table space, and all auxiliary indexes for all auxiliary tables associated with tables in the table space. X This LOB or XML table space has the NOT LOGGED attribute. Undo and redo logging for the table space is suppressed. Also, the logging attribute for this LOB or XML table space is linked to the logging attribute of the associated base table space and might not be able to be altered independently. If the logging attribute of the base table space is altered to LOGGED, the logging attribute of the LOB or XML table space will also be altered to LOGGED.	G
NACTIVEF	FLOAT NOT NULL WITH DEFAULT -1	Number of active pages in the table space. A page is termed active if it is formatted for rows, even if it currently contains none. The value is -1 if statistics have not been gathered. This is an updatable column.	S
DSSIZE	INTEGER NOT NULL WITH DEFAULT	Maximum size of a data set in kilobytes. The value might be 0 if the table space was created prior to DB2® 10, but will contain the actual value after the table space is converted to a partitioned by growth table space.	G
OLDEST_VERSION	SMALLINT NOT NULL WITH DEFAULT	The version number of the oldest format of data in the table space and any image copies.	G
CURRENT_VERSION	SMALLINT NOT NULL WITH DEFAULT	The version number describing the newest format of data in the table space. A zero indicates that the table space has never had versioning. After the version number reaches the maximum value, the number wraps back to one.	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 have not been gathered.	G
SPACEF	FLOAT NOT NULL WITH DEFAULT	Kilobytes of DASD storage for the storage group. The value is -1 if statistics have not been gathered. This is an updatable column.	G
CREATORTYPE	CHAR(1) NOT NULL WITH DEFAULT	Indicates the type of creator: blank Authorization ID L Role	G
RELCREATED	CHAR(1) NOT NULL	The release of Db2 that is used to create the object. Blank if created prior to DB2 9. See Release dependency indicators for all other values.	G
INSTANCE	SMALLINT NOT NULL WITH DEFAULT	INSTANCE indicates the data set instance number of the current base object (table and index).	G
CLONE	CHAR(1) NOT NULL WITH DEFAULT	Indicates whether the table space contains any objects that are involved in a clone relationship: Y Table space contains objects that are involved in a clone relationship N Table space does not contain any objects that are involved in a clone relationship	G
MAXPARTITIONS	SMALLINT NOT NULL WITH DEFAULT	Identifies the maximum number of partitions to which the table space can grow. 0 if the table space is not a partition-by-growth table space.	G
MEMBER_CLUSTER	CHAR(1) NOT NULL WITH DEFAULT	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	G
ORGANIZATIONTYPE	CHAR(1) NOT NULL WITH DEFAULT	Type of table space organization: blank Not known. Blank is the default. H Hash organization	G
HASHSPACE	BIGINT NOT NULL WITH DEFAULT	The amount of space, in KB, that is to be allocated to the table space or partition as hash space. For partition-by-growth table spaces, the space applies to the whole table space. For partition-by-range table spaces, the space is applicable for each partition.	G
HASHDATAPAGES	BIGINT NOT NULL WITH DEFAULT	The total number of hash data pages to preallocate for hash space. For partition-by-growth table spaces, this includes all pages in the fixed part of the table space. For partition-by-range table spaces, this is the number of pages in the fixed hash space in each partition unless it is overridden by providing hash space at the partition level. This is calculated by Db2 from the value specified with the HASH SPACE option or when the REORG utility is run with automatic estimation of space. The calculated value is used in the hash algorithm. The value is 0 for non-hash table spaces. The value is also 0 for table spaces which have been changed to use hash access but have not been reorganized.	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
INSERTALG	SMALLINT NOT NULL WITH DEFAULT	The insert algorithm level for tables in this table space. 0 The insert algorithm level for tables in this table space is determined by the DEFAULT_INSERT_ALGORITHM subsystem parameter. 0 is the default value. 1 The insert algorithm level for tables in this table space is the basic insert algorithm. 2 The insert algorithm level for tables in this table space is the fast insert algorithm when the MEMBER CLUSTER option is specified.
PQTY	INTEGER WITH DEFAULT 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. Unlike PQTY, however, PRIQTY accepts space in 1 KB units. A value of -1 indicates that one 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. This column contains the null value when the value is unknown for objects created prior to Db2 12.
STORTYPE	CHAR(1) WITH DEFAULT NULL	Type of storage allocation: E Explicit (storage group not used) I Implicit (storage group used) This column is not used for rows representing catalog table spaces. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
STORNAME	VARCHAR(128) WITH DEFAULT NULL	Name of storage group used for space allocation. Blank if storage group not used. This column is not used for rows representing catalog table spaces. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
VCATNAME	VARCHAR(24) WITH DEFAULT NULL	Name of integrated catalog facility catalog used for space allocation. This column is not used for rows representing catalog table spaces. Catalog data sets are managed by Db2 This column contains the null value when the value is unknown for objects created prior to Db2 12.
FREEPAGE	SMALLINT WITH DEFAULT NULL	Number of pages loaded before a page is left as free space. This column contains the null value when the value is unknown for objects created prior to Db2 12.
PCTFREE	SMALLINT WITH DEFAULT NULL	Percentage of each page left as free space. This column contains the null value when the value is unknown for objects created prior to Db2 12.
COMPRESS	CHAR(1) WITH DEFAULT NULL	Indicates the COMPRESS attribute for the table space. The following values are valid for the column: Y The table space 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. This column contains the null value when the value is unknown for objects created prior to Db2 12.	G
GBPCACHE	CHAR(1) WITH DEFAULT NULL	Group buffer pool cache option 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. This column contains the null value when the value is unknown for objects created prior to Db2 12.
TRACKMOD	CHAR(1) WITH DEFAULT NULL	Whether to track the page modifications in the space map pages: N No blank Yes This column contains the null value when the value is unknown for objects created prior to Db2 12. This column is not applicable for LOB table spaces.
SECQTYI	INTEGER WITH DEFAULT NULL	Secondary space allocation in units of 4 KB storage. For user-managed data sets, the value is the secondary space allocation in units of 4 KB blocks. This column contains the null value when the value is unknown for objects created prior to Db2 12.
PCTFREE_UPD	SMALLINT WITH DEFAULT NULL	The percentage of free space that is reserved for updates to variable length records, as defined when the object as created or altered. This column contains the null value when the value is unknown for objects created prior to Db2 12.
PCTFREE_UPD_CALC	SMALLINT WITH DEFAULT NULL	The percentage of free space that is reserved for updates to variable length records, calculated by Db2 or utilities. This column contains the null value when the value is unknown for objects created prior to Db2 12.
COMPRESSRATIO	SMALLINT NOT NULL WITH DEFAULT	Average percentage of bytes saved by compression on each compressed data record in the table space 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 has not been collected 0 No compression exists or the average compressed record length is the same as or longer than the uncompressed record length.	G
KEYLABEL	VARCHAR(192) NOT NULL WITH DEFAULT	The key label that is specified at the table level. Otherwise, the value is an empty string.