CREATE CALC

The CREATE CALC data description statement defines a CALC key on a base table. The CALC key definition is stored in the dictionary. It is also a CA IDMS extension of the SQL standard. You can define only one CALC key on any given table.

Authorization

To issue a CREATE CALC statement, you must own or have the ALTER privilege on the table on which the CALC key is being defined.

Syntax

►►─── CREATE ─┬──────────┬─ CALC key ─────────────────────────────────────────►
              └─ UNIQUE ─┘

 ►─── ON ─┬────────────────┬─ table-identifier ───────────────────────────────►
          └─ schema-name. ─┘

         ┌────── , ──────┐
 ►─── ( ─▼─ column-name ─┴─ ) ────────────────────────────────────────────────►◄

Parameters

UNIQUE

Specifies the CALC key value in any given row of the table on which the CALC key is being defined must be different from the CALC key value in any other row of the table. A table with a unique CALC key cannot contain duplicate CALC key values.

table-identifier

Specifies the table on which the CALC key is being defined. Table-identifier must identify a base table defined in the dictionary. The named table cannot:

Contain any data
Have a clustered index defined on it
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 CALC key. Column-name must identify a column in the table on which the CALC key is being created and must be unique within the list of column names.

You can include from 1 through 32 columns in a CALC key.

Usage

SYSTEM tables

You cannot define a CALC key on a table in the SYSTEM schema.

Example

Defining a Unique CALC Key

The following CREATE CALC statement defines a unique CALC key on the COVERAGE table. The CALC key consists of two columns: PLAN_CODE and EMP_ID.

create unique calc key
   on coverage
      (plan_code, emp_id);

Note: For more information about dropping CALC key definitions, see DROP CALC.