CREATE KEY

The CREATE KEY statement defines a key on a procedure or table procedure. The key definition is stored in the dictionary. It is also a CA IDMS extension of the SQL standard.
idmscu19
The CREATE KEY statement defines a key on a procedure or table procedure. The key 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 KEY statement, you must own or hold the ALTER privilege on the procedure or table procedure on which the key is being defined.
Syntax
  ►►─── CREATE ─┬───────────┬── KEY 
key-name
 ───────────────────────────────────►               ├─ UNIQUE  ─┤               └─ PRIMARY ─┘  ►─── ON ──┬────────────────┬──┬─ 
procedure-identifier
 ───────┬───────────────►            └─ 
schema-name
. ─┘  └─ 
table-procedure-identifier
 ─┘         ┌──────── , ────────┐  ►── ( ─▼─ 
parameter-name.
 ─┴─ ) ─┬────────────────────────────┬──────────────►                                   └─ ESTIMATED ROWS 
row-count
 ─┘  ►─┬──────────────────────────┬───────────────────────────────────────────────►◄    └─ ESTIMATED IOS io-count ─┘  
Parameters
  • UNIQUE
    Specifies the key value is unique to a row that the procedure or table procedure returns. CA IDMS does not enforce this restriction. The procedure or table procedure itself must enforce uniqueness.
  • PRIMARY
    Specifies the key is unique and that it is the most commonly-used key for identifying specific rows returned by the procedure. While you can define several unique keys for a procedure or table procedure, you can specify only one primary key.
  • key-name
    Specifies the name of the key. The
    key-name
    must be:
    • A 1- to 18-character name that follows the conventions for SQL identifiers
    • Unique for the procedure or table procedure on which the key is defined
  • ON
    table-procedure-identifier
    Specifies the table procedure for which you are defining the key. The
    table-procedure-identifier
    must identify a table procedure defined in the dictionary.
    • procedure-identifier
      Specifies the procedure for which you define the key. The procedure-identifier must identify a procedure defined in the dictionary.
    • schema-name
      Identifies the schema associated with the procedure or table procedure.
      If you do not specify a
      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
    • (
      parameter-name
      )
      Specifies one or more procedure or table procedure parameters that form the key. The
      parameter-name
      must:
      • Identify a parameter of the procedure or table procedure on which the key is defined
      • Be unique within the list of parameter names
      You can include as many as 32 parameters in a key.
    • row-count
      Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the number of rows that the procedure or table procedure returns when input values are provided for all the parameters in the key.
    • io-count
      Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the number of disk accesses that the procedure or table procedure generates while returning
      row-count
      rows when input values are provided for all the parameters in the key.
Usage
Enforcing Uniqueness
It is the responsibility of the procedure or table procedure to enforce the uniqueness of the procedure or table procedure keys; for example, on an INSERT into a table procedure, CA IDMS makes no attempt to determine whether a duplicate row, with respect to a unique table procedure key, exists. The table procedure, in conjunction with database services it invokes, is responsible for ensuring uniqueness.
Influencing Join Strategies
CA IDMS uses procedure or table procedure key information when determining the best approach to satisfy queries that join procedure or table procedures with other tables, views, procedures or table procedures. Specifically, if the set of column values provided on a particular call to the table procedure matches the columns defined in the table procedure's KEY, the ESTIMATED ROWS and ESTIMATED I/Os for that KEY are used during optimization. If these statistics are provided, and data is passed to the table procedure's key by the WHERE clause during execution, the optimizer uses the statistical information when the table procedure is joined with other tables or views. Providing estimated-row and I/O counts, for the procedure or table procedure and for each access key that the procedure uses, allows CA IDMS to select the optimal access strategy.
Unique Keys for CA IDMS Server
If you define procedure or table procedure keys, CA IDMS Server reports this information when processing an ODBC request to return key information for a procedure. The ability to return key information is particularly important for certain ODBC-based products which require a unique key to update and delete data.
Example
The following CREATE KEY statements define three keys on the EMP.ORG table procedure. The first two keys are simple access keys; the third defines a primary key for CA IDMS Server to use.
(1)       CREATE KEY ORG1 ON EMP.ORG (EMP_ID)              ESTIMATED ROWS 3              ESTIMATED IOS  3; (2)       CREATE KEY ORG2 ON EMP.ORG (MGR_ID)              ESTIMATED ROWS 5              ESTIMATED IOS  5; (3)       CREATE PRIMARY KEY ORG3 ON EMP.ORG              (MGR_ID, EMP_ID, START_DATE)              ESTIMATED ROWS 1              ESTIMATED IOS  3;
More Information