Storage management view tables

STMG_OBJECT_TYPE table

The STMG_OBJECT_TYPE table contains one row for each supported storage type that can be monitored.

The STMG_OBJECT_TYPE must be specified as the first parameter to the capture_storagemgmt_info() stored procedure. For example:

sysproc.capture_storagemgmt_info(<stmg_object_type>, <object_schema>, <object_name>)

The first parameter, stmg_object_type, is defined by the entries in this table.

STMG_THRESHOLD_REGISTRY table

The STMG_THRESHOLD_REGISTRY table contains one row for each storage threshold type. The enabled thresholds are used by the analysis process when a storage snapshot is taken. If a threshold type is enabled, the threshold analysis will be performed on the data being monitored and threshold exceeded columns will be updated with the appropriate values for the specified threshold type.

Example:

To disable threshold analysis for table space space usage:

   db2 UPDATE SYSTOOLS.STMG_THRESHOLD_REGISTRY SET ENABLED = 'N' 
      WHERE STMG_TH_TYPE = 1

STMG_CURR_THRESHOLD table

The STMG_CURR_THRESHOLD table contains one row for each threshold type which is explicitly set for a storage object. When a new storage snapshot is taken, and threshold analysis is enabled for the objects being captured (see the Table 2), the values in this table are used to determine the warning and alarm thresholds that are set for each type of threshold being monitored. If an object under analysis does not have thresholds explicitly set in this table, the thresholds for the parent object for that object type are used. By default, this table contains three rows, one for each threshold type. The thresholds in these three rows are set for the database object, the parent of all other objects in the database. All objects included in the storage snapshot analysis will automatically inherit these thresholds from the database object unless a threshold is set explicitly on a child object such as a table space or table.

Example:

To set the space usage warning and alarm thresholds for all objects in the database to 90 and 95:

   db2 UPDATE SYSTOOLS.STMG_CURR_THRESHOLD SET WARNING_THRESHOLD = 90, 
       ALARM_THRESHOLD = 95
       WHERE STMG_TH_TYPE = 1 AND OBJ_TYPE = 0

STMG_ROOT_OBJECT table

The STMG_ROOT_OBJECT table contains one row for the root object of each storage snapshot. Complete storage snapshots can be deleted by deleting entries from this table.

Examples:

1. Delete all storage management snapshots:

      db2 DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT

2. Delete all table space snapshots:

      db2 DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT WHERE OBJ_TYPE = 2

STMG_OBJECT table

The STMG_OBJECT table contains one row for each storage object that is analyzed by the storage snapshots taken so far.

STMG_HIST_THRESHOLD table

The STMG_HIST_THRESHOLD table contains one row for each threshold used for the analyzing the storage objects at the time the storage snapshots are taken. This is basically a snapshot of what was in the SYSTOOLS.STMG_CURR_THRESHOLD table at the time of the snapshot.

STMG_DATABASE table

The STMG_DATABASE table contains one row for each detailed entry of database storage snapshots.

STMG_DBPGROUP table

The STMG_DBPGROUP table contains one row for each detailed entry of database partition group storage snapshots.

STMG_DBPARTITION table

The STMG_DBPARTITION table contains one row for each detailed entry of database partition storage snapshots. This is meant to be used along with the STMG_DBPGROUP table.

STMG_TABLESPACE table

The STMG_TABLESPACE table contains one row for each detailed entry of table space storage snapshots.

STMG_CONTAINER table

The STMG_CONTAINER table contains one row for each detailed entry of container storage snapshots.

STMG_TABLE table

The STMG_TABLE table contains one or more rows for each table included in the specified snapshot type. A database snapshot would insert entries for each table in the database. A table space snapshot would insert one or more rows for each table in the specified table space, a table snapshot would insert entries for the table specified in the snapshot command.

For non-partitioned tables, there would be exactly one row per table. For partitioned tables, there would one row per table space that the table resides in. For example, if a partitioned table was spread over 5 table spaces, there would be 5 rows in the STMG_TABLE for that table. Each row would contain information specific to a table space with one exception: Information that relates to table totals for partitioned tables are a summation of values taken from all the table spaces; each row would show the same value where a table total is kept.

STMG_TBPARTITION table

The STMG_TBPARTITION table contains one row for each detailed entry of table partition storage snapshots.

STMG_INDEX table

The STMG_INDEX table contains one row for each detailed entry of index storage snapshots.

STMG_OBJ_HISTORICAL_THRESHOLDS view

The STMG_OBJ_HISTORICAL_THRESHOLDS view contains one row for each captured snapshot object. This view can be used to determine the thresholds that were set for a given object at the time of the snapshot. It can also be used to determine easily which objects have exceeded their thresholds for data skew, cluster ratio, and space usage.