ADMIN_GET_TAB_COMPRESS_INFO table function - estimate compression savings

The ADMIN_GET_TAB_COMPRESS_INFO table function estimates the compression savings that can be gained for the table, assuming a REORG with RESETDICTIONARY option will be performed.

This table function provides a direct replacement for the 'ESTIMATE' mode provided by the deprecated ADMIN_GET_TAB_COMPRESS_INFO table function in previous versions of Db2®.

Syntax

The schema is SYSPROC.

Table function parameters

tabschema: An input argument of type VARCHAR(128) that specifies the schema name.
tabname: An input argument of type VARCHAR(128) that specifies the table name, a materialized query table name or a hierarchy table name.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

Table 1. Information returned for ADMIN_GET_TAB_COMPRESS_INFO
Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name monitor element
TABNAME	VARCHAR(128)	table_name - Table name monitor element
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
DATAPARTITIONID	INTEGER	Data partition number.
OBJECT_TYPE	VARCHAR(4)	Type of object. One of: DATA XML
ROWCOMPMODE	CHAR(1)	The current row compression mode for the object. The returned metric can be one of the following values: 'S' if Classic Row Compression is enabled 'A' if Adaptive Row Compression is enabled Blank if no row compression is enabled
PCTPAGESSAVED_CURRENT	SMALLINT	Current percentage of pages saved from row compression.
AVGROWSIZE_CURRENT	SMALLINT	Current average record length.
PCTPAGESSAVED_STATIC	SMALLINT	Estimated percentage of pages saved from Classic Row Compression.
AVGROWSIZE_STATIC	SMALLINT	Estimated average record length from Classic Row Compression.
PCTPAGESSAVED_ADAPTIVE	SMALLINT	Estimated percentage of pages saved from Adaptive Row Compression.
AVGROWSIZE_ADAPTIVE	SMALLINT	Estimated average record length from Adaptive Row Compression.

Usage notes

The ADMIN_GET_TAB_COMPRESS_INFO table function is specific to row compression and is not applicable to column-organized tables. The table function returns 0 rows if you specify a column-organized table.
If both the tabschema and tabname are specified, information is returned for that specific table only.
If the tabschema is specified but tabname is empty (") or NULL, information is returned for all tables in the given schema.
If the tabschema is empty (") or NULL and tabname 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 tabschema and tabname are empty (") or NULL, information is returned for all tables.
If tabschema or tabname do not exist, or tabname does not correspond to a table name (type T) or a materialized query table name (type S), an empty result set is returned.
When the ADMIN_GET_TAB_COMPRESS_INFO table function is retrieving data for a given table, it will acquire a shared lock on the corresponding row of SYSTABLES to ensure consistency of the data that is returned (for example, to ensure that the table is not altered while information is being retrieved for it). The lock will only be held for as long as it takes to retrieve the compression information for the table, and not for the duration of the table function call.
If the specified table has one or more XML columns, the ADMIN_GET_TAB_COMPRESS_INFO table function will return two rows per partition. One row with OBJECT_TYPE returning 'DATA' and another row with OBJECT_TYPE returning 'XML'. If the specified table does not have any XML columns, then only one row per partition will be returned with OBJECT_TYPE of 'DATA'.
For XML object types, the estimates returned for PCTPAGESSAVED_ADAPTIVE and PCTPAGESSAVED_STATIC are identical as adaptive compression only applies to the data portion of the table.

Examples

Example 1: View the current compression results and estimate report of both classic row compression and adaptive compression information of the TABLE1 table in the SCHEMA1 schema.

SELECT SUBSTR(TABSCHEMA, 1, 10) AS TABSCHEMA, SUBSTR(TABNAME, 1, 10) AS TABNAME, 
       DBPARTITIONNUM, DATAPARTITIONID, OBJECT_TYPE, ROWCOMPMODE, 
       PCTPAGESSAVED_CURRENT, AVGROWSIZE_CURRENT, 
       PCTPAGESSAVED_STATIC, AVGROWSIZE_STATIC, 
       PCTPAGESSAVED_ADAPTIVE, AVGROWSIZE_ADAPTIVE 
FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SCHEMA1', 'TABLE1'))

Output from this query:

TABSCHEMA  TABNAME    DBPARTITIONNUM DATAPARTITIONID OBJECT_TYPE ROWCOMPMODE ...
---------- ---------- -------------- --------------- ----------- ----------- ...
SCHEMA1    TABLE1                  0               0 DATA        A           ...
SCHEMA1    TABLE1                  0               0 XML         S           ...

PCTPAGESSAVED_CURRENT AVGROWSIZE_CURRENT PCTPAGESSAVED_STATIC AVGROWSIZE_STATIC ...
--------------------- ------------------ -------------------- ----------------- ...
                   60                 40                   68                34 ...
                   58                255                   62               198 ...

PCTPAGESSAVED_ADAPTIVE AVGROWSIZE_ADAPTIVE 
---------------------- ------------------- 
                    70                  30 
                    62                 198 

  2 record(s) selected.