Compare Process Table Map

Edit online

The COMPARE Process Table Map panel enables you to match unlike named tables and to eliminate one or more tables from the comparison.

The tables from each source are listed under the appropriate Source 1 or Source 2 heading. Each table in Source 1 is compared to the corresponding table in Source 2 displayed on the same line. You may also specify the names of any Column Maps that are to be used. (Column Maps enable you to map unlike named columns and eliminate individual columns from the Compare Process.)

When this panel is initially displayed:

Source 1 contains the Creator ID and table names defined in the Extract File or Access Definition that is used. The order in which the tables are listed is the same as in the Extract File or Access Definition. These values are protected.
Source 2 contains the Creator ID and matching table names defined in the Extract File or Access Definition, if one is used. However, if All Rows is specified, these values are populated with the information from Source 1.

When Source 2 is specified as an Extract File or an Access Definition, you can use only the tables included in the Extract File or Access Definition. However, if Source 2 is All Rows, you can use any database table, view, alias, or synonym.

Assuming an Extract File is named for Source 1 and All Rows is specified for Source 2, the following panel is displayed:

------------------------- COMPARE Process Table Map ----------------------------
Command ===>                                                           Scroll ===> CSR

Commands: APPLY,SAVE,LIST,MAP,POPULATE,ACM,CLEAR,MKEY,MOPT,TOPT  END when Done
Source 2 Tables must Exist in Corresponding Extract File
 Src 1 CID: RDLC                                          Column
 Src 2 CID: RDLC                                      >>  Map ID ===>

Source 1 Table Name      Source 2 Table Name        Type    Column Map or LOCAL
--------------------- –------------------------->> ------- ---------------------
************************************* TOP *************************************
FILE10A               FILE10A                      EXISTS
FILE10B               FILE10B                      EXISTS
FILE10C               FILE10C                      EXISTS
FILE10D               FILE10D                      EXISTS
*********************************** BOTTOM ************************************

This panel includes the following items:

Src 1 CID: The default Creator ID for the Source 1 tables, as defined in the Access Definition, Extract or Archive File being used.

Src 2 CID

The default Creator ID for the Source 2 tables. The initial display is based on the source type.

If Source 2 is an Extract File or an Access Definition, the default Creator ID is displayed and cannot be modified.

If Source 2 is All Rows (Option 3 on the Specify COMPARE Source Types panel), Src 2 CID is unprotected. If Source 1 is an Extract File, the default Creator ID from that file is displayed in Src 2 CID. If Source 1 is an Access Definition, Src 2 CID is blank.

Column Map ID: The default qualifier for Column Maps.

Source 1 Table Name: The list of tables to be compared from Source 1. The names of the tables are taken from the Access Definition or Extract or Archive File specified for the source.

Source 2 Table Name

The list of tables to be compared from Source 2. If an Access Definition, Extract or Archive File is specified as the source, Compare attempts to match same name tables. Non-matching names are not displayed. If All Rows is the source, the Source 1 table names are repeated. To edit the list:

Replace a name by typing over it or selecting a new table from a list. You can request a selection list using the LIST TABLES primary command.
Clear all names using the CLEAR primary command.
Prefix all names with a string using the PREFIX primary command.
Append a string to all names using the SUFFIX primary command.

Type

The type of the object named in Source 2 Table Name. Compare supplies this value and it is not modifiable. Possible values include:

TABLE: Table

S-MQT: System-maintained Materialized Query Table

U-MQT: User-maintained Materialized Query Table

VIEW: View

UNKNOWN: Non-existent table or no value in Dest CID

EXISTS: Exists

TEMPTBL: Temporary table

A-TABLE: Alias of a table

S-TABLE: Synonym of a table

A-VIEW: Alias of a view

S-VIEW: Synonym of a view

UNUSED: Unused

When a Source 2 table is not mapped to a Source 1 table, the type is UNUSED. When a Source 2 table does not exist, the type is UNKNOWN. (This must be resolved before saving or using the Compare Definition.)

When Source 2 is a table and the type value is EXISTS, one of the following values may also be displayed:

An asterisk (*) indicates a user-defined match key will be applied to the tables.
A dash (-) indicates user-specified match options will be used.
A plus sign (+) indicates both user-defined match key and user-specified match options.

When Source 2 is an Extract File, the original source type may be unavailable; therefore, the type is EXISTS.

Column Map: The name of the Column Map used to compare the two tables.

Modify Source 2 Table Names

You can modify the Source 2 Creator ID when All Rows is the source. You can modify any of the table names specified for Source 2. However, if the source is specified as an Access Definition, Extract or Archive File, you can only specify table names included in the specified source. If the source is All Rows, you can specify any database table, view, alias, or synonym.

When Source 2 is All Rows, you can display a selection list of tables for Source 2, using the LIST TABLES command to replace or insert a table name. This command presents a list of the available tables and assigns a number to the tables listed for Source 1. This number is used to select a table from the list of Source 2 tables. The following is a sample of the LIST TABLES display. In the following figure, LIST TABLES FOPDEMO.% has been entered. Note there are a few database tables not used in the Extract File for Source 1, but included on the list for Source 2 tables because they are in the database.

----------------------- COMPARE Process Table Map -----------------------------
Command ===>                                                            Scroll ===> PAGE

Available Commands: APPLY,SAVE,LIST,MAP,POPULATE,ACM,CLEAR,MKEY,MOPT END when Done
Source 2 May be any Tables or Views
 Src 1 CID: FOPDEMO Column
 Src 2 CID: FOPDEMO ==>
                              +-------------Tables--------------+
