ADMIN_GET_TAB_DICTIONARY_INFO table function - report properties of existing table dictionaries

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.

Note:

In Db2® 11.1 Mod Pack 4 and Fix Pack 4 and later releases, the table function also reports dictionary information for column-organized tables.

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.

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

Usage notes

Up to MP4FP4 the table function returns 0 rows if you specify a column-organized table. Since MP4FP4, ADMIN_GET_TAB_DICTIONARY_INFO table function is also applicable to column-organized tables, but only OBJECT_TYPE, BUILDER, ROWS_SAMPLED, BUILD_TIMESTAMP, SIZE are filled.
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.
If the specified table has one or more XML columns, the ADMIN_GET_TAB_DICTIONARY_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'.
When the ADMIN_GET_TAB_DICTIONARY_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.

Information returned

Table 1. Information returned by ADMIN_GET_TAB_DICTIONARY_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 COL
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
BUILDER	VARCHAR(30)	Code path taken to build the dictionary, which can be one of the following values: 'INSPECT' = INSPECT ROWCOMPESTIMATE 'LOAD' = LOAD INSERT/REPLACE 'NOT BUILT' = no dictionary available 'REDISTRIBUTE' = REDISTRIBUTE 'REORG' = REORG RESETDICTIONARY 'TABLE GROWTH' = INSERT
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: 'N' = No historical dictionary exists 'Y' = Historical dictionary exists
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.

Examples

Example 1. View a report of the dictionary information of the ADMIN_VIEW table in the PAGECOMP schema.

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' ))

Output from this query:

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.