Personal tools
You are here: Home → DB2 → DB2 UDB v9 → Index Compression - DB2 9.7
Navigation
Log in


Forgot your password?
 
Document Actions

Index Compression - DB2 9.7

admin_get_index_compress_info

Example

After database migration, all the existing indexes are uncompressed. You may want to estimate the potential index compression savings for existing indexes on the table "S.T1", which has a data partition ID of 3 and resides on database partition number 2. In this example, S is the schema name and T1 is the table name, and T1 is not compressed

  SELECT compress_attr, iid, dbpartitionnum, index_compressed,
      pct_pages_saved, num_leaf_pages_saved 
    FROM TABLE(sysproc.admin_get_index_compress_info('', 'S', 'T1', 2, 3)) 
    AS t

The following is a sample of the output from this statement.

COMPRESS_ATTR       IID DBPARTITIONNUM INDEX_COMPRESSED ...
------------- --------- -------------- ---------------- ...
N                     1              2 N                ...
N                     2              2 N                ...
... PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED
... --------------- --------------------
...              50                  200
...              45                  150

You may decide that the savings from compression are worthwhile, and you want to enable index compression.

  ALTER INDEX INDEX1 compress yes
  ALTER INDEX INDEX2 compress yes
  REORG INDEXES all FOR table S.T1

As time passes, you may determine the need to create new indexes for the table and want to estimate index compression savings for these indexes before compressing them. You may also wish to see the compression statistics from already compressed indexes.

  SELECT compress_attr, iid, dbpartitionnum, index_compressed,
      pct_pages_saved,  num_leaf_pages_saved 
    FROM TABLE(sysproc.admin_get_index_compress_info('', 'S', 'T1', 2, 3))
    AS t

The following is a sample of the output from this statement.

COMPRESS_ATTR       IID DBPARTITIONNUM INDEX_COMPRESSED ...
------------- --------- -------------- ---------------- ...
Y                     1              2 Y                ...
Y                     2              2 Y                ...
N                     3              2 N                ...
N                     4              2 N                ...
... PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED
... --------------- --------------------
...              -1                   -1
...              -1                   -1
...              58                  230
...              49                  140

As the first two indexes were already compressed, as indicated by the index_compressed column, the statement returns values from the system catalogs. In this case, the values from the catalogs were not collected.

After running RUNSTATS on the table, the next run of the index function yields the corrected results.

  RUNSTATS ON TABLE S.T1 FOR INDEXES ALL
  SELECT compress_attr, iid, dbpartitionnum, index_compressed,
      pct_pages_saved, num_leaf_pages_saved 
    FROM TABLE(sysproc.admin_get_index_compress_info('', 'S', 'T1', 2, 3))
    AS t

The following is a sample of the output from this statement.

COMPRESS_ATTR       IID DBPARTITIONNUM INDEX_COMPRESSED ...
------------- --------- -------------- ---------------- ...
Y                     1              2 Y                ...
Y                     2              2 Y                ...
N                     3              2 N                ...
N                     4              2 N                ...
... PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED
... --------------- --------------------
...              50                  200
...              45                  150
...              58                  230
...              49                  140

ADMIN_GET_INDEX_COMPRESS_INFO table function metadata

Table 1. ADMIN_GET_INDEX_COMPRESS_INFO table function metadata
Column Name Data Type Description
INDSCHEMA VARCHAR(128) Name of the schema where the index is defined.
INDNAME VARCHAR(128) Index name.
TABSCHEMA VARCHAR(128) Name of the schema where the table is defined.
TABNAME VARCHAR(128) Table name.
DBPARTITIONNUM SMALLINT Database partition number.
IID SMALLINT Identifier for the index.
DATAPARTITIONID INTEGER Data partition ID.
COMPRESS_ATTR CHAR(1) The state of the COMPRESSION attribute on the index.
  • "Y" = Index compression is enabled
  • "N" = Index compression is not enabled
INDEX_COMPRESSED CHAR(1) Physical index format.
  • "Y" = Index is in compressed format
  • "N" = Index is in uncompressed format
If the physical index format does not match the compression attribute, an index reorganization is needed to convert index to the defined format If the table or index is in error at the time this function is executed, then this value is NULL.
PCT_PAGES_SAVED SMALLINT If the index is not physically compressed (INDEX_COMPRESSED is "N"), then this value represents the estimated percentage of leaf pages saved, as if the index were actually compressed. If the index is physically compressed (INDEX_COMPRESSED is "Y"), then this value reports the PCTPAGESSAVED value from the system catalog view (either SYSCAT.INDEXES or SYSCAT.INDEXPARTITIONS).
Note: This value is the same for each entry of an index or index partition for each database partition in a partitioned database environment. If the table or index is in error at the time this function is executed, then this value is NULL.
NUM_LEAF_PAGES_SAVED BIGINT If the index is not physically compressed (INDEX_COMPRESSED is "N"), then this value represents the estimated number of leaf pages saved as if the index were actually compressed. If the index is physically compressed (INDEX_COMPRESSED is "Y"), then this value reports the calculated number of leaf pages saved, based on the PCTPAGESSAVED and NLEAF values from the system catalog view (either SYSCAT.INDEXES or SYSCAT.INDEXPARTITIONS). If either PCTPAGESSAVED or NLEAF are invalid values (-1), then this value is set to -1 as well.
Note: This value is the same for each entry of an index or index partition for each database partition in a partitioned database environment. If the table or index is in error at the time this function is executed, then this value is NULL.
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls