Primary Keys

A primary key is the column or columns that contain values that uniquely identify each row in a table.

A primary key is needed:

To extract or archive data from a table that is visited more than once during a process (for example, a child table that has two or more parent tables referenced in the Access Definition).
To insert, restore, or delete data from a table. The primary key is used to determine whether a row exists.
To enable a Point-and-Shoot session for a Start Table.

Optim™ uses primary keys that are defined to the DB2^® Catalog. You can, however, define Optim primary keys to supplement those in the DB2 Catalog. If a primary key is not defined and is required to perform a specific operation, Optim prompts you to create an Optim primary key.

You can define two types of primary keys that are stored in the Optim Directory:

An Explicit primary key applies to a single table.
A Generic primary key applies to any tables that have the same base name, column names, and attribute specifications, but different Creator IDs.

There is no difference in function or appearance between generic and explicit primary keys. However, if a table has keys of both types, Optim uses the explicit primary key.

Legacy Tables

For Move or Compare for IMS, VSAM, and Sequential Files only, you can create an Optim primary key for a VSAM or sequential file Legacy Table as though the Legacy Table were a DB2 table. Fields in a Legacy record are treated as columns for the purpose of creating a primary key.

You can select and browse IMS™ primary keys, but IMS primary keys cannot be modified. Refer to Move User Manual or Compare for IMS, VSAM, and Sequential Files.

Match Keys

When a primary key has not been defined for either table in a pair of tables to be compared, you are prompted to define a “match key.” (Compare only)

Match keys are used to “match” rows from one source with rows from the other source for the comparison. When available, Compare uses a primary key from a source table for the Compare Process. However, when a usable primary key is not defined in the DB2 Catalog or in the Optim Directory for either table, you are prompted to create a match key.

For the most part, the prompts to define match keys are the same as those documented in this section for defining primary keys. When defining a match key, the column names in both source tables must match — either directly, having the same base name and attributes, or indirectly, using a Column Map to match unlike names.

Also note that, unlike the DB2 primary keys, match keys need not be based on a unique index and, unlike OPT primary keys, match keys are not available to other users or processes. Match keys reside in the Compare Definition only.

For Relationships

If present, primary keys assist in defining relationships. Specifically, when you create a relationship and the parent table has a primary key, the names of the primary key columns are automatically inserted into the Define Relationship panel. You may then modify the column information as desired.