DB2 Version 10.1 for Linux, UNIX, and Windows

ADMINTABCOMPRESSINFO administrative view and ADMIN_GET_TAB_COMPRESS_INFO table function (deprecated) - returns compressed information

The ADMINTABCOMPRESSINFO administrative view and the ADMIN_GET_TAB_COMPRESS_INFO table function return compression information for tables, materialized query tables (MQT) and hierarchy tables.

Note: This administrative view and the associated table function has been deprecated and replaced by the following functions:

ADMIN_GET_TAB_COMPRESS_INFO table function - estimate compression savings. Note that this replacement function has the same name as the deprecated function, but takes fewer arguments than the deprecated function.
ADMIN_GET_TAB_DICTIONARY_INFO table function - report properties of existing table dictionaries.

This topic consists of the following sections:

ADMINTABCOMPRESSINFO administrative view
ADMIN_GET_TAB_COMPRESS_INFO table function

ADMINTABCOMPRESSINFO administrative view

The ADMINTABCOMPRESSINFO administrative view returns compression information for tables, materialized query tables (MQT) and hierarchy tables only. These table types are reported as T for table, S for materialized query tables and H for hierarchy tables in the SYSCAT.TABLES catalog view. The information is returned at both the data partition level and the database partition level for a table.

The schema is SYSIBMADM.

Refer to the Information returned by ADMINTABCOMPRESSINFO administrative view and ADMIN_GET_TAB_COMPRESS_INFO table for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the ADMINTABCOMPRESSINFO administrative view
CONTROL privilege on the ADMINTABCOMPRESSINFO administrative view
DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

EXECUTE privilege on the ADMIN_GET_TAB_COMPRESS_INFO table function
DATAACCESS authority

Default PUBLIC privilege

None

Examples

Example 1: Retrieve all compression information for all tables

  SELECT * FROM SYSIBMADM.ADMINTABCOMPRESSINFO

The following is an example of output from this query:

TABSCHEMA  TABNAME      DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR ...
---------- ------------ -------------- ----------------- ------------- ...
SYSIBM     SYSTABLES                 0                 0 N             ...
SYSIBM     SYSCOLUMNS                0                 0 N             ...
...
SIMAP2     STAFF                     0                 0 Y             ...
SIMAP2     PARTTAB                   0                 0 Y             ...
...

  156 record(s) selected.

Output from this query (continued):

... DICT_BUILDER DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ...
... ------------ -------------------------- ------------------ ---------------- ...
... NOT BUILT    -                                           0                0 ...
... NOT BUILT    -                                           0                0 ...
... ...                                                                         
... REORG        2006-08-27-19.07.36.000000              13312             5312 ...
... REORG        2006-08-27-22.07.17.000000               5760             4248 ...
... ...

Output from this query (continued):

... ROWS_SAMPLED PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH
... ------------ ------------------- ------------------- -----------------------
...            0                   0                   0                       0
...            0                   0                   0                       0
...
...           35                  65                  84                     100
...           45                  76                  79                      98
...

Example 2: Determine the dictionary building action and time of dictionary creation for all tables.

SELECT TABSCHEMA, TABNAME, DBPARTITIONNUM, DATA_PARTITION_ID, DICT_BUILDER, DICT_BUILD_TIMESTAMP 
FROM SYSIBMADM.ADMINTABCOMPRESSINFO

The following is an example of output from this query:

TABSCHEMA TABNAME    DBPARTITIONNUM DATA_PARTITION_ID DICT_BUILDER DICT_BUILD_TIMESTAMP
--------- ---------- -------------- ----------------- ------------ --------------------------
SYSIBM    SYSTABLES               0                 0 NOT BUILT    -
SYSIBM    SYSCOLUMNS              0                 0 NOT BUILT    -
...
SIMAP2    STAFF                   0                 0 REORG        2006-08-27-19.07.36.000000
SIMAP2    SALES                   0                 0 NOT BUILT    -
SIMAP2    CATALOG                 0                 0 NOT BUILT    -
...

  156 record(s) selected.

ADMIN_GET_TAB_COMPRESS_INFO table function

The ADMIN_GET_TAB_COMPRESS_INFO table function returns the same information as the ADMINTABCOMPRESSINFO administrative view, but allows you to specify a schema, table name and an execution mode.

Refer to the Information returned by ADMINTABCOMPRESSINFO administrative view and ADMIN_GET_TAB_COMPRESS_INFO table for a complete list of information that can be returned.

Syntax


>>-ADMIN_GET_TAB_COMPRESS_INFO--(--tabschema--,--tabname--,--execmode--)-><

The schema is SYSPROC.

Table function parameters

tabschema

An input argument of type VARCHAR(128) that specifies a schema name.

tabname

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

execmode

An input argument of type VARCHAR(30) that specifies the execution mode. The execution mode can be one of the following modes:

'REPORT'-- Reports compression information as of last generation. This is the default value.
'ESTIMATE'-- Generates new compression information based on the current table.

Authorization

EXECUTE privilege on the ADMIN_GET_TAB_COMPRESS_INFO function.

Default PUBLIC privilege

None

Examples

Example 1: Retrieve existing compression information for table SIMAP2.STAFF

SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SIMAP2', 'STAFF', 'REPORT')) 
AS T

The following is an example from output of this query:

