Create the Optim Directory Tables

Edit online

The fifth option on the Full Installation menu prompts for the information needed to create the tables that hold object definitions used by Optim™.

Although the base table names must be ADDEF, CDDEF, PKDEF, RELDEF, TMDEF, CMDEF, ARCHDEF, ARCHIDX, ADB2AUDIT, MDB2PROCESS, LTDEF, ARCHLOG, ENVDEF, RDDEF, AFCOLLCT, CPMETER and CMPROC, any user-specified name can be supplied to replace the default SYSFOP Authorization ID. The same user-specified name must be used for all tables.

Note: Both the CMDEF and CMPROC tables contain a LOB column. During installation and upgrade processing, Optim will set the CURRENT RULES special register to STD, allowing Db2® to implicitly create the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in the base table.

Access Definition Table

This table contains Access Definitions created while using Optim™. Some sample Access Definitions are distributed with the products and are loaded into this table through Option 10 (Setup Directory and Sample Database). This table is required to store Access Definitions. The default name is SYSFOP.ADDEF.

Compare Definition Table

This table contains any Compare Definitions created while using Compare. This table is required even if you do not install or use Compare. The default name is SYSFOP.CDDEF.

Primary Key Table

This table stores the primary keys defined through Optim™. This table is required. The default name is SYSFOP.PKDEF.

Relationship Table

This table stores relationships defined through Optim™ as well as shadow copies of the Db2® relationships for faster access. This table is required. The default name is SYSFOP.RELDEF.

Table Map Table

This table stores the Table Maps defined using Optim™. This table is required. The default name is SYSFOP.TMDEF.

Column Map Table

This table stores the Column Maps defined using Optim™. This table is required. The default name is SYSFOP.CMDEF.

Archive Directory Table

This table stores information about the Archive Files created while using Archive. This table is required even if you do not intend to install Archive. The default name is SYSFOP.ARCHDEF.

Archive Index Table

This table stores the index and content information for Archive Files created while using Archive. This table is required even if you do not intend to install Archive. The default name is SYSFOP.ARCHIDX.

Audit Table

This table stores the audit information accumulated when the Access Audit Facility is activated. This table is required even if you do not currently intend to activate the Audit Facility or Access. The default name is SYSFOP.ADB2AUDIT.

Note:

If the Audit Facility is activated, this table has the potential to become quite large. It is recommended that you maintain the table by periodically executing SQL, such as the following:


         DELETE FROM SYSFOP.ADB2AUDIT
         WHERE TIMESTAMP < CURRENT TIMESTAMP - 7 DAYS;

This sample SQL is included in the sample library in the member FOP9SQL2.

If you would rather not maintain the audit table, disable the Audit Facility by specifying NEVER for the Audit Mode setting on the Site Options panel (see Figure 1).

If the table resides in a separate table space, it can be more easily tailored to site requirements. For this reason, IBM® recommends a separate table space for ADB2AUDIT if you intend to use the Access Audit Facility.

Process Table

This table stores the information generated for the last execution of a process that can be restarted or retried. This table is required. The default name is SYSFOP.MDB2PROCESS.

Optim™ Legacy Table

This table contains Legacy Table information for Optim Legacy. This table is required even if you do not intend to install Optim Legacy. The default name is SYSFOP.LTDEF.

Archive Log Table

This table stores information from the Archive Logging facility. It is required even if you do not intend to use Archive Logging. The default name is SYSFOP.ARCHLOG.

IMS™ Environment Definition Table

This table contains the overall specifications for the IMS environment. This table is required even if you do not currently intend to install Optim Legacy. The default name is SYSFOP.ENVDEF.

Archive File Collections Table

This table contains information for Archive File Collections. This table is required even if you do not currently intend to use Archive File Collections. The default name is SYSFOP.AFCOLLCT.

IMS Retrieval Definition Table

This table contains the information needed to access IMS data. This table is required even if you do not currently intend to install Optim Legacy. The default name is SYSFOP.RDDEF.

Consumption Meter: This table contains a count of the amount of data processed in an Archive, Convert, or Extract. The default name is SYSFOP.CPMETER.
Note: The use of this table has been deprecated.

Column Map Procedure Table: This table stores the Column Map Procedures defined using Optim. This table is required. The default name is SYSFOP.CMPROC.

General Information

All Optim system tables are defined on a single panel.

The names of the database and table space are taken from entries on the Specify Table Space(s) for Directory Tables panel, if any. Otherwise, names can be supplied on the panel, or the table space names can be omitted and “IN DATABASE” is assumed.

The SQL for each table is displayed separately when you enter Y at the Review SQL prior to execution prompt. (For more information about this prompt, see Review SQL Statements and DSN Subcommands.) However, the tables are actually created in a single unit of work.

------------------------ Creation of Directory Tables -----------------------
Command ===>

  Authorization ID ===>  SYSFOP

                      ADDEF        PKDEF          RELDEF    CMDEF    ARCHDEF
  Database      ===>
  Tablespace    ===>

                      ADB2AUDIT    MDB2PROCESS    TMDEF     CDDEF    ARCHIDX
  Database      ===>
  Tablespace    ===>

                      LTDEF        ARCHLOG        ENVDEF    RDDEF    AFCOLLCT
  Database      ===>
  Tablespace    ===>

                      CPMETER      CMPROC
  Database      ===>
  Tablespace    ===>

Review SQL prior to execution ===> NO  (Y-Yes, N-No)

  Press ENTER key to create tables
  Press END key to cancel

******************************** N O T E S **********************************
*  You must have CREATETAB privilege DBADM, DBCTRL, or DBMAINT authority    *
*  for the database, or SYSADM authority to complete this function.         *
*  If the target DB2 subsystem is at Version 12 Function level 504 or       *
*  higher you must use a separate table space for each table.  Either       *
*  specify individual values above or leave them blank for DB2 to           *
*  dynamically create them via the 'IN DATABASE' clause.                    *
*****************************************************************************

The following prompts are displayed:

Authorization ID: Authorization ID used to create the Tables. The default is SYSFOP. If you modify this value, the new value is used for the creation process.
Table: Name of each table to be created. This value is provided and cannot be modified.
Database: Name of the database for each table as specified on the Specify Database for Directory Tables panel. If there is no specification, Database is blank.
Tablespace: Name of the tablespace for each table as specified on the Specify Table Space(s) for Directory Tables panel. If there is no specification, this is blank. You can modify table space names on this panel. If Tablespace is blank for all tables, Db2 creates an implicit table space for each table.