ALTER INDEX

On a mainframe DB2 subsystem, this statement changes the configuration of an existing index. See the ALTER TABLE command for information on altering indexes that result from UNIQUE, PRIMARY and FOREIGN keys. In some cases, it may be necessary to drop an index and re-create it using the DROP INDEX and CREATE INDEX commands.

Invocation

When issued against an XDB Server, this statement only supports the ADD COLUMN clause, but is fully supported when issued against a DB2 database through XDB Link.

Syntax

ALTER INDEX index-name 
    [BUFFERPOOL bpname]
    [CLOSE {YES | NO}] 
    [COPY {YES | NO}] 
    [DSETPASS password] 
    [PART integer] 
    [FREEPAGE integer] 
    [PCTFREE integer] 
    [USING {VCAT catalog-name 
            | STOGROUP stogroup-name}] 
    [PRIQTY {3 | integer}] 
    [SECQTY {3 | integer}] 
    [ERASE {YES | NO}] [GBPCACHE {CHANGED | ALL}]
    [CONVERT TO {TYPE 1 [SUBPAGES {4 | 1 | 2 | 8 | 16}] | TYPE 2}] 
    [PIECESIZE integer {K | M | G}] 
    [PART integer [VALUES (constant[,...])] 
        [[using-block | free-block | gbpcache-block][...]][,...]]    
    [{CLUSTER | NOT CLUSTER}]
    [{PADDED | NOT PADDED}] 
    [ADD COLUMN (column-name [{ASC | DESC}] )]

     

Parameters:

bpname A name that identifies a bufferpool. The 4K bufferpools are named BP0, BP1, BP2...BP49. The 32K bufferpools are named BP32K, BP32K1, BP32K2...BP32K9.

Description

index-name

Identifies the index to be altered. The name must identify a user-created index that exists at the current server. The name must not identify an index that is defined on a declared temporary table.

BUFFERPOOL

This clause has no functional equivalent within the XDB Server operating environment. However, for DB2 compatibility purposes, the XDB Server records any of the sixty possible bufferpools (BP0...BP49, BP32K, BPK1...BPK9). The first fifty bufferpools are for indexes with 4K page sizes, while the last ten are for indexes with 32K page sizes. Although not functionally supported, this new information is semantically checked and recorded in the XDB Server catalog tables.

COPY

Indicates whether the COPY utility is allowed for the index.

NO Does not allow full image or concurrent copies or the use of the RECOVER utility on the index.
YES Allows full image or concurrent copies and the use the RECOVER utility on the index.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

PIECESIZE integer

Specifies the maximum addressability of each piece (data set) for a nonpartitioning index. Be very aware that when you alter the PIECESIZE value, the index is placed into REBUILD-pending (PSRBD) status and the entire index space becomes inaccessible. You must run the REBUILD INDEX or the REORG TABLESPACE utility to remove that status.

The subsequent keyword K, M, or G, indicates the units of the value specified in integer.

K Indicates that the integer value is to be multiplied by 1 024 to specify the maximum piece size in bytes. The integer must be a power of two between 256 and 67 108 864.
M Indicates that the integer value is to be multiplied by 1 048 576 to specify the maximum piece size in bytes. The integer must be a power of two between 1 and 65 536.
G Indicates that the integer value is to be multiplied by 1 073 741 824 to specify the maximum piece size in bytes. The integer must be a power of two between 1 and 64

The piece size limit for partitioned table spaces with more than 254 partitions is 4096.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

Using Block for Nonpartitioning Indexes

For nonpartitioning indexes, the USING clause specifies whether the data sets for the index are to be managed by the user or managed by DB2. The USING clause applies to every data set that can be used for the index. (A nonpartitioning index can have more than one data set if PRIQTY+118 * SECQTY is at least 2 gigabytes.)

If you specify USING, the index must be in the stopped state when the ALTER INDEX statement is executed.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

USING Block for Partitioning Indexes:

