DB2 Tablespaces Test

If the target tablespace is a System-managed tablespace, then this measure will report the value System Managed Storage. On the other hand, if the tablespace is a database managed tablespace, then the value of this measure will be Database Managed Storage.

The SMS (System Managed Space) tablespaces allow the operating system to allocate and manage the space where the table data resides. Once the initial create has been completed, you cannot add or delete containers to an SMS tablespace. The data in the table spaces is striped by extent across all the containers in the system. An extent is a group of consecutive pages defined to the database. The file extension denotes the type of the data stored in the file. To distribute the data evenly across all containers in the table space, the starting extents for tables are placed in round-robin fashion across all containers. Such distribution of extents is particularly important if the database contains many small tables.

In a DMS (Database Managed Space) table space, the database manager controls the storage space. The storage model consists of a limited number of devices or files whose space is managed by DB2 Database for Linux, UNIX, and Windows. The database administrator decides which devices and files to use, and DB2 manages the space on those devices and files. The table space is essentially an implementation of a special purpose file system designed to best meet the needs of the database manager.

DMS table spaces are different from SMS table spaces in that space for DMS table spaces is allocated when the table space is created. For SMS table spaces, space is allocated as needed - i.e., on demand.

The numeric values that correspond to the tablespace types reported by this measure are as follows:

Note:

By default, this measure reports the Types displayed in the table above as its value. In the graph of the measure however, the types are represented using their corresponding numeric equivalents - i.e., 0 and 1.

This measure is available only for DMS tablespaces.

Ideally, the value of this measure should be low. A very high value or a consistent increase in this value could indicate a potential contention for space.

This measure is available only for DMS tablespaces. This is because, for SMS tablespaces, space is allocated on demand, and deallocated when not required; this implies that SMS tablespaces will at no time have any unused space. 

For DMS tablespaces, a high value is desired for this measure. A very low value or a gradual decrease in this value could be a cause for concern, as it indicates a slow, but steady space erosion.