ADMIN_GET_INDEX_COMPRESS_INFO table function - returns compressed index information

The ADMIN_GET_INDEX_COMPRESS_INFO table function returns the potential index compression savings for uncompressed indexes.

Syntax

The schema is SYSPROC.

Table function parameters

objecttype

An input argument of type VARCHAR(1) that indicates the object type. The value must be one of the following case-sensitive values:

'T', NULL, or the empty string to indicate a table
'I' for an index

objectschema

A case-sensitive input parameter of type VARCHAR(128) that specifies the object schema.

If objecttype is 'T', NULL, or the empty string ("), then objectschema indicates the table schema.

If objectschema is specified and objectname is NULL or the empty string ("), then information is returned for all indexes on all tables in the specified schema.
If both objectschema and objectname are specified, then information is returned for all indexes on the specified table.

If objecttype is 'I', then objectschema indicates the index schema.

If objectschema is specified and objectname is NULL or the empty string ("), then information is returned for all indexes in the specified schema.
If both objectschema and objectname are specified, then information is returned for the specified index.
If neither objectschema or objectname are specified, then information is returned for all indexes in all of the schemas.

If objectname is specified and objectschema is not specified, the function returns an SQL error. A parameter value is said to be unspecified when either it has a value of NULL or the empty string (").

objectname

A case-sensitive input parameter of type VARCHAR(128) that specifies the object name. See the description for the objectschema parameter.

member

An input parameter of type INTEGER that specifies a database member number. When specified, information is returned only for indexes that reside on the specified database member. To specify that data should be returned for all active database members, set the member parameter value to either -2 or NULL. In single-member environments, specify -2 or NULL.

datapartitionid

An input parameter of type INTEGER that specifies the data partition ID. When specified, information is returned only for index partitions defined on the specified data partitions. The data partition ID should correspond to the DATAPARTITIONID found in the SYSCAT.DATAPARTITIONS view. To specify that data should be returned for all data partitions, set the datapartitionid parameter value to either -2 or NULL. For nonpartitioned indexes, specify -2, 0, or NULL.

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

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Usage notes

Do not use this function on existing indexes to get actual compression savings; instead run the RUNSTATS command (if statistics are not up to date), then query either or both of the following catalog values:
- SYSCAT.INDEXES.PCTPAGESSAVED
- SYSCAT.INDEXPARTITIONS.PCTPAGESSAVED

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 want 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

Information returned

Table 1. Information returned by ADMIN_GET_INDEX_COMPRESS_INFO
Column Name	Data Type	Description
INDSCHEMA	VARCHAR(128)	index_schema - Index schema monitor element
INDNAME	VARCHAR(128)	index_name - Index name monitor element
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
IID	SMALLINT	iid - Index identifier monitor element
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.