Num Source 1 Tables    Type   |                                 | ap or "LOCAL"
--- ----------------- ------- | Select Items by Matching ‘Num’  | -------------
*** ************************* |                                 | *************
  1 CUSTOMERS         TABLE   | Num CreatorID.TableName 1 OF 9  |
  2 ORDERS            TABLE   | --- --------------------        |
  3 DETAILS           TABLE   | ********* TOP **********        |
  4 ITEMS             TABLE   | 1__ FOPDEMO.CUSTOMERS           |
  5 SHIP_TO           TABLE   | 2__ FOPDEMO.ORDERS              |
***************************** | 3__ FOPDEMO.DETAILS             | *************
                              | ___ FOPDEMO.FEMALE_RATES        |
                              | 4__ FOPDEMO.ITEMS               |
                              | ___ FOPDEMO.MALE_RATES          |
                              | ___ FOPDEMO.SHIP_TO             |
                              | ___ FOPDEMO.SHIP_INSTR          |
                              | ___ FOPDEMO.STATE_LOOKUP        |
                              | ******** BOTTOM ********        |
                              +---------------------------------+

Use Num on the Tables pop-up window to specify the number of the Source 1 table to match to the selected Source 2 table. In this example, five tables are mapped to the five Source 1 tables. The selected table names are automatically inserted under Source 2 Table Name. The LIST command can be used to display a selection list of tables, views, aliases, and synonyms.

When Source 2 is an Extract File, Archive File, or an Access Definition, the LIST TABLES command lists all of the objects currently not matched to Source 1 tables. Use LIST ALL to list all available objects in the Extract File, Archive File, or Access Definition. (See the Common Elements Manual for details about using LIST to map source tables.)

Use Existing Map: You can use the APPLY command to populate the Table Map with the specifications from a previously defined Table Map. If the Source 1 tables in the Compare Process match the Source 1 tables in the Table Map, Compare will populate the Source 2 tables from the existing Table Map, provided they exist.

User-Defined Match Key: You can use the MKEY command to create a match key. To specify the table pair to which you want to apply the match key, type one of the table names with the command (for example, MKEY creatorid.tablename), or type the command and place the cursor on the row with the tables. After pressing Enter, a minus sign (-) is displayed next to the Type for the tables. After completing the Table Map, you will be prompted to define a match key, which is saved with the Compare Definition. The steps used to define a match key are the same as those used to define a primary key.

Display Match Key: After a Compare Definition is saved, you can display a match key for a table pair using the MKEY DISPLAY command. To display a match key, type the command, place the cursor on the row with the table pair, and press Enter.

MOPT Command (Match Options): For a multiple compare, you can define the match options each pair of tables. Type MOPT (Match Options) on the command line, put the cursor on the source 2 table name, and then press Enter. Optim™ displays the Compare Match Options Panel.

Data Translation Options

You can use the TOPT command to define data translation options. To specify the pair of tables to which you want to apply the data translation options, do one of the following:

Type TOPT, followed by one of the table names, for example:
```
TOPT creatorid.tablename
```
Type TOPT, and then put the cursor on the row with the tables.

Press Enter. A slashmark (/) is displayed next to the Type for the tables. Press END. The Compare Data Translation Options Panel popup appears for each pair that you specified.

ACM Command: If necessary, you can use the ACM command to open the Choose Access Method pop-up dialog, allowing you to override the default method (scan or key lookup) for accessing the parent or child table for each relationship. A scan reads all rows in a table at one time; whereas a key lookup locates rows using a WHERE clause to search for primary or foreign key values.

Saving the Map

You can use the SAVE command to save this Table Map in the Optim Directory. Then, the Table Map is available to other users and can be used for other Compare Definitions when specified as the operand of the APPLY command.

If you do not explicitly save the Table Map, it is stored only in the current Compare Definition and is not available to other users or processes.

Column Maps

By default, columns whose names match and attributes are compatible are compared. When this default is inadequate, you can specify Column Maps for any pair of compared tables. These maps are used to match unlike named columns and eliminate columns from the comparison. However, the following restrictions apply:

Mapped columns must be defined with compatible data types.
You cannot eliminate a column that is part of the match key.
If you eliminate a column that is part of a relationship, that relationship cannot be used to mark related changes or find orphans.

For details, see Column Maps.

Specify the Column Map Name

You can type the Column Map name in the provided area, request a selection list of Column Maps using the LIST MAPS command, or use the POPULATE command to automatically insert the Column Map names. You can create a new Column Map by specifying a new name. (Compare will display a confirmation prompt and, if the name is correct, invoke the Column Map editor.) You can edit an existing Column Map using the MAP command.

You can define a Column Map to be used by the current Compare Definition by specifying the Column Map name as LOCAL. Compare assumes that you are going to create the Column Map; therefore, the Column Map editor panel is automatically displayed and populated with the names of the columns that match in name and have compatible data types from each source table. You can edit appropriately.

POPULATE command: You can use the POPULATE command to automatically insert the names of Column Maps stored in the Directory for each pair of tables to be compared. If the columns in the Column Map are not present in the paired tables, a diagnostic is displayed.

Specifications Complete: When you have completed specifying the Table Map information, use END to proceed. Compare analyzes the available relationships on the two sources. Typically all relationships can be used automatically and the Specify COMPARE Parameters and Execute panel is displayed. This is the same panel that is displayed when Option 3 PERFORM is selected on the COMPARE Process menu, see Specify Compare Parameters and Execute Panel for information.