CREATE INDEX statement for VSAM

You can use the CREATE INDEX statement to define an index that references the columns that make up a VSAM KSDS primary key or the columns that map to an alternate index PATH definition.

Indexes identify columns in a table that correspond to a physical index that is in the source database. The query processor uses the contents of the columns that are referenced in a WHERE clause to create the index. The query processor attempts to build either a full key value to use in database access or a partial key value. The partial key can be used to perform a range scan against the target database to reduce the number of records that are accessed.

Although you might not be able to define an index against columns that correspond to the primary key, you can identify these columns as primary key columns. This primary key information does not affect existing connector index selection and access optimization. The primary key information is made available for use by front-end tools, where key information is either required or beneficial.

Syntax

data-set-information:

Parameters

CREATE INDEX index-name

Identifies the SQL statement as an index definition statement. A unique index does not have any restrictions about the columns that make up the index. For example, a unique index column can contain null values.

If qualified, the index name is a two-part name, and the authorization ID that qualifies the name is the owner of the index. If an unqualified table name is supplied, the owner name is the authorization ID from the CURRENT SQLID special register.

ON table-name

Identifies the table for which the index is being defined. The table name can be a qualified or unqualified table name. For unqualified names, the table owner is from the CURRENT SQLID special register.

The table name is validated with the standard syntax checks that are associated with identifiers, and then the existence of the table is verified. Do not define an index on a view.

column-name ASC/DESC

Specifies column names that make up the index key. Optionally, you can identify whether the column is stored or accessed in ascending (ASC) or descending (DESC) key sequence. By default, the key is in ascending key sequence.

There are no fixed limits on the number of columns that you can identify as key columns for an index. The only requirements are as follows:

The column must exist in the table.
The column must map to what constitutes a key in the target database. In most cases, this determination is based on the starting offset and length of the column as compared to the starting offset and length of the key in the target database.
The column cannot overlap another column within the key definition. This determination is based on the starting offset and length of a column as compared to the starting offsets and length of the other columns that are identified as key columns for the index definition.
For the key column, generally varying length and graphic data types are not supported.

Key column verification is strict and enforces all rules for column names. Rule 4 enforcement (nonvalid data types) is enforced. Identification of any kind of varying-length character or graphic string is not allowed.

Data-set-information

Identifies the data set that accesses the columns that make up the table that table_name references. One common reason to create an index is when a VSAM KSDS file has an alternate index that is associated with it. The most likely scenario is that the table references the base cluster name, and some queries do not contain references in the WHERE clause to the VSAM primary key. But, the queries do contain columns in the WHERE clause that map to the key of the alternate index. For these queries, use the alternate index.

To define an alternate index, use either the DD clause or DS clause to identify the name of the alternate index path data set. Also, identify as the key columns those columns that map to the alternate index key. When a query that contains a WHERE clause references these key columns, automatic index selection determines that the index is the best candidate for processing the query. Thus, the alternate index path accesses the actual VSAM data.

You can also define indexes to publish information to tools. For example, if you have a single column that maps the entire social security number and the three-column mapping for the components, you can define two index definitions. In the first index definition, identify the single column as the key column. In the second index definition, identify the three-component columns as the keys. In this example, identification of DD or DS information is not allowed. The DD or DS clauses only identify an alternate index path data set.

DD DD-name

Specifies the VSAM file that the table maps to. Specifies either a local file reference or a reference to a CICS® file definition table (FDT) entry name.

If DD-name represents a local file reference, a DD clause that references the VSAM file must exist in the Classic federation data server JCL.

If DD-name references an FDT entry name, you must specify CICS connection information with the CICS_connection_information clause.

DD-name must correspond to a cluster or alternate index path definition for a VSAM ESDS, KSDS or RRDS data set.

DD-name is a short native identifier that conforms to the naming conventions for a z/OS® JCL DD statement.

DS data-set-name

Specifies the VSAM file that the table definition accesses. Designates a VSAM cluster definition or a PATH component when the VSAM file is accessed from a VSAM alternate index.

The data set name must correspond to a cluster or alternate index path definition for a VSAM ESDS, KSDS or RRDS data set.

The name can be 1 to 44 characters in length and follows z/OS naming conventions for VSAM data sets.

Example

The following is an example of a CREATE INDEX statement for VSAM.

CREATE UNIQUE INDEX "DBA"."EMPLOYEE_EP_IDX1" 
 ON "DBA"."EMPLOYEE_EP" ("EMPL_SSN" ASC);