7.4. Table Spaces A table space is a logical object of your database. It is used to associate your logical tables and indexes to your physical devices (containers) and physical memory (buffer pools). All tables and indexes must reside inside a table space. 7.4.1. Table Space Classification Table spaces can be classified based on how the table space is managed and on what type of data they contain. Based on how the table space is managed, a table space can be one of the following types. System-managed space (SMS): This type of table space is managed by the operating system and requires minimal administration. This is the default table space type. Database-managed space (DMS): This type of table space is managed by the DB2 database manager, and it requires some administration.
SMS and DMS table spaces are discussed in detail in Chapter 8, The DB2 Storage Model. Based on the type of data it contains, a table space can be one of the following types. Regular: Use this type of table space to store any kind of data except temporary data. This is the default type. Large: Use this type of table space to store LONG VARCHAR, LONG VARGRAPHIC, or LOB data (see section 7.7.1.2, String Data Types) as well as index data. This table space is supported only with DMS table spaces. Temporary: Use this type of table space to hold temporary data. In turn, temporary table spaces can be further classified as two types. - - System: These table spaces hold temporary data required by the database manager to perform operations such as sorts or joins, which require extra space for processing a result set.
- - User: These table spaces hold temporary data from tables created with the DECLARE GLOBAL TEMPORARY TABLE statement. This type of table is explained in section 7.8.11, Temporary Tables.
NOTE System temporary table spaces and user temporary table spaces are commonly confused. Remember that system temporary table spaces are used by DB2, while user temporary table spaces are used by users when they declare global temporary tables.
NOTE Indexes can be stored in either regular or large table spaces.
To create a table space, use the CREATE TABLESPACE statement. A table space can be created with any of these page sizes: 4K, 8K, 16K, and 32K. A corresponding buffer pool of the same page size must exist prior to issuing this statement. Refer to Chapter 8, The DB2 Storage Model, for details. 7.4.2. Default Table Spaces When a database is first created, the following table spaces are created by default. SYSCATSPACE contains the DB2 system catalog tables and views. This set of tables and views contains system information about all the objects in the database. TEMPSPACE1 is used for system temporary data when DB2 needs temporary tables to process large sort or join operations. USERSPACE1 is the table space where most tables are created by default if a table space name has not been explicitly indicated in the CREATE TABLE statement. Section 7.8.3, User Tables, describes in detail the rules followed when a table space is implicitly assigned to a table.
|
