This topic describes the Storage management view tables.
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.
Column name | Data type | Nullable | Description |
---|---|---|---|
OBJ_TYPE | INTEGER | N | Integer value corresponds to a type of storage
object
|
TYPE_NAME | VARCHAR | N | Descriptive name of the storage object type
|
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.
To disable threshold analysis for table space space usage:
db2 UPDATE SYSTOOLS.STMG_THRESHOLD_REGISTRY SET ENABLED = 'N' WHERE STMG_TH_TYPE = 1
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TH_TYPE | INTEGER | N | Integer value corresponds to a storage threshold
type
|
ENABLED | CHARACTER | N | Y = the threshold is enabled N = the threshold is not enabled and therefore will not be compared against during storage analysis |
STMG_TH_NAME | VARCHAR | Y | Descriptive name of the storage threshold
|
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.
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
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TH_TYPE | INTEGER | N | Integer value corresponds to a storage threshold type. See Table 2 for a definition of threshold types. |
OBJ_TYPE | INTEGER | N | Integer value corresponds to a type of storage object. See Table 1 for a definition of threshold types. |
OBJ_NAME | VARCHAR | N | The name of the storage object. |
OBJ_SCHEMA | VARCHAR | N | The schema of the storage object. "-" is used when schema is not applicable for the object |
WARNING_THRESHOLD | SMALLINT | Y | The value of the warning threshold set for the storage object. |
ALARM_THRESHOLD | SMALLINT | Y | The value of the alarm threshold set for the storage object. |
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.
db2 DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT
db2 DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT WHERE OBJ_TYPE = 2
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_TYPE | INTEGER | N | Integer value corresponds to a type of storage object. See Table 1 for a definition of threshold types. |
ROOT_ID | VARCHAR | N | The ID of the root object. |
The STMG_OBJECT table contains one row for each storage object that is analyzed by the storage snapshots taken so far.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates the time the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
ROOT_ID | CHARACTER | N | The ID of the root object. |
OBJ_TYPE | INTEGER | N | Integer value corresponds to a type of storage object. See Table 1 for a definition of threshold types. |
OBJ_SCHEMA | VARCHAR | N | The schema of the storage object. "-" is used when schema is not applicable for the object |
OBJ_NAME | VARCHAR | N | The name of the storage object. |
DBPG_NAME | VARCHAR | Y | The name of the database partition group the object residing in. Null if not applicable. |
TS_NAME | VARCHAR | Y | The name of the table space the object residing in. Null if not applicable. |
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.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates the time the data capturing process started. |
STMG_TH_TYPE (PK) | INTEGER | N | Integer value corresponds to a storage threshold type. See Table 2 for a definition of threshold types. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
WARNING_THRESHOLD | SMALLINT | Y | The value of the warning threshold set for the storage object at the time the storage snapshot was taken. |
ALARM_THRESHOLD | SMALLINT | Y | The value of the alarm threshold set for the storage object at the time the storage snapshot was taken |
The STMG_DATABASE table contains one row for each detailed entry of database storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the database, identified by OBJ_ID column. |
REMARKS | VARCHAR | Y | User-specified remarks. |
The STMG_DBPGROUP table contains one row for each detailed entry of database partition group storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the database partition group, identified by OBJ_ID column. |
PARTITON_COUNT | SMALLINT | Y | The number of database partitions included in the database partition group. |
TARGET_LEVEL | BIGINT | Y | The average data size, in bytes, over all the database partitions contained by the database partition group. It is the target level of even data distribution. |
DATA_SKEW | SMALLINT | Y | A percentage of the maximum data size deviation from the TARGET_LEVEL among all the database partitions. This value is used during data capture and analysis process to be compared against the data distribution skew set for the database partition group in the Table 3. |
TOTAL_SIZE | BIGINT | Y | The total size, in bytes, over all the database partitions contained by the database partition group. It is the sum of the total size (number of pages multiplied by page size) of all table spaces defined under the database partition group. For DMS table spaces, the total size is the allocated size; for SMS table spaces, it is the size of the currently used by the table space. |
DATA_SIZE | BIGINT | Y | The data size, in bytes, over all the database partitions contained by the database partition group. It is the sum of the data size (number of data pages multiplied by page size) of all table spaces defined under the database partition group. |
PERCENT_USED | SMALLINT | Y | A percentage value of data size over total size. This value is compared against the space usage threshold during the data capture and analysis process. In the case of SMS table spaces, the space usage threshold for the table space or its parent database partition group should be set to 100 to avoid unnecessary alarms. |
REMARKS | VARCHAR | Y | User-specified remarks. |
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.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
PARTITION_NUM (PK) | INTEGER | Y | The database partition number. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the database partition, identified by OBJ_ID column. |
DBPG_NAME | CHARACTER | Y | The name of database partition group. |
IN_USE | CHARACTER | Y | Status of the database partition at the time of the storage snapshot. Same as IN_USE column in SYSCAT.DBPARTITIONGROUPDEF. |
HOST_NAME | VARCHAR | Y | The host name of the database partition. |
HOST_SYSTEM_SIZE | BIGINT | Y | NOT AVAILABLE. |
EST_DATA_SIZE | BIGINT | Y | The estimated data size on the database partition, within the database partition group scope. This value is calculated as the sum of the data size for that portion of the table found on the given partition. |
The STMG_TABLESPACE table contains one row for each detailed entry of table space storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the table space, identified by OBJ_ID column. |
TYPE | CHARACTER | Y | As defined in SYSCAT.TABLESPACES. |
DATATYPE | CHARACTER | Y | As defined in SYSCAT.TABLESPACES. |
TOTAL_SIZE | BIGINT | Y | As defined in SYSCAT.TABLESPACES. |
PERCENT_USED | SMALLINT | Y | As defined in SYSCAT.TABLESPACES. This is used during data capture and analysis process to be compared against the space usage threshold in the STMG_CURR_THRESHOLD table. |
DATA_SIZE | BIGINT | Y | DATA_PAGE * PAGE_SIZE. |
DATA_PAGE | BIGINT | Y | USED_PAGES as defined in SYSPROC.SNAPSHOT_TBS_CFG table UDF. |
EXTENT_SIZE | INTEGER | Y | As defined in SYSCAT.TABLESPACES. |
PREFETCH_SIZE | INTEGER | Y | As defined in SYSCAT.TABLESPACES. |
OVERHEAD | DOUBLE | Y | As defined in SYSCAT.TABLESPACES. |
TRANSFER_RATE | DOUBLE | Y | As defined in SYSCAT.TABLESPACES. |
BUFFERPOOL_ID | INTEGER | Y | As defined in SYSCAT.TABLESPACES. |
PAGE_SIZE | INTEGER | Y | As defined in SYSCAT.TABLESPACES. |
The STMG_CONTAINER table contains one row for each detailed entry of container storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the container, identified by OBJ_ID column. |
TABLESPACE_ID | INTEGER | Y | tablespace_id - Table Space Identification monitor element |
CONTAINER_ID | INTEGER | Y | container_id - Container Identification monitor element |
PARTITION_NUM | INTEGER | Y | node_number - Node Number monitor element |
CONTAINER_TYPE | CHARACTER | Y | container_type - Container Type monitor element |
TOTAL_PAGES | BIGINT | Y | container_total_pages - Total Pages in Container monitor element |
USABLE_PAGES | BIGINT | Y | container_usable_pages - Usable Pages in Container monitor element |
ACCESSIBLE | BIGINT | Y | container_accessible - Accessibility of Container monitor element |
STRIPE_SET | BIGINT | Y | container_stripe_set - Stripe Set monitor element |
FILESYSTEM_NODENAME | BIGINT | Y | The node name of the file system in which the container is defined. |
FILESYSTEM_ID | BIGINT | Y | The unique file system identifier. |
FILESYSTEM_MOUNT_POINT | VARCHAR | Y | The file system mount point. |
FILESYSTEM_TYPE_NAME | VARCHAR | Y | File system type. For example, jfs, jfs2, ext2, ntfs or gpfs. |
FILESYSTEM_DEVICE_TYPE | BIGINT | Y | File system device type. |
FILESYSTEM_TOTAL_SIZE | BIGINT | Y | The total file system size in bytes. |
FILESYSTEM_FREE_SIZE | BIGINT | Y | The total file system free size in bytes. |
REMARKS | VARCHAR | Y | User-specified remarks. |
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.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the table, identified by OBJ_ID column. |
DBPG_NAME | VARCHAR | Y | The name of the database partition group in which the table resides. |
TOTAL_ROW_COUNT | BIGINT | Y | Total row count of the table. |
AVG_ROW_COUNT | BIGINT | Y | The average row count from all portions of the table. |
TARGET_LEVEL | BIGINT | Y | The average data size on each database partition, in bytes. |
DATA_SKEW | SMALLINT | Y | The maximum percentage of the ROW_COUNT value deviated from the TARGET_LEVEL, over all portions of the table, for the given table. This is used during data capture and analysis process to be compared against the data skew threshold in the STMG_CURR_THRESHOLD table. |
AVG_ROW_LENGTH | BIGINT | Y | The average row length of the table. If this statistic has been collected, it will be the sum of the average column length of all the columns in this table; when there is no statistical data, this value is calculated by adding the fixed columns' length with the percentage of the variable columns' length. |
COLCOUNT | INTEGER | Y | As defined in SYSCAT.TABLES. |
ESTIMATED_SIZE | BIGINT | Y | As defined in SYSCAT.TABLES. |
NPAGES | INTEGER | Y | As defined in SYSCAT.TABLES. |
FPAGES | INTEGER | Y | As defined in SYSCAT.TABLES. |
OVERFLOW | INTEGER | Y | As defined in SYSCAT.TABLES. |
MAIN_TBSPACE | VARCHAR | Y | As defined in SYSCAT.TABLES. |
INDEX_TBSPACE | VARCHAR | Y | As defined in SYSCAT.TABLES. |
LONG_TBSPACE | VARCHAR | Y | As defined in SYSCAT.TABLES. |
REMARKS | VARCHAR | Y | User-specified remarks. |
TABLE_PARTITIONED | CHAR(1) | N | Specifies whether the table is divided into one or more data partitions. Has value "Y" if table is partitioned and "N" otherwise. |
The STMG_TBPARTITION table contains one row for each detailed entry of table partition storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
PARTITION_NUM (PK) | INTEGER | N | The partition number of the database partition where the table partition resides. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the table partition, identified by OBJ_ID column. |
DBPG_NAME | VARCHAR | Y | The name of the database partition group where the table resides. |
ROWCOUNT | BIGINT | Y | The number of rows in this table partition. |
REMARKS | VARCHAR | Y | User-specified remarks. |
The STMG_INDEX table contains one row for each detailed entry of index storage snapshots.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
COMPLETE_TIMESTAMP | TIMESTAMP | Y | The timestamp of when the data capturing process has completed for the index, identified by OBJ_ID column. |
DBPG_NAME | VARCHAR | Y | The name of the database partition group in which the index resides. |
TB_SCHEMA | VARCHAR | Y | As TABNAME defined in SYSCAT.INDEXES. |
TB_NAME | VARCHAR | Y | As TABSCHEMA defined in SYSCAT.INDEXES. |
COLCOUNT | INTEGER | Y | As defined in SYSCAT.INDEXES. |
ESTIMATED_SIZE | BIGINT | Y | As defined in SYSCAT.INDEXES. |
NLEAF | INTEGER | Y | As defined in SYSCAT.INDEXES. |
NLEVELS | SMALLINT | Y | As defined in SYSCAT.INDEXES. |
FIRSTKEYCARD | BIGINT | Y | As defined in SYSCAT.INDEXES. |
FIRST2KEYCARD | BIGINT | Y | As defined in SYSCAT.INDEXES. |
FIRST3KEYCARD | BIGINT | Y | As defined in SYSCAT.INDEXES. |
FIRST4KEYCARD | BIGINT | Y | As defined in SYSCAT.INDEXES. |
FULLKEYCARD | BIGINT | Y | As defined in SYSCAT.INDEXES. |
CLUSTERRATIO | SMALLINT | Y | As defined in SYSCAT.INDEXES, this is used during data capture and analysis process to compare against the threshold set for the given index. |
CLUSTERFACTOR | BIGINT | Y | As defined in SYSCAT.INDEXES. |
SEQUENTIAL_PAGES | INTEGER | Y | As defined in SYSCAT.INDEXES. |
DENSITY | INTEGER | Y | As defined in SYSCAT.INDEXES. |
REMARKS | VARCHAR | Y | User-specified remarks. |
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.
Column name | Data type | Nullable | Description |
---|---|---|---|
STMG_TIMESTAMP (PK) | TIMESTAMP | N | The timestamp of the storage snapshot. It indicates when the data capturing process started. |
OBJ_ID (PK) | VARCHAR | N | The unique identifier for each storage object under a given storage snapshot timestamp. |
OBJ_NAME (PK) | VARCHAR | N | The name of the storage object. |
OBJ_SCHEMA (PK) | VARCHAR | N | The schema of the storage object. "-" is used when schema is not applicable for the object. |
DBPG_NAME | VARCHAR | Y | The name of the database partition group where the object resides. Null if not applicable. |
TS_NAME | VARCHAR | Y | The name of the table space in which the object resides. Null if not applicable. |
SPACE_WARNING_THRESHOLD | SMALLINT | Y | The space usage warning threshold. Null if not applicable. |
SPACE_ALARM_THRESHOLD | SMALLINT | Y | The space usage alarm threshold. Null if not applicable. |
SPACE_THRESHOLD_EXCEEDED | SMALLINT | Y | The space usage threshold exceeded value. 1 if exceeded; 0 otherwise. Null if not applicable. |
SKEW_WARNING_THRESHOLD | SMALLINT | Y | The data skew warning threshold. Null if not applicable. |
SKEW_ALARM_THRESHOLD | SMALLINT | Y | The data skew alarm threshold. Null if not applicable. |
SKEW_THRESHOLD_EXCEEDED | SMALLINT | Y | The data skew threshold exceeded value. 1 if exceeded; 0 otherwise. Null if not applicable. |
CLUSTER_WARNING_THRESHOLD | SMALLINT | Y | The cluster ratio warning threshold. Null if not applicable. |
CLUSTER_ALARM_THRESHOLD | SMALLINT | Y | The cluster ratio alarm threshold. Null if not applicable. |
CLUSTER_THRESHOLD_EXCEEDED | SMALLINT | Y | The cluster ratio threshold exceeded value. 1 if exceeded; 0 otherwise. Null if not applicable. |