CREATE INDEX

The CREATE INDEX data description statement defines an index on a base table. The index definition is stored in the dictionary. It is also a CA IDMS extension of the SQL standard.
idmscu19
The CREATE INDEX data description statement defines an index on a base table. The index definition is stored in the dictionary. It is also a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue a CREATE INDEX statement, you must:
  • Hold the ALTER privilege on or own the table on which the index is being defined
  • Hold the USE privilege on the area where the named index is stored
Syntax
  ►►─── CREATE ─┬──────────┬─ INDEX 
index-name
 ─────────────────────────────────►               └─ UNIQUE ─┘  ►─── ON ─┬────────────────┬─ 
table-identifier
 ───────────────────────────────►           └─ 
schema-name
. ─┘  ►─┬───────────────────────────────────────┬──────────────────────────────────►    │     ┌──────────── , ────────────┐     │    └─ ( ─▼─ 
column-name
 ─┬─────────┬─┴─ ) ─┘                          ├─ ASC ◄──┤                          └─ DESC ──┘  ►─┬──────────────────┬───────────────────────────────────────────────────────►    ├─ COMPRESSED ─────┤    └─ UNCOMPRESSED ◄──┘  ►─┬─────────────────────────────┬─┬─────────────────────────────┬────────────►    └─ IN 
segment-name.area-name
 ─┘ └─ 
index-block-specification
 ─┘  ►─┬─────────────┬────────────────────────────────────────────────────────────►    └─ CLUSTERED ─┘  ►─┬────────────────────────────┬─────────────────────────────────────────────►◄    └─ INDEX ID 
index-id-number
 ─┘  
Expansion of index-block-specification
  ►►─── INDEX BLOCK CONTAINS 
key-count
 KEYs ────────────────────────────────────►  ►─┬─────────────────────────────────────┬────────────────────────────────────►◄    └─ DISPLACEMENT IS 
page-count
 PAGES ──┘  
Parameters
  • UNIQUE
    Specifies that the index-key value in any given row of the table on which the index is being defined must be different from the index-key value in any other row of the table. A table with a unique index cannot contain duplicate index key values.
    If you specify UNIQUE, and the table on which the index is being defined contains duplicate rows, CA IDMS returns an error.
  • index-name
    Specifies the name of the index being created.
    Index-name
    must be a 1- through 18-character name that follows the conventions for SQL identifiers.
    Index-name
    must be unique for the table on which the index is defined, and it cannot be the same as any constraint name defined on the schema.
  • ON
    table-identifier
    Specifies the table on which the index is being defined.
    Table-identifier
    must identify a base table defined in the dictionary.
    If you specify CLUSTERED in a CREATE INDEX statement, the named table:
    • Cannot have a CALC key defined on it
    • Cannot have another clustered index defined on it
    • Cannot be the referencing table in a clustered referential constraint
  • schema-name
    Identifies the schema associated with the named table.
    If you do not specify
    schema-name
    , it defaults to:
    • The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • (
    column-name
    )
    Specifies one or more columns that make up the index key. CA IDMS maintains index entries in ascending or descending order according to the values in the specified columns. Entries are ordered first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.
    Column-name
    must identify a column in the table on which the index is being created and must be unique within the list of column names.
    You can include from 1 through 32 columns in an index key.
    If no column name is specified, CA IDMS creates an index on the db-key sorted in ascending order.
  • ASC
    Indicates that values in the named column are to be sorted in ascending order. ASC is the default when you specify neither ASC nor DESC.
  • DESC
    Indicates that values in the named column are to be sorted in descending order.
  • COMPRESSED
    Directs CA IDMS to maintain index entries in a compressed form in the database.
  • UNCOMPRESSED
    Directs CA IDMS to maintain index entries in an uncompressed form in the database.
    UNCOMPRESSED is the default when you specify neither COMPRESSED nor UNCOMPRESSED.
  • IN
    Specifies the area to be used to store entries in the index.
    If you do not associate an area with an index, CA IDMS uses the area associated with the table on which the index is being defined.
  • segment-name
    Identifies the segment associated with the area.
  • area-name
    Identifies the area to be associated with the index.
    Area-name
    must identify an area defined in the dictionary.
  • index-block-specification
    Establishes characteristics of the index.
    Syntax for
    index-block-specification
    immediately follows the syntax for CREATE INDEX.
    If
    index-block-specification
    is omitted, values for
    key-count
    and
    page-count
    are calculated by CA IDMS using available information about actual or estimated row count for the table on which the index is being defined.
  • CLUSTERED
    Specifies that each row of the table on which the index is being defined is to be stored as close as possible to the table row with the immediately preceding index-key value.
  • INDEX ID
    index-id-number
    Assigns an index ID value for the index being created. The index-id-number must be in the range of 1 through 32767.
Parameters for Expansion of index-block-specification
  • key-count
    KEYs
    Establishes the maximum number of entries in each internal index record (SR8 system record).
    Key-count
    must be an unsigned integer in the range 3 through 8180.
    For more information on how to calculate
    key-count 
    see INDEX BLOCK CONTAINSsection.
  • page-count
    PAGES
    Indicates how far away from the top of the index (the SR7 system record) the bottom-level index records are to be stored.
    Page-count
    must be an unsigned integer in the range 0 through 32767.
    If the value of
    key-count
    is 0, the bottom-level internal index records are not displaced from the SR7 record.
    For more information on how to calculate
    page-count 
    see section
Usage
Specifying an Index ID
When defining an index you can specify a value for its numeric index identifier. If explicitly specified, it must be unique across all other indexes residing in the same database area. If not specified, the index's numeric identifier is automatically set to the next available number in the range 1 through 32,767.
SYSTEM Tables
You cannot define an index on a table in the SYSTEM schema.
SYSTEM Areas
You cannot associate an index with a system area supplied with CA IDMS.
Order of Null Values
If the value of an index key column is null, it is treated as higher than all non-null values.
Null Values in Unique Indexes
Nullable columns are allowed to be used in a UNIQUE index. Null values are treated like any other value when the uniqueness of an index is evaluated. For example, a single column index can only contain one null value.
Mixed Page Group
An index must reside in the same page group as the table on which the index is created.
Enforcing Indexes
If you define unique constraints using CREATE TABLE, unique indexes with default values are generated to enforce the constraints. You may replace enforcing indexes by creating unique indexes with the same column list, the sort order does not need to match.   When a matching unique index is created, it also becomes an enforcing index, and you may drop the either enforcing index after that.   If the existing index was generated by Create Table it wlll be automatically dropped.   When replacing a primary key enforcing index, the replacement index will become a primary key index when the primary key index is dropped.   
Example
Defining a Unique Index
The following CREATE INDEX statement defines a unique index on the JOB table. The index key consists of two columns: JOB_ID and JOB_TITLE. The index entries are stored in compressed form in the same area as the JOB table.
create unique index job_title_index    on job       (job_id, job_title)    compressed;
Defining a Clustered Index
The following CREATE INDEX statement defines an index on the MONTHLY_BUDGET table. The index key consists of two columns: FISCAL_YEAR and MONTH. The index entries are stored in compressed form in the SALESSEG.SALES_X_AREA area. Rows of the MONTHLY_BUDGET table that have consecutive index-key values are stored close to each other.
create index budget_date_index    on sales_sch.monthly_budget       (fiscal_year desc, month)    compressed    in salesseg.sales_x_area    clustered;