A DB2 table space is a set of volumes on disks that hold the data sets in which tables are actually stored. Every table is stored in table space. A table space consists of a number of VSAM linear data sets (LDSs). Table spaces are divided into equal-sized units, called pages. Each page is read from disk to the assigned buffer pool, or written from the buffer pool to disc, in a single operation. The page size is controlled by the buffer pool that you assign to the table space (4 KB, 8 KB, 16 KB, or 32 KB in size) for the data. The default page size is 4 KB.
Table space is a database object where actually the table data gets stored. It is a like child of the database, where there can be multiple table spaces within a database. Each table space contains the ‘containers’. There can be one or more containers per table space. Containers can be added/dropped to TS to increase the size, if it is a DMS managed TS.
Data in most table spaces can be compressed, which can allow you to store more data on each data page. You can let DB2 create and manage the table space for you by issuing a CREATE TABLE statement that does not specify an existing table space. DB2 creates a partition-by-growth or range-partitioned universal table space and assigns the table space to the default database and the default storage group. For more information, see Creation of table spaces.
You can also explicitly define table spaces by issuing CREATE TABLESPACE statements. You specify the database that the table space belongs to, and the storage group that it uses. When you create a table space, certain options that you specify control the type of table space that is created. In most cases, it is best to create universal table spaces. Other table space types are deprecated. That is, they are supported in DB2 10, but support might be removed in the future.
For descriptions of the various table space types, see Types of DB2 table spaces. Some table space types can contain more than one table. However, in the cases of the recommended universal table space types, each table space contains only a single table. For smaller tables, multiple-table segmented table spaces can sometimes help to reduce the number of data sets that need to be managed for backup and recovery, and the number of data sets that the database system needs to open and close during DB2 operations. Nevertheless, this type is deprecated.
Table space is nothing but the space where the tables are stored. A table space is a set of volumes or disks where the tables actually stored. Table space contains all the tables in the database. One table space can contain one or more tables. One table space can be stored on more than one VSAM file. Table space can be divided into equal parts, called as pages.
One operation on database can read one page at a time. To create the storage group, user required CREATETS privilege is required apart from the above two authorities mentioned in storage space and database. CREATE TABLESPACE is used to create table space.
ARBDP
Indicates that the index should be rebuilt to improve performance and allows the DB2 subsystem to pick this index for index-only access.
AREO*
Indicates that the table space, index, or partition identified should be reorganized for optimal performance.
ACHKP
Indicates an error in the LOB column of the base table space. The base table space has the auxiliary CHECK-pending restrictive status.
AREST
Indicates that an object (a table space, index space, or a physical partition of a table space or index space) is in an advisory RESTART-pending state. If backout activity against the object is not already underway, initiate it either by issuing the RECOVER POSTPONED command, or by recycling the system with the system parameter LBACKOUT=AUTO.
COPY
The object (a table space or a partition within a table space) is in the COPY-pending status. An image copy is required for this object.
CHKP
The object (a table space, a partition within a table space, or an index) is in the CHECK-pending status.
LSTOP
The logical partition of a nonpartitioning index is stopped.
REFP
The object (a table space, index space, or an index) is in the REFRESH-pending status.
RO
The database, table space, table space partition, index space, or index space partition is started for read-only activity.
STOPE
The table space or index space was implicitly stopped because there is a problem with the log RBA in a page. Message DSNT500I or DSNT501I is issued when the error is detected, indicating the inconsistency.
UTRO
A utility is in process, on the table space, table space partition, index space, or index space partition, that allows only RO access. If the utility was canceled before the object was drained, the object can allow SQL access because the object was not altered by the utility.
A table space is a logical entity used to define where tables and indexes will be stored within a database. All DB2 tables and indexes reside in table spaces, allowing complete control over where the table and index data are physically stored. A table space can be created using one or more underlying physical storage devices called containers. This provides the ability to create a physical database design that provides optimal performance in any physical environment. Details about the table spaces in a database can be obtained using, GET SNAPSHOT FOR TABLESPACES ON <database name>, LIST TABLESPACES
A table space is a logical database entity; table space containers are the physical storage associated with a table space. A container definition depends on the type of table space being created and can be defined as an operating system directory, a logical device/drive name, or a file. When a table space is created, it must have at least one container associated with it. A single table space can contain multiple containers, but a container can belong to only one table space. Details about a table space's containers can be obtained using the LIST TABLESPACE CONTAINERS FOR x command, where x is the table space's ID. A table space's ID can be found using the LIST TABLESPACES command and searching for the table space of interest.
The basic unit of storage in a DB2 database is the page, and pages can be different sizes. When pages are written to disk, they are grouped into contiguous ranges called extents. The extent size for a table space is specified for the table space when it is created and cannot be changed. the DFT_EXTENT_SZ database configuration parameter specifies the default extent size for all table spaces in the database. This value can be overridden when the table space is created, using the EXTENTSIZE parameter of the CREATE TABLESPACE statement.
When a table space is created with more than one container, DB2 will write the data to the containers in a round-robin fashion. DB2 will fill an extent in one container, then fill an extent in the next container, and so on, until it has written an extent in all of the containers in the table space. DB2 will then fill the second extent in the first container, and so on.
However, this may change if containers are added or removed using the ALTER TABLESPACE command. In Figure 1.5, the first extent (Extent 0) is written to Container 0, Extent 1 is written to Container 1, Extent 2 is written to Container 2. At this point, there is one extent in each of the containers, so DB2 will go back to the first container and add the next extent. Therefore, Extent 3 is written to Container 0, Extent 4 is written to Container 1, and so on, as more data is added.