How do you get index space usage by a table? DB2 provides a Table function ADMIN_GET_TAB_INFO to retrieve information about a table.
For example – To find index space used by a non-partition table’s indexes, you can use following SQL:
SELECT index_object_p_size as index_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'ADMIN', 'ORDERS' )) AS T
In above SQL, the schema name is ADMIN and table name is ORDERS.
INDEX_OBJECT_P_SIZE is the physical disk space used in KB.
Please note that this value is reported only for non-partition tables. For partitioned tables this value will be 0.
Rules for specifying Schema and Table Name:
- If both the schema and table names are specified, information is returned for that specific table only.
- If the schema name is specified but table name is empty (“) or NULL, information is returned for all tables in the given schema.
- If the schema name is empty (“) or NULL and table name is specified, an error is returned. To retrieve information for a specific table, the table must be identified by both schema and table name.
- If both schema and table names are empty (“) or NULL, information is returned for all the tables.
To get total physical space used by table ADMIN.ORDERS, you will use following SQL.
SELECT (data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size) as TOTAL_PHYSICAL_SIZE FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'ADMIN', 'ORDERS' )) AS T
To get logical space used by ADMIN.ORDERS table, you can use following SQL or same SQL will also tell you the space required if the table is to be moved to another table space, where the new table space has the same page size and extent size as the original table space:
SELECT (data_object_l_size + index_object_l_size + long_object_l_size + lob_object_l_size + xml_object_l_size) as total_l_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'ADMIN', 'ORDERS' )) AS T