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

Authorization

  • EXECUTE privilege on the CAPTURE_STORAGEMGMT_INFO procedure.
  • EXECUTE privilege on the SYSPROC.DB_PARTITIONS, SYSPROC.MON_GET_TABLESPACE, SYSPROC.MON_GET_CONTAINER, and SYSPROC.WLM_SET_CONN_ENV routines.
  • SELECT privilege on SYSCAT.TABLES, SYSCAT.TABLESPACES, SYSCAT.NODEGROUPDEF, SYSCAT.DATABASEPARTITIONS, SYSCAT.DATAPARTITIONEXPRESSION, SYSCAT.INDEXES, and SYSCAT.COLUMNS.

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Syntax

Read syntax diagramSkip visual syntax diagramCAPTURE_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.

Usage notes

  1. 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 preceding stored procedure.

    drop_storagemgmt_tables(0 or 1) where '0' indicates 'stop' and '1' indicates 'proceed' on encountering an error.)

  2. 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

  3. The following command should be used to populate the storage management tables:

    Run 'capture_storagemgmt_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>)"

  4. 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"