The ADMIN_GET_TAB_DICTIONARY_INFO table function reports the dictionary information of classic row compression for a specified schema and table when the table dictionary was created.
This is a direct replacement for the 'REPORT' 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) | objtype - Object type monitor element |
ROWCOMPMODE | CHAR(1) | The current row compression mode for the object.
The returned metric can be one of the following values:
|
BUILDER | VARCHAR(30) | Code path taken to build the dictionary, which
can be one of the following values:
|
BUILD_TIMESTAMP | TIMESTAMP | Timestamp of when the dictionary was built. Timestamp granularity is to the second. If no dictionary is available, then the timestamp is NULL. |
SIZE | BIGINT | Size of the expansion dictionary measured in bytes. If a historical dictionary exists, this value is the sum of the current and historical dictionary sizes. |
HISTORICAL_DICTIONARY | CHAR(1) | Indicates the presence of a historical dictionary.
The returned metric can be one of the following values:
|
ROWS_SAMPLED | BIGINT | Number of records that contributed to building the dictionary. |
PCTPAGESSAVED | SMALLINT | Percentage of pages saved from compression. This information is a projection, based on the records contributing to building the dictionary. |
AVGCOMPRESSEDROWSIZE | SMALLINT | Average compressed record length of the records contributing to building the dictionary. |
SELECT SUBSTR(TABSCHEMA, 1, 10) AS TABSCHEMA, SUBSTR(TABNAME, 1, 10) AS TABNAME,
DBPARTITIONNUM, DATAPARTITIONID, OBJECT_TYPE, ROWCOMPMODE, BUILDER,
BUILD_TIMESTAMP, SIZE, HISTORICAL_DICTIONARY, ROWS_SAMPLED,
PCTPAGESSAVED, AVGCOMPRESSEDROWSIZE
FROM TABLE( SYSPROC.ADMIN_GET_TAB_DICTIONARY_INFO( 'PAGECOMP', 'ADMIN_VIEW' ))
TABSCHEMA TABNAME DBPARTITIONNUM DATAPARTITIONID OBJECT_TYPE ROWCOMPMODE ...
---------- ---------- -------------- --------------- ----------- ----------- ...
PAGECOMP ADMIN_VIEW 0 0 DATA S ...
BUILDER BUILD_TIMESTAMP SIZE
------------------------------ -------------------------- -------------------- ...
REORG 2010-09-03-01.10.33.000000 52736 ...
HISTORICAL_DICTIONARY ROWS_SAMPLED PCTPAGESSAVED AVGCOMPRESSEDROWSIZE
--------------------- ------------ ------------- --------------------
N 300000 80 38
1 record(s) selected.