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® for Linux, UNIX, and Windows.
None
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:
|
ROWCOMPMODE | CHAR(1) | The current row compression mode for the object.
The returned metric can be one of the following values:
|
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. |
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'))
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.