To execute the CREATE INDEX command, users must possess either INDEX privileges on the table, DBADM authority on the database containing the table, overall SYSADM or SYSCTRL authority for the location, or be the owner of the table. If the index name specified is qualified with another AuthID (different from the application AuthID), the user must possess SYSADM or SYSCTRL authority for the location.
Bold text indicates clauses or options that are supported only syntactically.
CREATE [TYPE 2] [UNIQUE [WHERE NOT NULL]] INDEX index-name ON {table-name (column-name [ASC | DESC] [,...]) | aux-table-name} [using-block | free-block | gbpcache-block | DEFINE {YES|NO}] [...] [GBPCACHE {CHANGED | ALL}] [DUPQTY number-records] [[CLUSTER | NOT CLUSTER] [PARTIONED] [PADDED | NOT PADDED] [using-block] [free-block] [gbpcache-block] [DEFINE YES | DEFINE NO] [...]] [PARTITION BY [RANGE] (partition-element [using-block | free-block | gbpcache-block] [...] [,...])]] [SUBPAGES {4 | 1 | 2 | 8 | 16}] [BUFFERPOOL bpname] [CLOSE {YES | NO}] [DSETPASS password] [DEFER {NO | YES}] [COPY YES] [PIECESIZE integer {K | M | G}]
using-block:
USING
{
VCAT {WITH | ROOT | PATH | NAME}
| STOGROUP stogroup-name
[PRIQTY {3 | integer}]
[SECQTY {3 | integer}]
[ERASE {NO | YES}]
}
free-block:
[FREEPAGE {0 | integer}]
[PCTFREE {10 | integer}]
partition-element:
PARTITION integer
[ENDING [AT] (constant[,...]) [INCLUSIVE]]
| index-name | Uniquely identifies the index among the other indexes and keys in the database. Index names must follow the rules for long identifiers. |
| table-name | Names the table being indexed. |
| column-name | Name of the column on which the index is created. If more than one column name is listed, a compound index is built on the named columns. A maximum of 64 columns may be included in a compound index. |
| integer | A whole number value from zero to 99, specifying the percentage of index space to be left free. |
| number-records | Specifies the amount of space to allocate for each .ptr block to hold duplicate record numbers. |
| 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. |
The CREATE INDEX command syntactically checks many mainframe-specific clauses(bold text). You can specify various XDB Server index file storage options with the USING VCAT clause.
Any number of indexes can be created on a table. An index can be created or dropped at any time. Indexes are maintained automatically by the system. While indexes can speed up retrieval, they do require storage space. Therefore, the tradeoff between space and retrieval speed might become an issue in larger databases. Since it takes time to maintain indexes there also exists a tradeoff between retrieval, and update, delete and insert speed.
Indexes can be created on any or all columns of a table. Only the first 190 characters of the combined indexed field values are used in creating a compound index. The actual number of characters permitted depends on the number and type of columns in the index, with single field character indexes using up to the first 254 characters of a value in an index.
Specifies a type 2 index. The TYPE 2 clause is not required. A type 2 index is always created.
This optional keyword (when appearing without the WHERE NOT NULL clause) creates a unique index with no duplicate values allowed. While indexes are generally used to speed data retrieval, unique indexes are also used to prevent the entry of duplicate values in a column or compound key (such as, a key made up of several column values).
A table also requires a unique index if there is a ROWID column that is defined as GENERATED BY DEFAULT.
A unique index cannot be created on a materialized query table.
When preceded with the UNIQUE keyword, a single-column index key defined with the UNIQUE WHERE NOT NULL clause can contain more than one null value, but all other non-null values must be unique. A good example would be a table of client data containing a column for Internet addresses. An index on NETADDR, defined with UNIQUE WHERE NOT NULL, allows any number of null values for clients with no Internet address, while still ensuring that any Internet addresses added or updated are unique.
In the case of an index key on multiple columns defined with the UNIQUE WHERE NOT NULL clause, any of the key columns in a particular record can be null and still be a valid key value. An index created with the attribute UNIQUE WHERE NOT NULL is recorded in the catalog table SYSIBM.SYSINDEXES with the value 'N' in the UNIQUERULE column.
Identifies the table on which the index is created. The name can identify a base table, a declared temporary table, or an auxiliary table.
Identifies the base table or declared temporary table on which the index is created. The name must identify a table that exists at the current server. (The name of a declared temporary table must be qualified with SESSION.) The name must not identify a created temporary table.
Identifies the auxiliary table on which the index is created. The name must identify an auxiliary table that exists at the current server. If the auxiliary table already has an index, do not create another one. An auxiliary table can only have one index.
Do not specify any columns for the index key. The key value is implicitly defined as a unique 19-byte value that is system generated.
If qualified, table-name or aux-table-name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of the field DB2 LOCATION NAME of installation panel DSNTIPR at the current server. (If the current server is not the local DB2, this name is not necessarily the name in the CURRENT SERVER special register.) Whether the name is two-part or three-part, the authorization ID that qualifies the name is the owner of the index.
These keywords are supported syntactically only. The XDB Server always creates indexes in ascending (ASC) order.
Specifies whether the index is the clustering index for the table.
This clause is only implemented syntactically in the XDB Server operating environment.
The index is to be used as the clustering index of the table. Do not specify CLUSTER for an index on an auxiliary table.
Specifies that the index is data partitioned (that is, partitioned according to the partitioning scheme of the underlying data). A partitioned index can be created only on a partitioned table space. The types of partitioned indexes are partitioning and secondary.
An index is considered a partitioning index if the specified index key columns match or comprise a superset of the columns specified in the partitioning key, are in the same order, and have the same ascending or descending attributes.
If PARTITION BY was not specified when the table was created, the CREATE INDEX statement must have the ENDING AT clause specified to define a partitioning index and use index-controlled partitioning. This index is created as a partitioned index even if the PARTITIONED keyword is not specified. When a partitioning index is created, if both the PARTITIONED and ENDING AT keywords are omitted, the index will be nonpartitioned. If PARTITIONED is specified, the USING block with PRIQTY and SECQTY specifications are optional. If these space parameters are not specified, default values are used.
A secondary index is any index defined on a partitioned table space that does not meet the definition of the partitioning index. For partitioned secondary indexes (data-partitioned secondary indexes), the ENDING AT clause is not allowed because the partitioning scheme of the index is predetermined by that of the underlying data. UNIQUE and UNIQUE WHERE NOT NULL are also not allowed. If a partitioned secondary index is created on a table that uses index-controlled partitioning, the table is converted to use table-controlled partitioning.
This clause is only implemented syntactically in the XDB Server operating environment.
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. Indexes that do not have varying-length string columns are always created as physically padded indexes.
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 being created on an auxiliary table. Indexes on auxiliary tables are always padded.
Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length.
When the index contains at least one varying-length column, the default for the option depends on the value of field PAD INDEXES BY DEFAULT on installation panel DSNTIPE:
This option leaves a percentage of the space free in the index block. The default percentage is 20. The range is 0 to 99. Read-only indexes should be created with PCTFREE 0.
Do not specify PCTFREE for an index on a declared temporary table.
XDB uses B-tree indexes. Each index consists of two files, with the file extensions .IDX and .PTR. The .PTR extension file will be empty if there are no duplicate values in the indexed column. The DUPQTY option specifies the duplicate index block size. XDB Server index-name.PTR files contain Record IDs (RIDs) of duplicate index values. The DUPQTY clause uses the number-records parameter to specify the amount of space to allocate for each .PTR block (to hold record numbers of duplicate index values).
When one .PTR block is full, it links to another block of the same size. The more links that are required, the slower the system performs when accessing duplicate index RIDs. The smaller the DUPQTY value, the smaller the disk space required. The default is 16 records. Specify a value that is closest to the average number of duplicates per index value. For more information on estimating the storage requirements of an index, see the Server Administration Guide.
This clause is supported syntactically and currently does NOT affect when the index is created (since it is always created immediately).
Specifies the maximum addressability of each piece (data set) for a secondary index . 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. |
This clause is only implemented syntactically in the XDB Server operating environment.
Indicates whether the COPY utility is allowed for the index. Do not specify COPY for an index on a declared temporary table.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the partitioning index for the table, which determines the partitioning scheme for the data in the table.
This clause is only implemented syntactically in the XDB Server operating environment.
A PARTITION clause specifies the highest value of the index key in one partition of a 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.
If you use CLUSTER, and the table is contained in a partitioned table space, you must use exactly one PARTITION clause for each partition (defined with NUMPARTS on CREATE TABLESPACE). If there are p partitions, the value of integer must range from 1 through p.
The length of the highest value of a partition (also called the limit key) is the same as the length of the partitioning index.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies that this is the partitioning index and indicates how the data will be partitioned. The table space is marked complete after this partitioning index is created. You must use at least one constant after ENDING AT in each PARTITION clause. You can use as many 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 unless the VALUES statement was already specified when the table or previous index was created.
The use of the constants to define key values is subject to these rules:
For large partitioned table space, the constants you specify are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any key values greater than the value specified for the last partition are out of range.
When you define a table space with DSSIZE, you automatically give the same size to all indexes that point to that table space.
ENDING AT can be specified only if the ENDING AT clause was not specified on a previous CREATE or ALTER TABLE statement for the underlying 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, and BP32K, BP32K1...BP32K9). 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 system catalog tables.
The default is the default buffer pool for indexes in the database.
USING VCAT allows you to specify one of the four values below. This XDB Server extension to the VCATNAME specification allows you to specify various storage location options when creating individual indexes.
Do not specify VCAT for an index on a declared temporary table.
| WITH | The default value, stores the index files in the same subdirectory as the table file. |
| ROOT | Stores the index files in the root directory of the current location. |
| PATH | Stores the index files in the path previously specified in the XDB Server index path environment variable. To use the VCAT PATH option, you must have the XDBIDX_location-name environment variable set, generally in your computer's configuration file. Enter an index path for each location's index files. For example, statements similar to the following might be included in the OS/2 config.sys file: SET XDBIDX_location1 = e:\xdb\loc1\indexes SET XDBIDX_location2 = p:\xdb\loc2\indexes |
| NAME | Stores an individual index file on the path specified by the XDB Server_index name_location name environment variable (OS/2 only). Allows the files for individual indexes to be located on separate storage devices. Take, for example, an index named COIDX that is to be created in the TUTORIAL location. The physical files for the index are to be placed in C:\IDXSTORE. Given this scenario, first set the following environment variable in your CONFIG.SYS file: SET XDB_COIDX_TUTORIAL=C:\IDXSTORE Then issue the following command (from SQLWizard, for example): CREATE INDEX COIDX ON COMPANY(COKEY) USING VCAT NAME; By allowing storage on a per index basis, the system can more flexibly use multiple physical devices, thereby increasing capacity and performance. |
Specifies the minimum primary space allocation for a DB2-managed data set. When you specify PRIQTY (with a value other than -1), the primary space allocation is at least n kilobytes, where n is:
If you do not specify PRIQTY or specify PRIQTY -1, DB2 uses a default value for the primary space allocation.
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 4KB not less than n. 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.
When determining a suitable value for PRIQTY, be aware that two of the pages of the primary space are used by DB2 for purposes other than storing index entries.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the minimum secondary space allocation for a DB2-managed data set. If you do not specify SECQTY, DB2 uses a formula to determine a value.
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 4KB not less than n. 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.
This clause is only implemented syntactically in the XDB Server operating environment.
In a data sharing environment, specifies what index pages are written to the group buffer pool. In a non-data-sharing environment, the option is ignored unless the index is on a declared temporary table. Do not specify GBPCAHCE for an index on a declared temporary table in either environment (data sharing or non-data-sharing).
| 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. GBPCACHE CHANGED is the default. 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 in the group buffer pool as they are read in from DASD. Exception: In the case of a single updating DB2 when no other DB2s 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 the index is partitioning, the value of GBPCACHE for a particular partition is given by the first of these choices that applies:
This clause is only implemented syntactically in the XDB Server operating environment.
The following example creates an index called suppidx on the SNO column of the SUPPLIER table:
CREATE INDEX suppidx
ON supplier (sno)
This index is used to speed data retrieval.
Unique indexes provide a means of preventing the insertion of duplicate data in a column which should have only one occurrence of a given value (including NULL values). For example, in the table PART, there should be only one part with the number P1. Building a unique index on PNO will prevent the entry of another P1. The command to build this index is:
CREATE UNIQUE INDEX pnoidx
ON part (pno)
The system also can use unique indexes to speed data retrieval.
A compound index is an index made up of multiple columns. The XDB Server's compound indexes can contain up to 64 columns of a table, with a combined length of up to 190 characters. A compound index can be used to speed up processing of records that are usually ordered according to the field values in the index columns. However, the primary purpose of a unique compound index is to prevent entry of duplicate keys in a table.
A key consists of one or more table columns, the field values of which are used to identify records in a table. If the PARTSUPP table has a unique key containing the PNO and SNO columns, then there should exist one and only one record for each part supplied by a particular supplier. This can be ensured by creating a unique compound index with the command:
CREATE UNIQUE INDEX psidxON partsupp(pno, sno)
For a primary key constraint to function when running in DB2 mode, you must have created a unique index on the PNO and SNO columns of the PARTSUPP table using a command like:
CREATE UNIQUE INDEX ps_key
ON partsupp (pno, sno)
The system checks for duplicate keys in the table when an attempt is made to create a unique compound index. If you are unable to create a unique compound index because your table creates duplicate keys, you should execute a query to determine which values have duplicates. To do this, select and group by all compound fields by using a clause such as:
HAVING COUNT(*) > 1
For example, if you want to create a unique compound index on the PNO and SNO columns of the PARTSUPP table, first execute the following command to verify whether there are any duplicate keys:
SELECT pno, sno
FROM partsupp
GROUP BY pno, sno
HAVING COUNT(*) > 1
If this query retrieves any records, they are duplicates, and must be removed before creating the unique compound index.
A clustered index is an index that physically orders the table data in the same sequence as the index. Only one clustered index is allowed per table. Building a clustered index causes all other indexes on the table to be rebuilt. Therefore, if the table is large (and contains many indexes) be sure to create the clustered index first.
The DUPQTY, CLUSTER and PCTFREE clauses can appear in any order after the CREATE INDEX clause. For example:
CREATE INDEX aidx ON employee(e_no)
PCTFREE 10 CLUSTER;
CREATE INDEX aidx ON employee(lname)
CLUSTER PCTFREE 30;
CREATE INDEX aidx ON employee(fname)
CLUSTER;
CREATE INDEX aidx ON employee(payrate)
PCTFREE 50 DUPQTY 16;
Creating or dropping an index causes the system to automatically update system tables for that location. You can query the system catalog tables for table, view, column, and index information. The query results can be formatted into your own reports. See XDB System Tables for descriptions of the system tables.
You can use the Catalog Browser function of SQLWizard to list table, view and index definitions using the format:
If a column is indexed, the name(s) of the index or indexes are listed after the column name, separated by a slash (/). Index type indicators consisting of a letter or a number may appear within parentheses after the index name.
Next, the data type of the column is listed. The NOT NULL or NOT NULL WITH DEFAULT keywords specifies the null permission of the column. If omitted, nulls are permitted in the column.
Partitioning indexes can only be dropped by dropping the associated table space. Nonpartitioning indexes that are not indexes on auxiliary tables can be dropped simply by dropping the indexes. An empty index on an auxiliary table can be explicitly dropped; a populated index can be dropped only by dropping other objects.
Create a unique index, named DSN8610.XDEPT1, on table DSN8610.DEPT. Index entries are to be in ascending order by the single column DEPTNO. DB2 is to define the data sets for the index, using storage group DSN8G610. Each data set (piece) should hold 1 megabyte of data at most. Use 512 kilobytes as the primary space allocation for each data set and 64 kilobytes as the secondary space allocation. These specifications enable each data set to be extended up to 8 times before a new data set is used--512KB + (8*64KB)= 1024KB.
The data sets can be closed when no one is using the index and do not need to be erased if the index is dropped.
CREATE UNIQUE INDEX DSN8610.XDEPT1
ON DSN8610.DEPT(DEPTNO ASC)
USING STOGROUP DSN8G610
PRIQTY 512
SECQTY 64
ERASE NO
BUFFERPOOL BP1
CLOSE YES
PIECESIZE 1 M;
For the above example, the underlying data sets for the index will be created immediately, which is the default (DEFINE YES). Assuming that table DSN8610.DEPT is empty, if you wanted to defer the creation of the data sets until data is first inserted into the index, you would specify DEFINE NO instead of accepting the default behavior.
Create a cluster index, named XEMP2, on table EMP in database DSN8610. Put the entries in ascending order by column EMPNO. Let DB2 define the data sets for each partition using storage group DSN8G610. Make the primary space allocation be 36 kilobytes, and allow DB2 to use the default value for SECQTY, which for this example is 12 kilobytes (3 times 4KB). If the index is dropped, the data sets need not be erased.
There are to be 4 partitions, with index entries divided among them as follows:
Associate the index with buffer pool BP1 and allow the data sets to be closed when no one is using the index. Enable the use of the COPY utility for full image or concurrent copies and the RECOVER utility.
CREATE INDEX DSN8610.XEMP2
ON DSN8610.EMP(EMPNO ASC)
USING STOGROUP DSN8G610
PRIQTY 36
ERASE NO
CLUSTER(
PART 1 VALUES('H99'),
PART 2 VALUES('P99'),
PART 3 VALUES('Z99'),
PART 4 VALUES('999'))
BUFFERPOOL BP1
CLOSE YES
COPY YES;
Create a nonpartitioning index, named DSN8610.XDEPT1, on table DSN8610.DEPT. Put the entries in ascending order by column DEPTNO. Assume that the data sets are managed by the user with catalog name DSNCAT and each data set (piece) is to hold 1 gigabyte of data at most before the next data set is used.
CREATE UNIQUE INDEX DSN8610.XDEPT1
ON DSN8610.DEPT(DEPTNO ASC)
USING VCAT DSNCAT
PIECESIZE 1048576 K;
Assume that a column named EMP_PHOTO with a data type of BLOB(110K) was added to the sample employee table for each employee's photo and auxiliary table EMP_PHOTO_ATAB was created in LOB table space DSN8D61A.PHOTOLTS to store the BLOB data for the column. Create an index named XPHOTO on the auxiliary table. The data sets are to be user-managed with catalog name DSNCAT.
CREATE UNIQUE INDEX DSN8610.XPHOTO
ON DSN8610.EMP_PHOTO_ATAB
USING VCAT DSNCAT
COPY YES;
In this example, no columns are specified for the key because auxiliary indexes have implicitly generated keys.