DB2 Tablespaces Test

This test auto-discovers the tablespaces on an IBM DB2 server, and monitors the space usage of each tablespace.

Target of the test : A DB2 database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for each tablespace on the DB2 database server being monitored

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed
  2. HostThe IP address of the DB2 server
  3. PortThe port number through which the DB2 server communicates. The default port is 50000.
  4. user - Specify the name of the user who has any of the following privileges to the specified DATABASE: SYSADM or SYSCTRL or SYSMAINT or SYSMON. You can create a separate user on the OS hosting the DB2 server for this purpose, and assign any of the aforesaid privileges to that user. The steps for the same are detailed in the Creating a Special User for Monitoring DB2.
  5. password - Enter the password of the specified USER in the PASSWORD text box.
  6. confirm password – Confirm the password by retyping it here.
  7. database - Specify the name of the database on the monitored DB2 server to be used by this test
  8. query - The test emulates a user executing a query on the specified DATABASE, and thus determines the availability and responsiveness of the database server. In the QUERY text box, specify the select query to execute.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Tablespace type:

 

Indicates the tablespace type.

 

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:

Numeric Value Tablespace Type
0 Database Managed Storage
1 System Managed Storage

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.

Usable space:

Indicates the amount of space allocated to this tablespace.

MB

This measure is available only for DMS tablespaces.

Used space:

Indicates the space used in this tablespace.

MB

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.

Free space:

Indicates the free space in this tablespace.

MB

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.

Availability:

Indicates the percentage of free space in this tablespace.

Percent

As free space value is applicable only for DMS tablespaces, this will be available only for DMS tablespaces. The test will not report this measure for SMS tablepsaces.

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.