For a partitioning index, there is an optional PART clause for each partition. A using-block can be specified at the global level or at the partition level. A using-block within a PART clause applies only to that partition. A using-block specified before any PART clauses applies to every partition except those with a PART clause with a using-block.

For DB2-managed data sets, the values of PRIQTY, SECQTY, and ERASE for each partition are given by the first of these choices that applies:

  • The values of PRIQTY, SECQTY, and ERASE given in the using-block within the PART clause for the partition. Do not use more than one using-block in any PART clause.
  • The values of PRIQTY, SECQTY, and ERASE given in a using-block before any PART clauses
  • The current values of PRIQTY, SECQTY, and ERASE

For data sets that are being changed from user-managed to DB2-managed, the values of PRIQTY, SECQTY, and ERASE for each partition are given by the first of these choices that applies:

  • The values of PRIQTY, SECQTY, and ERASE given in the using-block within the PART clause for the partition. Do not use more than one using-block in any PART clause.
  • The values of PRIQTY, SECQTY, and ERASE given in a using-block before any PART clauses
  • The default values of PRIQTY, SECQTY, and ERASE, which are:

    PRIQTY 12

    SECQTY 12, if PRIQTY is not specified in either using-block, or 10% of PRIQTY or 3 times the index page size (whichever is larger) when PRIQTY is specified

    ERASE NO

Any partition for which USING or ERASE is specified (either explicitly at the partition level or implicitly at the global level) must be in the stopped state when the ALTER INDEX statement is executed.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

VCAT catalog-name

Specifies a user-managed data set with a name that starts with the specified catalog name. You must specify the catalog name in the form of a short identifier. Thus, you must specify an alias if the name of the integrated catalog facility catalog is longer than eight characters.

If n is the number of the partition, the identified integrated catalog facility catalog must already contain an entry for the nth data set of the index. Refer to appropriate DB2 documentation for a full explanation of DB2 naming conventions for data sets.

One or more DB2 subsystems could share integrated catalog facility catalogs with the current server. To avoid the chance of having one of those subsystems attempt to assign the same name to different data sets, select a value for catalog-name that is not used by the other DB2 subsystems.

DB2 assumes one and only one data set for each partition.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

STOGROUP stogroup-name

If USING STOGROUP is used, stogroup-name must identify a storage group that exists at the current server and the privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege for the storage group.

DB2 assumes one and only one data set for each partition.

Omitting or specifying -1 for the PRIQTY or SECQTY clause implicitly specifies the default value for that clause. For more information on the PRIQTY, SECQTY, and ERASE clauses, see the description of those clauses for nonpartitioning indexes.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

FREEPAGE integer

Specifies how often to leave a page of free space when index entries are created as the result of executing a DB2 utility. One free page is left for every integer pages. The value of integer can range from 0 to 255. The change to the description of the index or partition has no effect until it is loaded or reorganized using a DB2 utility.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

PCTFREE integer

Determines the percentage of free space to leave in each nonleaf page and leaf page when entries are added to the index or partition as the result of executing a DB2 utility. The first entry in a page is loaded without restriction. When additional entries are placed in a nonleaf or leaf page, the percentage of free space is at least as great as integer.

The value of integer can range from 0 to 99, however, if a value greater than 10 is specified, only 10 percent of free space will be left in nonleaf pages. The change to the description of the index or partition has no effect until it is loaded or reorganized using a DB2 utility.

If the index is partitioning, the values of FREEPAGE and PCTFREE for a particular partition are given by the first of these choices that applies:

  • The values of FREEPAGE and PCTFREE given in the PART clause for that partition. Do not use more than one free-block in any PART clause.
  • The values given in a free-block before any PART clauses.
  • The current values of FREEPAGE and PCTFREE for that partition.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

GBPCACHE

Specifies what index pages are written to the group buffer pool in a data sharing environment. In a non-data-sharing environment, you can specify this option, but it is ignored.

