DB2 10.5 for Linux, UNIX, and Windows

ADMIN_GET_TAB_COMPRESS_INFO_V97 table function - Returns compressed information

The ADMIN_GET_TAB_COMPRESS_INFO_V97 table function returns compression information for tables, materialized query tables (MQT) and hierarchy tables.

Note: This table function has been deprecated and replaced by the ADMIN_GET_TAB_COMPRESS_INFO table function and the ADMIN_GET_TAB_DICTIONARY_INFO table function.

Refer to the Table 1 table for a complete list of information that can be returned.

Syntax


>>-ADMIN_GET_TAB_COMPRESS_INFO_V97--(--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

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

Examples

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

  SELECT * 
    FROM TABLE (
      SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('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 ...
--------- ------- -------------- ----------------- ------------- ------------ ...
SIMAP2    STAFF                0                 4 Y             REORG        ...
SIMAP2    STAFF                0                 4 Y             NOT BUILT    ...

2 record(s) selected.

Output from this query (continued):

DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
2009-03-31-12.19.30.000000              13312             5296           35 ...
-                                           0                0            0 ...

Output from this query (continued):

PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
                 38                  38                      27 DATA       
                  0                   0                       0 XML

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

  SELECT * 
    FROM TABLE (
      SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('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   ...
--------- ------- -------------- ----------------- ------------- -------------- ...
SIMAP2    STAFF                0                 4 Y             TABLE FUNCTION ...
SIMAP2    STAFF                0                 4 Y             TABLE FUNCTION ...

2 record(s) selected.

Output from this query (continued):

DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
2009-03-31-12.27.06.000000              13312             5296           35 ...
2009-03-31-12.27.06.000000              13312             9544            8 ...

Output from this query (continued):

PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
                 38                  38                      27 DATA       
                 75                  75                      95 XML

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

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

Output from this query:

TABSCHEMA    TABNAME      OBJECT_TYPE  DICT_BUILDER ...
------------ ---------- -------------- ------------ ...
SIMAP2       ACT          DATA         NOT BUILT    ...
SIMAP2       ACT          XML          NOT BUILT    ...
SIMAP2       ADEFUSR      DATA         INSPECT      ...
SIMAP2       ADEFUSR      XML          NOT BUILT    ...
...
SIMAP2       CUSTOMER     DATA         REORG        ...
SIMAP2       CUSTOMER     XML          REORG        ...
SIMAP2       DEPARTMENT   DATA         NOT BUILT    ...
SIMAP2       DEPARTMENT   XML          NOT BUILT    ...
...
SIMAP2       STAFF        DATA         REORG        ...
SIMAP2       STAFF        XML          NOT BUILT    ...
SIMAP2       SUPPLIERS    DATA         TABLE GROWTH ...
SIMAP2       SUPPLIERS    XML          NOT BUILT    ...

44 record(s) selected.

Output from this query (continued):

TOTAL_DICT_SIZE DBPARTITIONNUM DATA_PARTITION_ID
--------------- -------------- -----------------
              0              0                 0
              0              0                 0
           1890              0                 0
              0              0                 0
...
           6968              0                 1
          24256              0                 1
              0              1                 0
              0              1                 0
...
          18608              0                 4
              0              0                 4
           6960              0                 2
              0              0                 2

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

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

Output from this query:

TABSCHEMA TABNAME   DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER ...
--------- --------- -------------- ----------------- ------------- ------------ ...
SIMAP2    ACT                    0                 0 N             NOT BUILT    ...
SIMAP2    ACT                    0                 0 N             NOT BUILT    ...
SIMAP2    ADEFUSR                0                 0 N             INSPECT      ...
SIMAP2    ADEFUSR                0                 0 N             NOT BUILT    ...
...
SIMAP2    CUSTOMER               0                 1 Y             REORG        ...
SIMAP2    CUSTOMER               0                 1 Y             REORG        ...
...
SIMAP2    STAFF                  0                 4 Y             REORG        ...
SIMAP2    STAFF                  0                 4 Y             NOT BUILT    ...
SIMAP2    SUPPLIERS              0                 2 N             NOT BUILT    ...
SIMAP2    SUPPLIERS              0                 2 N             NOT BUILT    ...

44 record(s) selected.

Output from this query (continued):

DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
-                                           0                0            0 ...
-                                           0                0            0 ...
2009-03-31-12.11.02.000000                290             1890           22 ...
-                                           0                0            0 ...
...
2009-03-31-11.08.18.000000               3968             3000            6 ...
2009-03-31-11.08.18.000000              13312            10944            6 ...
...
2009-03-31-12.19.30.000000              13312             5296           35 ...
-                                           0                0            0 ...
-                                           0                0            0 ...
-                                           0                0            0 ...

Output from this query (continued):

PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
                  0                   0                       0 DATA
                  0                   0                       0 XML
                 20                  25                      21 DATA
                  0                   0                       0 XML
...
                 70                  70                      31 DATA
                 66                  66                     235 XML
...
                 38                  38                      27 DATA
                  0                   0                       0 XML
                  0                   0                       0 DATA
                  0                   0                       0 XML

Example 5: View a report of the dictionary information of DATA objects of tables in the SIMAP2 schema.

  SELECT * FROM TABLE (
      SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2','','REPORT'))
    WHERE OBJECT_TYPE='DATA'

Output from this query:

TABSCHEMA TABNAME   DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER ...
--------- --------- -------------- ----------------- ------------- ------------ ...
SIMAP2    ACT                    0                 0 N             NOT BUILT    ...
SIMAP2    ADEFUSR                0                 0 N             INSPECT      ...
...
SIMAP2    CUSTOMER               0                 1 Y             REORG        ...
SIMAP2    DEPARTMENT             1                 0 N             NOT BUILT    ...
...
SIMAP2    STAFF                  0                 4 Y             REORG        ...
SIMAP2    SUPPLIERS              0                 2 N             NOT BUILT    ...

  22 record(s) selected.

Output from this query (continued):

DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED...
-------------------------- ------------------ ---------------- ------------ ...
-                                           0                0            0 ...
2009-03-31-12.11.02.000000                290             1890           22 ...
...
2009-03-31-11.08.18.000000               3968             3000            6 ...
-                                           0                0            0 ...
...
2009-03-31-12.19.30.000000              13312             5296           35 ...
-                                           0                0            0 ...

Output from this query (continued):

PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
                  0                   0                       0 DATA       
                 20                  25                      21 DATA       

                 70                  70                      31 DATA       
                  0                   0                       0 DATA       

                 38                  38                      27 DATA       
                  0                   0                       0 DATA

Example 6: View a report of the dictionary information of XML objects of the CUSTOMER table in the SIMAP2 schema.

  SELECT * FROM TABLE (
      SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2', 'CUSTOMER',  'REPORT'))
    WHERE OBJECT_TYPE='XML'

Output from this query:

TABSCHEMA TABNAME   DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER ...
--------- --------- -------------- ----------------- ------------- ------------ ...
SIMAP2    CUSTOMER               0                 1 Y             REORG        ...

Output from this query (continued):

DICT_BUILD_TIMESTAMP       COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
2009-03-31-11.08.18.000000              13312            10944            6 ...

Output from this query (continued):

PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
                 66                  66                     235 XML

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_V97 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 ADMIN_GET_TAB_COMPRESS_INFO_V97
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.
OBJECT_TYPE	VARCHAR(4)	objtype - Object type monitor element