IBM Db2 11.1 Certification Guide

Getting information about the compression dictionary

It is a good idea to monitor compression effectiveness for tables with high insert, update, and delete rates. Tables that have little to no data change or completely reloaded data do not to be checked.

The following DML can be used to evaluate the compression dictionary for a table:

SELECT TRIM(TABSCHEMA) AS SCHEMA, TRIM(TABNAME) AS TAB, DATAPARTITIONID AS PART, OBJECT_TYPE AS OTYP, ROWCOMPMODE AS COMPM, BUILDER, BUILD_TIMESTAMP AS BDT, SIZE, ROWS_SAMPLED AS SAMPSIZE, PCTPAGESSAVED AS PPS, AVGCOMPRESSEDROWSIZE AS ACRS 
FROM TABLE ( 
  "SYSPROC"."ADMIN_GET_TAB_DICTIONARY_INFO" ( 
   CAST ('DEMO' AS VARCHAR (128)), 
   CAST ('CUSTOMER' AS VARCHAR (128)))) AS UDF 
FOR FETCH ONLY;

The CUSTOMER table, as ...