ALTER INDEX

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include one of the following:

• Ownership of the index
• Ownership of the table on which the index is defined
• DBADM authority for the database that contains the table
• SYSADM or SYSCTRL authority
• System DBADM

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

If BUFFERPOOL or USING STOGROUP is specified, additional privileges could be needed, as explained in the description of those clauses.

Description

index-name

Identifies the index to be changed or regenerated. 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.

REGENERATE

Specifies that the index will be regenerated. The structure that represents the index definition is regenerated. The index definition will be composed from the catalog. Existing authorities and dependencies, if any, are retained. The catalog is updated with the regenerated index definition. The index is put into rebuild-pending state and catalog entries for the index statistics are deleted. Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

If the index cannot be successfully regenerated, an error is returned. In this case, the index must be dropped and re-created.

BUFFERPOOL bpname

Identifies the buffer pool that is to be used for the index. bpname must identify an activated 4K, 8 KB, 16 KB, or 32 KB buffer pool, and the privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege for the buffer pool.

A buffer pool with a smaller size should be chosen for indexes with random insert patterns. A buffer pool with a larger size should be chosen for indexes with sequential insert patterns.

If the index is changed to use index compression (the COMPRESS YES clause), the buffer pool must be 8 KB, 16 KB, or 32 KB in size.

The change is a pending definition change if all of the following conditions are true:

• The data sets of the index are created
• The index is defined on one of the following:
  • A table that is in a universal table space, or a table that is in a table space for which a pending definition change will convert the table space to a universal table space
  • An XML table that is associated with a base table that is in a universal table space, or an XML table that is in a table space for which a pending definition change will convert the table space to a universal table space
  • One of the following types of auxiliary tables:
    • An auxiliary table that is associated with a base table that is in a universal table space
    • An auxiliary table that is associated with a base table that is in a table space for which a pending definition change will convert the table space to a universal table space
    
• The buffer pool is changed to a buffer pool with a different size, or the buffer pool is changed to a buffer pool with the same size and the table space or indexes in the table space have pending definition changes.

If any of the previous conditions are not true, the change is an immediate change.

If the change is an immediate change, the change to the description of the index takes effect the next time the data sets of the index space are opened. The data sets can be closed and reopened by a STOP DATABASE command to stop the index followed by a START DATABASE command to start the index.

If the buffer pool is changed to a buffer pool with a different page size, and the change is an immediate change, the index is placed into REBUILD-pending status.

If the change is a pending definition change, the change is not reflected in the current definition or data at the time of the alter. Instead, the index is placed in an advisory REORG-pending (AREOR) state. A subsequent reorganization of the entire index with an appropriate utility will materialize the changes and apply the pending definition changes to the catalog and data.

CLOSE

Specifies whether the data set is eligible to be closed when the index is not being used and the limit on the number of open data sets is reached. The change to the close rule takes effect the next time the data sets of the index space are opened.

YES

Eligible for closing.

NO

Not eligible for closing.

If DSMAX is reached and there are no CLOSE YES page sets to close, CLOSE NO page sets will be closed.

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. For data sharing, changing COPY to YES causes additional SCA (Shared Communications Area) storage to be used until the next full or incremental image copy is taken or until COPY is set back to NO.

PIECESIZE integer

Specifies the maximum addressability of each data set for a non-partitioned index. The PIECESIZE clause can only be specified for non-partitioned indexes.

Be aware that when you alter the PIECESIZE value, the index is placed into page set REBUILD-pending (PSRBD) status. 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 that is specified in integer.

K

Indicates that the integer value is to be multiplied by 1024 to specify the maximum data set size in bytes. integer must be a power of two between 1 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the maximum data set size in bytes. integer must be a power of two between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the maximum data set size in bytes. integer must be a power of two between 1 and 256.

Table 1 shows the valid values for data set size, which depend on the size of the table space.

Table 1. Valid values of PIECESIZE clause

