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.
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}] )]
| 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. |
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.
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.
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.
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.
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.
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:
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:
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.
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.
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.
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.
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 XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.
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:
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.
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 XDB server operating environment syntactically verifies this clause, and records information in the appropriate catalog tables.
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.
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.
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.
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.
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.
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.
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:
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.
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;