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.
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 ADMINTABCOMPRESSINFO administrative view and ADMIN_GET_TAB_COMPRESS_INFO table function metadata table for a complete list of information that can be returned.
Example 1: Retrieve all compression information for all tables
SELECT * FROM SYSIBMADM.ADMINTABCOMPRESSINFO
The following example is a sample output from this query:
TABSCHEMA TABNAME DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER DICT_BUILD_TIMESTAMP
---------...- -----------...- -------------- ----------------- ------------- -------------...- --------------------------
SYSIBM SYSTABLES 0 0 N NOT BUILT -
SYSIBM SYSCOLUMNS 0 0 N NOT BUILT -
...
SIMAP2 STAFF 0 0 Y REORG 2006-08-27-19.07.36.000000
SIMAP2 PARTTAB 0 0 Y REORG 2006-08-27-22.07.17.000000
...
156 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
0 0 0 0 0 0
...
13312 5312 35 65 84 100
5760 4248 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 example is a sample 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.
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 ADMINTABCOMPRESSINFO administrative view and ADMIN_GET_TAB_COMPRESS_INFO table function metadata table for a complete list of information that can be returned.
>>-ADMIN_GET_TAB_COMPRESS_INFO--(--tabschema--,--tabname--,--execmode--)-><
The schema is SYSPROC.
EXECUTE privilege on the ADMIN_GET_TAB_COMPRESS_INFO function.
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 example is a sample 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 example is a sample 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
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 | Database partition number |
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:
|
DICT_BUILDER | VARCHAR(30) | Code path taken to build the dictionary which
can be one of the following values:
|
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 | INTEGER | 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. |