CHANGED When there is inter-DB2 R/W interest on the index or partition, updated pages are written to the group buffer pool. When there is no inter-DB2 R/W interest, the group buffer pool is not used. Inter-DB2 R/W interest exists when more than one member in the data sharing group has the index or partition open, and at least one member has it open for update.

If the index is in a group buffer pool that is defined as GBPCACHE(NO), CHANGED is ignored and no pages are cached to the group buffer pool.

ALL Indicates that pages are to be cached to the group buffer pool as they are read in from DASD, with one exception. When the page set is not GBP-dependent and one DB2 data sharing member has exclusive R/W interest in that page set (no other group members have any interest in the page set), no pages are cached in the group buffer pool.

Hiperpools are not used for indexes or partitions that are defined with GBPCACHE ALL.

If the index is in a group buffer pool that is defined as GBPCACHE(NO), ALL is ignored and no pages are cached to the group buffer pool.

NONE Indicates that no pages are to be cached to the group buffer pool. DB2 uses the group buffer pool only for cross-invalidation.

If you specify NONE, the index or partition must not be in group buffer pool recover pending (GRECP) status.

If the index is partitioned, the value of GBPCACHE for a particular partition is given by the first of these choices that applies:

  1. The value of GBPCACHE given in the PART clause for that partition. Do not use more than one gbpcache-block in any PART clause.
  2. The value given in a gbpcache-block before any PART clauses.
  3. The current value of GBPCACHE for that partition.

If you specify GBPCACHE in a data sharing environment, the index or partition must be in the stopped state when the ALTER INDEX statement is executed. You cannot alter the GBPCACHE value for the following indexes on catalog table SYSIBM.SYSINDEXES: DSNDXX01, DSNDXX02, DSNDXX03.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

PART integer

Identifies a partition of the index. For an index that has n partitions, you must specify an integer in the range 1 to n. You must not use this clause if the index is nonpartitioned. You must use this clause if the index is partitioned and you specify the VALUES clause.

VALUES(constant,...) Specifies the highest value of the index key for the identified partition of the partitioning index. In this context, highest means highest in the sorting sequences of the index columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC), the lowest actual value is highest in the sorting sequence.

You must use at least one constant after VALUES in each PART clause. You can use as many constants as there are columns in the key. The concatenation of all the constants is the highest value of the key in the corresponding partition of the index. The length of each highest key value (also called the limit key) is the same as the length of the partitioning index.

The use of the constants to define key values is subject to these rules:

  • The first constant corresponds to the first column of the key, the second constant to the second column, and so on. Each constant must have the same data type as its corresponding column.
  • If a key includes a ROWID column (or a column with a distinct type that is sourced on a ROWID data type), the values of the ROWID column are assumed to be in the range of X'000...00' to X'FFF...FF'. Only the first 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
  • The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column.
  • If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'; if the column is descending, the padding character is X'00'.
  • Using fewer constants than there are columns in the key has the same effect as using the highest possible values for all omitted columns for an ascending index. For a descending index, it has the same effect as using the lowest possible values for all omitted columns.
  • The highest value of the key in any partition must be lower than the highest value of the key in the next partition.
  • The highest value of the key in the last partition depends on how the table space was defined. For table spaces created without the LARGE or DSSIZE option, the constants you specify after VALUES are not enforced. The highest value of the key that can be placed in the table is the highest possible value of the key.For table spaces created with the LARGE or DSSIZE options, the constants you specify after VALUES are enforced. The value specified by the constants is the highest value of the key that can be placed in the table. Any keys that are made invalid after the ALTER statement is executed are placed in a discard data set when you run REORG. If the last partition is in REORG pending, regardless of whether you changed its limiting key values, you must specify a discard data set when you run REORG.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

CLUSTER or NOT CLUSTER

Specifies whether the index is the clustering index for the table.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

CLUSTER

The index is used as the clustering index for the table. This change takes effect immediately. Any subsequent INSERT statements will use the new clustering index. Existing data remains clustered by the previous clustering index until the table space is reorganized.

