DB2 Version 9.7 for Linux, UNIX, and Windows

CAPTURE_STORAGEMGMT_INFO procedure - Retrieve storage-related information for a given root object

The CAPTURE_STORAGEMGMT_INFO procedure attempts to collect the storage-related information for the given root object, as well as the storage objects defined within its scope. All the storage objects are specified in the SYSTOOLS.STMG_OBJECT_TYPE table.

Table 1. STMG_OBJECT_TYPE table
Column name	Data type	Nullable	Description
OBJ_TYPE	INTEGER	N	Integer value corresponds to a type of storage object 0 - Database 1 - Database Partition Group 2 - Table Space 3 - Table Space Container 4 - Table 5 - Index
TYPE_NAME	VARCHAR	N	Descriptive name of the storage object type STMG_DATABASE STMG_DBPGROUP STMG_TABLESPACE STMG_CONTAINER STMG_TABLE STMG_INDEX

Syntax


>>-CAPTURE_STORAGEMGMT_INFO--(--in_rootType--,------------------>

>--in_rootSchema--,--in_rootName -)----------------------------><

The schema is SYSPROC.

Procedure parameters

in_rootType

An input argument of type SMALLINT. The valid option types are:

0 - Database
1 - Database Partition Group
2 - Table Space
4 - Table
5 - Index

The input argument cannot be null. If a null value is specified, an SQL0443 error with SQLSTATE 38553, and token DBA7617 is returned.

in_rootSchema

An input argument of type VARCHAR (128) that specifies the schema name of the storage snapshot root object. A NULL value can be specified if the in_rootType is a database, a database partition group, or a table space.

in_rootName

An input argument of type VARCHAR (128) that specifies the name of the root object. The input argument cannot be null. If a null value is specified, an SQL0443 error with SQLSTATE 38553, and token DBA7617 is returned.

Authorization

EXECUTE privilege on the CAPTURE_STORAGEMGMT_INFO procedure.
EXECUTE privilege on the SYSPROC.DB_PARTITIONS, SYSPROC.SNAP_GET_CONTAINER, SYSPROC.SNAPSHOT_CNTRFS table functions.
SELECT privilege on SYSCAT.TABLES, SYSCAT.TABLESPACES, SYSCAT.NODEGROUPDEF, SYSCAT.DATABASEPARTITIONS, SYSCAT.DATAPARTITIONEXPRESSION, SYSCAT.INDEXES, and SYSCAT.COLUMNS.

Usage Notes:

The following stored procedure should be used to create storage management tables:
create_storagemgmt_tables(TABLESPACE_NAME) where 'TABLESPACE' is the name of the table space, on which storage management tables would be created.

(In case of a problem with the existing storage management tables, it can be dropped using the following stored procedure and can be re-created using the previously described stored procedure.

drop_storagemgmt_tables(0 or 1) where '0' or '1 'indicates 'stop' or 'proceed' on encountering an error.)
The following command should be used to run statistics for the storage object for which details have to be obtained:
RUNSTATS ON TABLE (TABLESCHEMA.TABLENAME) ON KEY COLUMNS AND INDEXES ALL
The following command should be used to populate the storage management tables:
Run 'capture_storagemgnt_info()' stored procedure to populate the storage management tables. Sometimes it might be necessary to run the CAPTURE_STORAGEMGMT_INFO procedure twice. The first time you run it, use the CAPTURE_STORAGEMGMT_INFO procedure to populate the storage tables with table space details. For example:

db2 "call capture_storagemgmt_info(0,<SCHEMA_NAME>,<DATABASE_NAME>)"

The second time, use the CAPTURE_STORAGEMGMT_INFO procedure to add details about the storage of the actual object to the storage table. For example, the following example adds details for an object of type index (the in_rootType argument is set to 5):

db2 "call capture_storagemgmt_info(5,<SCHEMA_NAME>,<SCHEMA_NAME.INDEX_NAME>)"
Run the select query on the required storage management table to see the details of the storage object, for example: as follows in case of INDEX object :
db2 "SELECT * FROM SYSTOOLS.STMG_INDEX"