TABSCHEMA      TABNAME     DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER     DICT_BUILD_TIMESTAMP      
----------...- -------...- -------------- ----------------- ------------- ------------...- --------------------------
SIMAP2         STAFF                    0                 0 Y             REORG            2006-08-27-19.07.36.000000

  1 record(s) selected.

Output from this query (continued):

COMPRESS_DICT_SIZE   EXPAND_DICT_SIZE     ROWS_SAMPLED PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH
-------------------- -------------------- ------------ ------------------- ------------------- -----------------------
               13312                 5312           35                  65                  84                     100

Example 2: Retrieve estimated compression information for table SIMAP2.STAFF as of now.

SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SIMAP2', 'STAFF', 'ESTIMATE')) 
AS T

The following is an example from output of this query:

TABSCHEMA      TABNAME     DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER         DICT_BUILD_TIMESTAMP      
----------...- -------...- -------------- ----------------- ------------- ----------------...- --------------------------
SIMAP2         STAFF                    0                 0 Y             TABLE FUNCTION       2006-08-28-19.18.13.000000

  1 record(s) selected.

Output from this query (continued):

COMPRESS_DICT_SIZE   EXPAND_DICT_SIZE     ROWS_SAMPLED PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH
-------------------- -------------------- ------------ ------------------- ------------------- -----------------------
               13508                 6314           68                  72                  89                      98

Example 3: Determine the total dictionary size for all tables in the schema SIMAP2

  SELECT TABSCHEMA, TABNAME, DICT_BUILDER, 
   (COMPRESS_DICT_SIZE+EXPAND_DICT_SIZE) AS TOTAL_DICT_SIZE, 
   DBPARTITIONNUM, DATA_PARTITION_ID 
  FROM TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SIMAP2', '', 'REPORT')) AS T

Output from this query:

TABSCHEMA       TABNAME         DICT_BUILDER                   TOTAL_DICT_SIZE      DBPARTITIONNUM DATA_PARTITION_ID
--------------- --------------- ------------------------------ -------------------- -------------- -----------------
SIMAP2          ACT             NOT BUILT                                         0              0                 0
SIMAP2          ADEFUSR         NOT BUILT                                         0              0                 0
...
SIMAP2          INVENTORY       NOT BUILT                                         0              0                 0
SIMAP2          ORG             NOT BUILT                                         0              0                 0
SIMAP2          PARTTAB         REORG                                         10008              0                 0
SIMAP2          PARTTAB         REORG                                          5464              0                 1
SIMAP2          PARTTAB         REORG                                          8456              0                 2
SIMAP2          PARTTAB         REORG                                          6960              0                 3
SIMAP2          PARTTAB         REORG                                          7136              0                 4
...
SIMAP2          STAFF           REORG                                         18624              0                 0
SIMAP2          SUPPLIERS       NOT BUILT                                         0              0                 0
SIMAP2          TESTTABLE       NOT BUILT                                         0              0                 0

  28 record(s) selected.

Example 4: View a report of the dictionary information of tables in the SIMAP2 schema.

SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SIMAP2', '', 'REPORT')) 
AS T

Output from this query:

TABSCHEMA      TABNAME     DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER         DICT_BUILD_TIMESTAMP      
----------...- -------...- -------------- ----------------- ------------- ----------------...- --------------------------
SIMAP2         T1                       0                 0 Y             NOT BUILT            -                         
SIMAP2         T2                       0                 0 N             REORG                2007-02-03-17.35.28.000000
SIMAP2         T3                       0                 0 Y             INSPECT              2007-02-03-17.35.44.000000
SIMAP2         T4                       0                 0 N             NOT BUILT            -                         

  4 record(s) selected.

Output from this query (continued):

COMPRESS_DICT_SIZE   EXPAND_DICT_SIZE     ROWS_SAMPLED PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH
-------------------- -------------------- ------------ ------------------- ------------------- -----------------------
                   0                    0            0                   0                   0                       0
                1280                 2562            -                   -                   -                       -
                1340                 2232            -                   -                   -                       -
                   0                    0            0                   0                   0                       0

Usage notes

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), a materialized query table name (type S) or a hierarchy table name (type H), 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 queried table is a non-XML table, there will be a row returned for the XML storage object (XDA).

Information returned

Table 1. Information returned by ADMINTABCOMPRESSINFO administrative view and 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
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier monitor element
COMPRESS_ATTR	CHAR(1)	The state of the COMPRESS attribute on the table which can be one of the following values: 'Y' = Row compression is set to yes 'N' = Row compression is set to no
DICT_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 'TABLE FUNCTION' = built by table function for the 'ESTIMATE' option
DICT_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.
COMPRESS_DICT_SIZE	BIGINT	Size of compression dictionary measured in bytes.
EXPAND_DICT_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.
ROWS_SAMPLED	BIGINT	Number of records that contributed to building the dictionary. Migrated tables with compression dictionaries will return NULL in this column.
PAGES_SAVED_PERCENT	SMALLINT	Percentage of pages saved from compression. This information is based on the record data in the sample buffer only. Migrated tables with compression dictionaries will return NULL in this column.
BYTES_SAVED_PERCENT	SMALLINT	Percentage of bytes saved from compression. This information is based on the record data in the sample buffer only. Migrated tables with compression dictionaries will return NULL in this column.
AVG_COMPRESS_REC_LENGTH	SMALLINT	Average compressed record length of the records contributing to building the dictionary. Migrated tables with compression dictionaries will return NULL in this column.