Create Optim Primary Keys

This utility is executed at installation because primary keys are required for some processes, as well as to simplify creating relationships. This utility is also provided as an upgrade option and as a maintenance option and should be re-executed to create additional primary keys for newly created tables and/or unique indexes.

When this option is selected, the following panel is displayed.

------------  Create OPTIM Primary Key For Unique Indices --------------------
COMMAND ===>


This process scans the DB2 Catalog for unique indices on tables which do not
have DB2 Primary Keys defined and creates Primary Key Definitions for these
in the Optim Directory.


   Directory CreatorID ===> SYSOPT
   Optim Plan Name        : USRPLAN
   Report PDS Library  ===> 'OPTRT.PROD.INSTALL' 

Press ENTER to create the Primary Keys
Press END to bypass this process

********************************** N O T E S ********************************
*           You must have EXECUTE authority for the Optim Plan and          *
*      update authority for the report PDS to complete this function.       *
*****************************************************************************

The following prompts are presented:

Directory CreatorID

Creator ID of the Directory that is to receive the primary key values.

Optim Plan Name

Name of the Optim™ Plan currently executing. This value cannot be modified. You must have EXECUTE authority for the named plan.

Report PDS Library

Name of the partitioned dataset that is to contain the report generated by the conversion utility. The report will be stored in this partitioned dataset in a member named RPTPKEY. If report output is not desired, leave blank.

Press ENTER to proceed with creating primary keys, or use END to bypass the process.

Utility Processing

The utility proceeds as follows:

• The utility searches for a table that does not have a primary key.
• If there is no unique index for that table, the table is ignored.
• If there is a single, unique index, the utility creates a primary key for the table based on the index and stores this key in the Optim™ Directory.

If there are multiple unique indexes, the utility attempts to determine which has the fewest columns. If only one is located, it selects that index. If more than one, it selects the first index DB2 returns.

Utility Messages

The utility messages are written to the RPTPKEY member in the library named as the Report PDS Library. The default Report PDS Library is the installation library. The following is the format of the message generated for each primary key that is created. (The variable text is shown in Italics.)

Table Name:

creator.tablename

Status:

Created Optim Key from Unique Index creator.indxname

Key Columns:

column1 column2

Regardless of whether you wish to write all of the messages to this file, a summary message is displayed at the terminal when the utility completes.

nnnn

Tables Processed

nnnn

Optim Primary Keys Created

nnnn

Tables already had a Primary Key

where nnnn is a numeric value.

This function may also be invoked in a batch job. In volatile environments where primary keys are added or updated regularly, a batch job may be scheduled to run at regular intervals to keep the Directory synchronized with the catalog ensuring good performance. Sample JCL to run this utility is provided in the installation library in the member FOPJCLUT.