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
Parameter	Description
Test period	How often should the test be executed
Host	The IP address of the DB2 server
Port	The port number through which the DB2 server communicates. The default port is 50000.
User	Specify the name of the user who is authorized to access the target database server and collect the required metrics in this text box. You can create a separate user on the OS hosting the DB2 server for this purpose. The steps for the same are detailed in the Creating a Special User for Monitoring DB2 Server topic.
Password	Enter the password of the specified USER in the PASSWORD text box.
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.
Database	Specify the name of the database on the monitored DB2 server to be used by this test.
Include DB	Specify a comma-separated list of databases that you wish to monitor in the Include DB text box.
Exclude DB	Specify a comma-separated list of databases that need to be excluded from monitoring in the Exclude DB text box.
SSL	If the target database server is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Trust Store File Name	This parameter is applicable only if the target DB2 UDB database is SSL-enabled, if not, set this parameter to none. Specify the file name of the client-side SSL truststore that contains the server certificate required for establishing an SSL connection. The truststore is used to verify the identity of the server and enable a secure communication channel. By default, the truststore file should be placed in:<EG_INSTALL_DIR>/jre/lib/security/mytruststore.jks Here, mytruststore.jks is the Truststore file name. You may change this to any valid file name. By default, none is specified against this text box.
Trust Store Password	This parameter is applicable only if the target DB2 UDB database is SSL-enabled, if not, set this parameter to none. If a Truststore File name is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Truststore File. By default, this parameter is set to none.
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.

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.

This measure is available only for DMS tablespaces.

Used space:

Indicates the space used in this tablespace.

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.

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.

Free space 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.