Previous Topic: CREATE INDEXNext Topic: CREATE PROCEDURE

Statements › CREATE KEY

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.

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:

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