K units	M units	G units	Size attribute of table space
256K
512K
1024K	1M
2048K	2M
4096K	4M
8192K	8M
16384K	16M
32768K	32M
65536K	64M
131072K	128M
262144K	256M
524288K	512M
1048576K	1024M	1G
2097152K	2048M	2G
4194304K	4096M	4G	LARGE, DSSIZE 4G (or greater)
8388608K	8192M	8G	DSSIZE 8G (or greater)
16777216K	16384M	16G	DSSIZE 16G (or greater)
33554432K	32768M	32G	DSSIZE 32G (or greater)
67108864K	65536M	64G	DSSIZE 64G (or greater)
134217728K	131072M	128G	DSSIZE 128G (or greater)
268435456K	262144M	256G	DSSIZE 256G

The data set size limit for partitioned table spaces with more than 256 partitions is 4096.

begin using-specification block

The components of the using-specification are discussed below, first for non-partitioned indexes and then for partitioned indexes.

USING (specification for nonpartitioned indexes)

For nonpartitioned 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.

If you specify USING, the index must be in the stopped state when the ALTER INDEX statement is executed. See Altering storage attributes to determine how and when changes take effect.

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 an SQL identifier. Thus, you must specify an alias if the name of the integrated catalog facility catalog is longer than eight characters. When the new description of the index is applied, the integrated catalog facility catalog must contain an entry for the data set the conforms to the DB2 naming conventions described in DB2 Administration Guide.

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.

STOGROUP stogroup-name

Specifies using a DB2-managed data set that resides on a volume of the specified storage group. 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. When the new description of the index is applied, the description of the storage group must include at least one volume serial number. Each volume serial number must identify a volume that is accessible to z/OS® for dynamic allocation of the data set, and all identified volumes must be of the same device type. Furthermore, the integrated catalog facility catalog used for the storage group must not contain an entry for the data set.

If you specify USING STOGROUP and the current data set is DB2-managed, omission of the PRIQTY, SECQTY, or ERASE clause is an implicit specification of the current value of the omitted clause.

If you specify USING STOGROUP to convert from user-managed data sets to DB2-managed data sets:

• Omission of the PRIQTY clause is an implicit specification of the default value. For information on how DB2 determines the default value, see Rules for primary and secondary space allocation.
• Omission of the SECQTY clause is an implicit specification of the default value. For information on how DB2 determines the default value, see Rules for primary and secondary space allocation.
• Omission of the ERASE clause is an implicit specification of ERASE NO.

PRIQTY integer

Specifies the minimum primary space allocation for a DB2-managed data set. integer must be a positive integer, or -1. This clause can be specified only if the data set is currently managed by DB2 and USING VCAT is not specified.

When you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is:

  12

If integer is less than 12

  integer

If integer is between 12 and 4194304

  2097152

If both of the following conditions are true:

• integer is greater than 2097152.
• The index is a non-partitioned index on a table space that is not defined with the LARGE or DSSIZE attribute.

  4194304

If integer is greater than 4194304

If you specify PRIQTY with a value of -1, DB2 uses a default value for the primary space allocation. For information on how DB2 determines the default value for primary space allocation, see Rules for primary and secondary space allocation.

If USING STOGROUP is specified and PRIQTY is omitted, the value of PRIQTY is its current value. (However, if the current data set is being changed from being user-managed to DB2-managed, the value is its default value. See the description of USING STOGROUP.)

If you specify PRIQTY and do not specify a value of -1, DB2 specifies the primary space allocation to access method services using the smallest multiple of 4 KB not less than n, where n is defined as in the PRIQTY description. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the space requested. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

When determining a suitable value for PRIQTY, be aware that two of the pages of the primary space could be used by DB2 for purposes other than storing index entries.

SECQTY integer

Specifies the minimum secondary space allocation for a DB2-managed data set. integer must be a positive integer, 0, or -1. This clause can be specified only if the data set is currently managed by DB2 and USING VCAT is not specified.

If you specify SECQTY with a value of -1, DB2 uses a default value for the secondary space allocation.

If USING STOGROUP is specified and SECQTY is omitted, the value of SECQTY is its current value. (However, if the current data set is being changed from being user-managed to DB2-managed, the value is its default value. See the description of USING STOGROUP.)

For information on the actual value that is used for secondary space allocation, whether you specify a value or DB2 uses a default value, see Rules for primary and secondary space allocation.

If you specify SECQTY, and do not specify a value of -1, DB2 specifies the secondary space allocation to access method services using the smallest multiple of 4 KB not less than integer. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the space requested. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

ERASE

