Saturday, 17 August 2013

DB2 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.
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.
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 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.
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.
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


Created with Artisteer

No comments:

Post a comment