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 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 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:
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.
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 an Extract File, the original source type may be unavailable; therefore, the type is EXISTS.
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.)
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.
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, an asterisk 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.
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.
For a multiple compare, match options can be defined for each pair of tables by typing MOPT (Match Options) on the command line, placing the cursor on the source 2 table name, and then pressing Enter. The Optim™ solution displays the Compare Match Options panel, as described inMatch Options Panel.
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.
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.
For details, see Column Maps.
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.
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.
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.