SYSIBM.SYSTABLESPACE table
The SYSIBM.SYSTABLESPACE table contains one row for each table space.
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:
|
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
range-partitioned table space.
|
G |
STATUS | CHAR(1)
NOT NULL |
Availability status of the table space:
|
G |
IMPLICIT | CHAR(1)
NOT NULL |
Whether the table space was created implicitly:
|
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.
|
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 came from the
basic machine-readable material (MRM) tape. 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 updated the statistics, the date and time when the last invocation of RUNSTATS updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This is an updatable column. |
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.
|
G |
TYPE | CHAR(1)
NOT NULL WITH DEFAULT |
The type of 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:
|
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 |
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.
|
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:
|
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:
|
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:
|
G |
ORGANIZATIONTYPE | CHAR(1)
NOT NULL WITH DEFAULT |
Type of table space 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 range-partitioned universal 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 range-partitioned universal 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 |