The ADMINTABCOMPRESSINFO administrative view and the ADMIN_GET_TAB_COMPRESS_INFO_V97 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_V97 table function metadata table for a complete list of information that can be returned.
SELECT * FROM SYSIBMADM.ADMINTABCOMPRESSINFO
TABSCHEMA TABNAME DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER ...
--------- --------- -------------- ----------------- ------------- ------------ ...
SYSIBM SYSTABLES 0 0 N NOT BUILT ...
SYSIBM SYSTABLES 0 0 N NOT BUILT ...
...
SIMAP2 STAFF 0 4 Y REORG ...
SIMAP2 STAFF 0 4 Y REORG ...
...
156 record(s) selected.
DICT_BUILD_TIMESTAMP COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
------------------------- ------------------- ---------------- ------------ ...
- 0 0 0 ...
- 0 0 0 ...
...
2009-03-31-11.08.18.000000 3968 3000 6 ...
2009-03-31-11.08.18.000000 13312 10944 6 ...
...
PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
0 0 0 DATA
0 0 0 XML
...
70 70 31 DATA
66 66 235 XML
...
SELECT TABSCHEMA, TABNAME, DBPARTITIONNUM, DATA_PARTITION_ID,
OBJECT_TYPE, DICT_BUILDER, DICT_BUILD_TIMESTAMP
FROM SYSIBMADM.ADMINTABCOMPRESSINFO
TABSCHEMA TABNAME DBPARTITIONNUM DATA_PARTITION_ID ...
--------------- --------------- -------------- ----------------- ...
SYSIBM SYSTABLES 0 0 ...
SYSIBM SYSTABLES 0 0 ...
...
SIMAP2 STAFF 0 4 ...
SIMAP2 STAFF 0 4 ...
SYSTOOLS HMON_COLLECTION 0 0 ...
SYSTOOLS HMON_COLLECTION 0 0 ...
156 record(s) selected.
OBJECT_TYPE DICT_BUILDER DICT_BUILD_TIMESTAMP
----------- --------------------------- --------------------------
DATA NOT BUILT -
XML NOT BUILT -
...
DATA REORG 2009-03-31-11.08.18.000000
XML REORG 2009-03-31-11.08.18.000000
DATA REDISTRIBUTE 2009-03-29-06.44.32.000000
XML REDISTRIBUTE 2009-03-29-06.44.32.000000
The ADMIN_GET_TAB_COMPRESS_INFO_V97 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_V97 table function metadata table for a complete list of information that can be returned.
This function returns two rows for every table. One row has a value of DATA in the OBJECT_TYPE column, and the other row has a value of XML for that column. The row marked as DATA is equivalent to the return value from the deprecated ADMINTABCOMPRESSINFO view and ADMIN_GET_TAB_COMPRESS_INFO table function - returns compressed information table function. The row marked as XML describes the XML compression dictionary.
>>-ADMIN_GET_TAB_COMPRESS_INFO_V97--(--tabschema--,--tabname--,--execmode--)-><
The schema is SYSPROC.
EXECUTE privilege on the ADMIN_GET_TAB_COMPRESS_INFO_V97 function.
SELECT *
FROM TABLE (
SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2', 'STAFF', 'REPORT'))
AS T
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.
DICT_BUILD_TIMESTAMP COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
2009-03-31-12.19.30.000000 13312 5296 35 ...
- 0 0 0 ...
PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
38 38 27 DATA
0 0 0 XML
SELECT *
FROM TABLE (
SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2', 'STAFF', 'ESTIMATE'))
AS T
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.
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 ...
PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
38 38 27 DATA
75 75 95 XML
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
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.
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
SELECT * FROM TABLE (
SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2', '', 'REPORT'))
AS T
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.
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
SELECT * FROM TABLE (
SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2','','REPORT'))
WHERE OBJECT_TYPE='DATA'
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.
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 ...
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
SELECT * FROM TABLE (
SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO_V97('SIMAP2', 'CUSTOMER', 'REPORT'))
WHERE OBJECT_TYPE='XML'
TABSCHEMA TABNAME DBPARTITIONNUM DATA_PARTITION_ID COMPRESS_ATTR DICT_BUILDER ...
--------- --------- -------------- ----------------- ------------- ------------ ...
SIMAP2 CUSTOMER 0 1 Y REORG ...
DICT_BUILD_TIMESTAMP COMPRESS_DICT_SIZE EXPAND_DICT_SIZE ROWS_SAMPLED ...
-------------------------- ------------------ ---------------- ------------ ...
2009-03-31-11.08.18.000000 13312 10944 6 ...
PAGES_SAVED_PERCENT BYTES_SAVED_PERCENT AVG_COMPRESS_REC_LENGTH OBJECT_TYPE
------------------- ------------------- ----------------------- -----------
66 66 235 XML
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. |
OBJECT_TYPE | VARCHAR(4) | The type of the object. Depending on the type,
this row contains values pertaining to the specified object. Output
can be one of the following values
|