Indicates whether the DB2-managed data sets are to be erased when they are deleted during the execution of a utility or an SQL statement that drops the index.

NO

Does not erase the data sets. Operations involving data set deletion will perform better than ERASE YES. However, the data is still accessible, though not through DB2.

YES

Erases the data sets. As a security measure, DB2 overwrites all data in the data sets with zeros before they are deleted.

This clause can be specified only if the data set is currently managed by DB2 and USING VCAT is not specified. If you specify ERASE, the index must be in the stopped state when the ALTER INDEX statement is executed. See Altering storage attributes to determine how and when changes take effect.

USING (specification for partitioned indexes:)

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

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-specification within the PARTITION clause for the partition. Do not use more than one using-specification in any PARTITION clause.
• The values of PRIQTY, SECQTY, and ERASE given in the using-specification before any PARTITION clause
• 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-specification within the PARTITION clause for the partition. Do not use more than one using-specification in any PARTITION clause.
• The values of PRIQTY, SECQTY, and ERASE given in a using-specification before any PARTITION clauses
• The default values of PRIQTY, SECQTY, and ERASE, which are:
  • PRIQTY 12
  • SECQTY 12, if PRIQTY is not specified in either using-specification, 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. See Altering storage attributes to determine how and when changes take effect.

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 an SQL 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 vth data set of the index, conforming to the DB2 naming convention for data sets described in DB2 Administration Guide.

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.

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.

For information on the PRIQTY, SECQTY, and ERASE clauses, see the description of those clauses in the using-specification for secondary indexes.

end using-specification block

begin free-specification block

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. Do not specify FREEPAGE for an implicitly created XML index.

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. Do not specify PCTFREE for an implicitly created XML index.

If the index is partitioned, 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 PARTITION clause for that partition. Do not use more than one free-specification in any PARTITION clause.
• The values given in a free-specification before any PARTITION clauses.
• The current values of FREEPAGE and PCTFREE for that partition.

end free-specification block

begin gbpcache-specification block

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 read-write interest on the index or partition, updated pages are written to the group buffer pool. When there is no inter-DB2 read-write interest, the group buffer pool is not used. Inter-DB2 read-write 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 read-write 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.

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 PARTITION clause for that partition. Do not use more than one gbpcache-specification in any PARTITION clause.
2. The value given in a gbpcache-specification before any PARTITION 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 certain indexes on DB2 catalog tables; for more information, see SQL statements allowed on the catalog.

end gbpcache-specification block

CLUSTER or NOT CLUSTER

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

CLUSTER

The index is used as the clustering index for the table. This change takes effect immediately. Any subsequent insert operations 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 an insert operation; data is always loaded and reorganized in cluster order.

Do not specify CLUSTER in the following cases:

• The index is for an auxiliary table.
• CLUSTER was used already for a different index on the table.
• The index is an XML index.
• The index includes expressions.
• The index is for a table that uses hash organization.
• The index is the hash overflow index for a 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. Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

COMPRESS NO or COMPRESS YES

Specifies whether the index data will be compressed. If the index is partitioned, this option will apply to all partitions.

When an index is changed from one compression option to another (either from COMPRESS YES to COMPRESS NO, or from COMPRESS NO to COMPRESS YES), the index is marked as rebuild pending. For a non-partitioned index, the index will be placed in a page set rebuilding state. For a partitioned index, the index will be placed in rebuilding state.

COMPRESS NO

Specifies that index compression will be turned off.

COMPRESS YES

