Tablespaces

About Tablespaces

DB2 provides two types of tablespaces, namely:
  • System Managed Tablespace (SMS)
  • Database Managed Tablespace (DMS)

The properties of these tablespaces are:

  1. An SMS tablespace uses the facilities of your operating system to manage physical space.

  2. SMS tablespaces are easy to create and manage and are well suited for small and moderate-sized tablespaces.

  3. Each container is a directory in the filespace of your operating system. The space in this directory is not pre-allocated but grows as data is added to the tablespace. Data is stored in the form of files in the directory.

  4. Containers cannot be added to a tablespace after it is created.

  5. All the data for a given table including its indexes and large objects must be stored in a single tablespace

  1. In a DMS tablespace, the physical space is managed directly by UDB.

  2. DMS tablespaces provide an additional degree of control that can be helpful in large databases and high-performance applications.

  3. A container may be either a fixed-size pre-allocated file or a physical device such as a disk. In either case, all the storage for the container must be allocated when the container is created. If the container is a device, a single tablespace will occupy the entire device (which may be a logical device) and this device cannot be used for other tablespaces.

  4. Containers can be added to an existing tablespace using the ALTER TABLESPACE statement.

  5. The primary data for a table can be stored in one tablespace, its indexes in a second tablespace, and its large objects (LOBs) in a third tablespace. This technique can improve the clustering of the table in physical storage.