Compare Process

Define the Sources

Many options are provided for defining the sources of the data. You can use a new or existing Access Definition and Compare will extract the data as part of the process. Alternatively, you can use the data previously extracted and stored in an Extract File on disk. The data to be compared can reside in a single table or in multiple related tables.

To compare data from IMS™, VSAM or sequential files, you must create Table Definitions. For IMS data, see Defining a Table for IMS Data and for VSAM or sequential data, see Defining a table for VSAM or Sequential File Data.

Store and Browse the Results

The results of the comparison are stored in a Compare File. You can browse the results online using Compare, or generate a report and print or store the report in a sequential file. A variety of report options enable you to control the contents.

Processing Flow

The Compare Process performs a series of steps based on your specifications. The following documents the steps that are performed when multiple tables are compared. A single table comparison only performs the first two steps--retrieving the data and comparing a pair of tables.

Retrieve the Data

Based on your specifications, Compare will obtain the data for each source from an Extract or Archive File, IMS or DB2^® tables, VSAM or sequential file.

Compare Each Pair of Tables using the Match Key

The rows in each pair of tables are compared independently. Compare uses the “match key” to determine the row from Source 1 that is compared to a row from Source 2. The match key consists of one or more corresponding columns from each source. These match key columns are used to determine if two rows are the same or different. That is, when the values in the match key columns are the same, the remaining columns in the pair of rows are compared.

Compare attempts to use the primary key defined for a source table as the match key, evaluating the primary keys for each pair of tables, as follows:

• If both tables have the same primary key, the primary key is used.
• If both tables have a primary key and one primary key consists of columns common to both, the common primary key is used.
• If both tables have a primary key and either is valid as the match key, you are prompted to select one.
• If neither table has a primary key or if a Column Map does not include a primary key column, you are prompted to define the match key, which is stored in the Compare Definition. The steps used to define a match key are the same as those used to define a primary key.

You can also define a match key that you can use with the current Compare Process or save in the Compare Definition. For a single table comparison, use the Specify COMPARE Sources panel to indicate a user-defined match key will be used. For a multiple table comparison, use the MKEY command in the COMPARE Process Table Map to indicate the tables that will use a user-defined match key. After completing each panel, you will be prompted to define the match key.

The columns in a match key can correspond directly by name and compatible attributes (for example, CUST_ID in both tables) or the correspondence can be established using a Column Map. (When a Column Map is used, the names may be different but the attributes must be compatible.)

Processing Fixed Arrays

• the same number of elements
• the same number of levels (dimensions)
• compatible data types and lengths

Command ===>                                                                    
                                                                                
User defined I/O Exit   ===>                                         Row 1 of 5 
Cmd Level/Field Name              Type  Len  Occur Column Name                  
--- ------------------------------ --- ----- ----- ---------------------------- 
*** ********************************** TOP ************************************ 
___  1 POLICY                            70        POLICY                       
___   5 POLICY-ID                  CHR    2        POLICY_ID                    
___   5 POLICY-INFO                      17     4  POLICY_INFO                  
___    10 POLICY-NAME              CHR    8        POLICY_NAME                  
___    10 POLICY-RATE              CHR    3     3  POLICY_RATE                  
*** ******************************** BOTTOM *********************************** 

When you use this table in a comparison and display the results, the column headings will appear as shown in the following example:

------------------- Optim: Browse (Source 1 Names Shown)  ----------------------    
Command ===>                                                                        
                                                         SUBSYS: DD9F               
Cmd Chg Src == Table: TESTNEST(T1) =================================================
            POLICY_ID POLICY_NAME_1 POLICY_RATE_1_1 POLICY_RATE_2_1 POLICY_RATE_3_1 
            --CH(2)-- ---CH(8)----- -----CH(3)----- -----CH(3)----- -----CH(3)----- 
*** ********************************************************************************
___      12    01       POLICY A          100             200             300       
*** ********************************************************************************

In the example, the column heading displayed as POLICY_RATE_2_1 is for the column defined in the Legacy Table as POLICY_RATE, which is the second element of the first table dimension.

You can use the generated column name with functions that accept a column name as input, such as FIND or LOCK.