Specifies that the index will use index compression. COMPRESS YES can be specified for user-managed data sets only if the control interval size is 4K.

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.

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 maximum 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 might exceed the maximum length (because 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 (using the 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, dynamically cached statements that are dependent on the index are invalidated. Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Do not specify PADDED if the index is an XML index.

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, a DECFLOAT column, or a distinct-type column that is based on a LOB or DECFLOAT data type.

The column cannot be:

• a VARBINARY column or a distinct-type column that is based on a VARBINARY data type, if the column is defined with the DESC attribute or if the index is defined with the PADDED attribute

The column cannot be a timestamp with time zone column (or a column with a distinct type that is based on the timestamp with time zone data type) when the PARTITION or PARTITION BY RANGE clause is also specified.

The index must not already be defined with the BUSINESS_TIME WITHOUT OVERLAPS specification.

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, or matches a foreign key
• 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
• An XML index
• An index that includes expressions
• The hash overflow index for a table.

The index is put into rebuild-pending (RBDP) status in the following cases:

• column-name specifies is a ROWID column
• a column is added to a table, rows are inserted into the table, and the same column is added to an associated index all within the same commit scope
• a column is added to a table and then is added to an associated index in a separate commit scope

Otherwise, the index is put into an advisory reorg-pending (AREO*) state.

ASC

Index entries are put in ascending order by the column.

DESC

Index entries are put in descending order by the column.

RANDOM

Index entries are put in a random order by the column. RANDOM cannot be specified in the following cases:

• A varying length column is part of the index key and the index is defined with the NOT PADDED option.
• A column of the index key is defined as TIMESTAMP WITH TIME ZONE.
• The index is part of a partitioning key.

ADD INCLUDE (column-name)

Specifies an additional column to append to the set of index key columns of a unique index. Any column that is specified using INCLUDE column-name, is not used to enforce uniqueness. The included column might improve performance for some queries using index only access.

Columns that are specified in the ADD INCLUDE clause count towards the limits for the number of columns and the limits on the sum of the length attributes of the columns that are specified in the index. The total number of columns for the index cannot exceed 64.

column-name must be unqualified, must identify a column of the specified table, and must not be one of the existing columns of the index. column-name must not identify a LOB or DECFLOAT column (or a distinct type that is based on one of those types).

The INCLUDE clause cannot be specified for the following types of indexes:

• A system defined catalog index
• A non-unique index
• A partitioning index when index-controlled partitioning is used
• An auxiliary index
• An index on a foreign key
• An XML index
• An extended index
• An index that includes expressions

If a column is added to both a table and an associated index within the same commit scope and the column is not a ROWID column, the index is placed in an advisory reorg-pending state (AREO*). Otherwise, the index is placed in a rebuild-pending state (RBDP).

Columns in the INCLUDE list that are defined as character or graphic string data types must be defined with the same encoding scheme as other key columns with character or graphic string data types.

ALTER PARTITION integer

Identifies the partition of the index to be altered. For an index that has n partitions, you must specify an integer in the range 1 to n. You must not use this clause under the following conditions:

• If the index is nonpartitioned
• If the index is defined on a table that contains an XML column and uses index-controlled partitioning

You must use this clause if the index is partitioned and you specify the ENDING AT clause.

ENDING AT(constant), MAXVALUE, or MINVALUE

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 sequence of the index columns. In a column defined as ascending (ASC), highest and lowest have the 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 value (constant, MAXVALUE, or MINVALUE) after ENDING AT in each PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all the values 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

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the 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'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must be MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MAXVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the following rules:

• The first value corresponds to the first column of the key, the second value to the second column, and so on.
• If a key includes a ROWID column (or a column with a distinct type that is based 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 value that is specified for the corresponding ROWID column are considered.
• Using fewer values 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.
• If the key exceeds 255 bytes, only the first 255 bytes are considered.
• 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 ENDING AT 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 ENDING AT are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any keys that are made invalid after the ALTER TABLE statement is executed are placed in a discard data set when you run the REORG utility. If the last partition is in reorg-pending status, regardless of whether you changed its limiting key values, you must specify a discard data set when you run the REORG utility.

ENDING AT must not be specified for any indexes defined on a table that uses table-controlled partitioning. Use ALTER TABLE ALTER PARTITION to modify the partitioning boundaries for a table that uses table-controlled partitioning.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

Notes

Pending definition changes:

The following ALTER INDEX options can cause pending changes to the definition of the specified index under certain conditions:

• BUFFERPOOL
• COMPRESS

When ALTER INDEX causes a pending definition change, semantic validation and authorization checking are performed for the statement. However, the current definition of the index is not changed, and the index is placed in advisory REORG-pending (AREOR) state. If there are no pending definition changes for the table space, you can run the REORG INDEX utility with SHRLEVEL CHANGE or the REORG TABLESPACE utility with SHRLEVEL CHANGE or REFERENCE to enable the changes to the definition of the index. If pending definition changes also exist for the table space, you must run the REORG TABLESPACE utility with SHRLEVEL CHANGE or REFERENCE to enable the changes to the definition of the index (and the pending table space definition).

Restrictions involving pending definition changes:

ALTER INDEX statements that result in a pending definition change are not allowed in the following cases:

• On the catalog, system objects, or objects in a workfile database
• If the definition of the table space is incomplete
• If the definition of the table on which the index is defined is incomplete
• If the ALTER INDEX statement also specifies options that will cause an immediate definition change
• If there are already pending definition changes to the index, ALTER INDEX to change from COMPRESS NO to COMPRESS YES is not allowed
• If there are already pending definition changes to the index or the table space that contains the index, the following are not allowed:
  • ALTER INDEX (with or without ALTER PARTITION) to change from a DB2-managed data set to a user-managed data set
  • ALTER INDEX REGENERATE to regenerate the index
  • ALTER INDEX ADD COLUMN to add a column to the index
  • ALTER INDEX to change the value of PIECESIZE

Altering storage attributes:

The USING, PRIQTY, SECQTY, and ERASE clauses define the storage attributes of the index or partition. If you specify the USING or ERASE clause when altering storage attributes, the index or partition must be in the stopped state when the ALTER INDEX statement is executed. A STOP DATABASE…SPACENAM… command can be used to stop the index or partition.

If the catalog name changes, the changes take effect after you move the data and start the index or partition using the START DATABASE…SPACENAM… command. The catalog name can be implicitly or explicitly changed by the ALTER INDEX statement. The catalog name also changes when you move the data to a different device. See the procedures for moving data in DB2 Administration Guide.

Changes to the secondary space allocation (SECQTY) take effect the next time DB2 extends the data set; however, the new value is not reflected in the integrated catalog until you use the REORG, RECOVER, or LOAD REPLACE utility on the index or partition. Changes to the other storage attributes take effect the next time you use the REORG, RECOVER, or LOAD REPLACE utility on the index or partition. If you change the primary space allocation parameters or erase rule, you can have the changes take effect earlier if you move the data before you start the index or partition.

Altering indexes on DB2 catalog tables:

For details on altering options on catalog tables, see SQL statements allowed on the catalog.

Size restriction for the object descriptor of an index:

The following case might result in an error being returned if the ALTER INDEX statement results in a versioned object descriptor that is larger than 30,000 bytes being added (or updated):

• An ALTER INDEX statement that results in the first version of the object descriptor being generated for the index

You might need to drop and re-create the index if the object descriptor for the index exceeds 30,000 bytes.

Invalidation of packages:

This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. See Changes that invalidate packages.

Altering limit keys:

If you specify ALTER PARTITION integer ENDING AT to change the limit key values of a partitioning index, the packages that are dependent on that index are marked invalid and go through automatic rebind the next time they are run.

Restrictions on SQL data change statements in the same commit scope as ALTER INDEX:

SQL data change statements that affect an index cannot be performed in the same commit scope as ALTER INDEX statements that affect that index.

Altering indexes for tables that are involved in a clone relationship:

You cannot change any index for a table that is involved in a clone relationship (base table or clone table). If a change to an index is required, the clone table must be dropped before the index can be changed. After the index is changed, the clone table can be created again.

Adding a varying length column to a key for a system with NOT PADDED as the default:

If the system default is NOT PADDED (the value of field PAD INDEXES BY DEFAULT on installation panel DSNTIPE is NO), no varying length columns are in the key, and the PADDED or NOT PADDED option is not explicitly specified when the index is created, the PADDED column of the SYSIBM.SYSINDEXES catalog table is populated with a blank value. If a varying length column is later added to the key, the value of the PADDED column in SYSIBM.SYSINDEXES is changed to 'Y' to indicate that the index is now a PADDED index.

Running utilities:

You cannot execute the ALTER INDEX statement while a DB2 utility has control of the index or its associated table space.

Alternative syntax and synonyms:

To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following keywords when altering the partitions of a partitioned index:

• PART can be specified as a synonym for PARTITION. In addition, the ALTER keyword that precedes PARTITION is optional. In addition, if you alter more than one partition, specifying a comma between each ALTER PARTITION integer clause is optional.
• VALUES can be specified as a synonym for ENDING AT.

Although these keywords are supported as alternatives, they are not the preferred syntax.