DB2 Version 10.1 for Linux, UNIX, and Windows

SYSCAT.TABLESPACES catalog view

Each row represents a table space.

Table 1. SYSCAT.TABLESPACES Catalog View
Column Name Data Type Nullable Description
TBSPACE VARCHAR (128)   Name of the table space.
OWNER VARCHAR (128)   Authorization ID of the owner of the table space.
OWNERTYPE CHAR (1)  
  • S = The owner is the system
  • U = The owner is an individual user
CREATE_TIME TIMESTAMP   Time at which the table space was created.
TBSPACEID INTEGER   Identifier for the table space.
TBSPACETYPE CHAR (1)   Type of table space.
  • D = Database-managed space
  • S = System-managed space
DATATYPE CHAR (1)   Type of data that can be stored in this table space.
  • A = All types of permanent data; regular table space
  • L = All types of permanent data; large table space
  • T = System temporary tables only
  • U = Created temporary tables or declared temporary tables only
EXTENTSIZE INTEGER   Size of each extent, in pages of size PAGESIZE. This many pages are written to one container in the table space before switching to the next container.
PREFETCHSIZE INTEGER   Number of pages of size PAGESIZE to be read when prefetching is performed; -1 when AUTOMATIC.
OVERHEAD DOUBLE   Controller overhead and disk seek and latency time, in milliseconds (average for the containers in this table space); -1 when value is inherited from the storage group that the table space uses.
TRANSFERRATE DOUBLE   Time to read one page of size PAGESIZE into the buffer (average for the containers in this table space); -1 when value is inherited from the storage group that the table space uses.
WRITEOVERHEAD DOUBLE Y Reserved for future use.
WRITETRANSFERRATE DOUBLE Y Reserved for future use.
PAGESIZE INTEGER   Size (in bytes) of pages in this table space.
DBPGNAME VARCHAR (128)   Name of the database partition group that is associated with this table space.
BUFFERPOOLID INTEGER   Identifier for the buffer pool that is used by this table space (1 indicates the default buffer pool).
DROP_RECOVERY CHAR (1)   Indicates whether or not tables in this table space can be recovered after a drop table operation.
  • N = Tables are not recoverable
  • Y = Tables are recoverable
NGNAME1 VARCHAR (128)   Name of the database partition group that is associated with this table space.
DEFINER2 VARCHAR (128)   Authorization ID of the owner of the table space.
DATATAG SMALLINT   A tag to identify data stored in this table space. Valid user-specified range is 1 through 9; 0 indicates no data tag specified; -1 indicates value is inherited from the storage group that the table space uses.
SGNAME VARCHAR (128) Y Name of the storage group the table space is using; null value when the table space is not using automatic storage.
SGID INTEGER   Identifier of the storage group the table space is using; -1 when the table space is not using automatic storage.
EFFECTIVEPREFETCHSIZE INTEGER   Value of the effective prefetch size when PREFETCHSIZE is set to -1 (AUTOMATIC); otherwise same as PREFETCHSIZE.
BPTIERNUM SMALLINT   Reserved for future use.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.
Note:
  1. The NGNAME column is included for backwards compatibility. See DBPGNAME.
  2. The DEFINER column is included for backwards compatibility. See OWNER.