The implicit or explicit clustering index is ignored when data is inserted into a table space that is defined with MEMBER CLUSTER. Instead of using cluster order, DB2 chooses where to locate the data based on available space. The MEMBER CLUSTER attribute affects only data that is inserted with the INSERT statement; data is always loaded and reorganized in cluster order.

Do not specify CLUSTER if the table is an auxiliary table or if CLUSTER was used already for a different index on the table.

NOT CLUSTER

The index is not used as the clustering index of the table. If the index is already defined as the clustering index, it continues to be used as the clustering index by DB2 and the REORG utility until clustering is explicitly changed by specifying CLUSTER for a different index.

Specifying NOT CLUSTER for an index that is not a clustering index is ignored.

If the index is the partitioning index for a table that uses index-controlled partitioning, the table is converted to use table-controlled partitioning. The high limit key for the last partition is set to the highest possible value for ascending key columns or the lowest possible value for descending key columns.

NOT PADDED or PADDED

Specifies how varying-length string columns are to be stored in the index. If the index contains no varying-length columns, this option is ignored, and a warning message is returned.

The XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.

NOT PADDED

Specifies that varying-length string columns are not to be padded to their maximum length in the index. The length information for a varying-length column is stored with the key.

NOT PADDED is ignored and has no effect if the index is on an auxiliary table. Indexes on auxiliary tables are always padded.

When PADDED is changed to NOT PADDED, the index key length is recalculated with the varying-length formula (2000 - n - 2m, where n is the number of columns that can contain null values and m is the number of varying-length columns in the key). If it is possible that the index key length may exceed the maximum length (because before when it was padded, the formula 2000 - n was used), an error occurs.

PADDED

Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length.

When an index with at least one varying-length column is changed from PADDED to NOT PADDED, or vice versa, the index is placed in restricted REBUILD-pending status (RBDP). The index cannot be accessed until it is rebuilt from the table (REBUILD INDEX, REORG TABLESPACE, or LOAD REPLACE utility). For nonpartitioned secondary indexes (NPSIs), the index is placed in page set REBUILD-pending status (PSRBD), and the entire index must be rebuilt. In addition, plans and packages that are dependent on the table are quiesced, and dynamically cached statements that are dependent on the index are invalidated.

ADD COLUMN column-name

Adds column-name to the index. column-name must be unqualified, must identify a column of the table, must not be one of the existing columns of the index, and must not be a LOB column or a distinct type column based on a LOB data type. The total number of columns for the index cannot exceed 64.

For PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000 - n, where n is the number of columns that can contain null values.

For NOT PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000 - n -2m, where n is the number of nullable columns and m is the number of varying-length columns.

The index cannot be any of the following types of indexes:

  • A system-defined catalog index
  • An index that enforces a primary key, unique key, or referential constraint
  • A partitioning index when index-controlled partitioning is being used
  • A unique index required for a ROWID column defined as GENERATED BY DEFAULT
  • An auxiliary index

If the column is added to the table and that same column is added to an associated index within the same commit scope and within that commit scope no data rows were inserted, the index is left in advisory REORG-pending (AREO*) state; otherwise, the index is left in REBUILD-pending (RBDP) state.

  • ASC - Index entries are put in ascending order by the column.
  • DESC - Index entries are put in descending order by the column.

Examples:

Alter the index DSN8610.XEMP1. Indicate that XDB is not to close the data sets that support the index when there are no current users of the index.

 ALTER INDEX DSN8610.XEMP1 
     CLOSE NO;

Alter the index DSN8610.XPROJ1. Use BP1 as the buffer pool that is to be associated with the index, indicate that full image or concurrent copies on the index are allowed, and change the maximum size of each data set to 8 megabytes.

 ALTER INDEX DSN8610.XPROJ1 
     BUFFERPOOL BP1
     COPY YES
     PIECESIZE 8M;