Sample Reports

The following is a sample of a Compare File report in columnar format. For this sample, the data has been truncated.

8/11/2014                      Optim - COMPARE File Report

Compare File      : FOPDEMO.COMPARE.D419
File Created by   : Job FOPDEMO using SQLID FOPDEMO Optim for z/OS, File ID: CFFI0010
File Created on   : Aug 02, 2014 at 09:48 AM
Processed By      : Job FOPDEMO using SQLID FOPDEMO Optim for z/OS Release: 11.7.0
Processed On      : DB2 Subsystem TDB2
Report Printed on : Aug 02, 2014 at 09:48 AM from DB2 Subsystem TDB2
DB2 CCSIDs        : EBCDIC(290, 930, 300) ASCII(897, 932, 301) Unicode(367, 1208, 1200)
Number of Tables : 1

Source 1         : Extract File - PSTCBR.TESTJUL
  Created by     : Job PSTCBR using SQLID PSTCBR
  Created on     : 03 Aug 2014 at 09:40 AM on DB2 Subsystem TDB2

Source 2         : Extract File - PSTCBR.ALL829
  Created by     : Job PSTCBR using SQLID PSTCBR
  Created on     : 11 Aug 2014 at 02:38 PM on DB2 Subsystem TDB2

Table Statistics: 1:PSTSUPP.OPTIM_CUSTOMERS  2:PSTSUPP.OPTIM_CUSTOMERS
   Total Number of Merged Rows                 :    3520
   Total Number of Rows from Source 1          :    3520
   Total Number of Rows from Source 2          :      18
   Number of Unmatched Rows from Source 1      :    3502
   Number of Unmatched Rows from Source 2      :       0
   Total Number of Equal Rows                  :      18
   Total Number of Rows with Direct Changes    :       0
   Total Number of Rows with Related Changes   :     N/A
   Total Number of Orphan Rows on Source 1     :     N/A
   Total Number of Orphan Rows on Source 2     :     N/A
   Sets of Rows with Non-Unique Keys           :      0
   Total Number of S1 Rows with Non-Unique Keys:      0
   Total Number of S2 Rows with Non-Unique Keys:      0

Compare Match Options:

 Match Key Property  : Unique
 

The report's header information includes the name of the Compare File, the user that generated the report and, the time, and the number of tables involved in the process. The sources are listed, along with any pertinent information about each source.

This is followed by the requested report information for each table. In this example, the information is provided for the CUSTOMERS table, the first table listed in the Compare Process. Both summary and detail information has been requested.

Summary Information

The summary information is the same as that provided on the Compare Summary Selection List panel. If you request only summary information, the information for each table is provided one after the other in the order in which the tables were specified for Source 1. That is the order of the tables in the Access Definition or Extract File, whichever was used for Source 1.

Only the pertinent summary information is included. This report documents a Compare Process that included multiple tables; however, if only one pair of tables or a reference table is involved, statistics about related rows and orphan rows are not included. (For an explanation of this information, see Compare Summary Selection List.)

Details

The details, rows from the compared tables, are included after the summary information. This portion of the report is divided into the following:

Chg

Identifies rows containing changes, rows for which related rows contain changes, orphan rows, and duplicate match key rows, as follows:

D

Indicates that one or more columns are different, other than the match key columns.

R

Indicates that one or more columns in a related row are different, other than the match key columns.

U

Identifies rows that are orphans and the pairs of compared rows that result in a different set of related data.

Orphan rows in a table can only be determined when data from multiple tables is compared and the parent table is included.

Different sets of related data can result when matched rows contain data in a relationship column that results in joining to a different child.

/   |   \

Identifies rows with duplicate match keys. If only two rows contain duplicate values:

/  00001  Video Magic
\  00001  Video Mania

If three or more rows contain duplicate values:

/  00001  Video Magic
|  00001  Video Mania
\  00001  Video Maniac

Src

Identifies the source of each row as:

1

Source 1

2

Source 2

12

Source 1 and Source 2. The row is identical; therefore, included once.

The data is shown before this information. The match key columns are presented first, and are identified by plus signs following the heading. The compared columns are listed next, followed by the columns that are unique to Source 1 and Source 2. The column headings defined for Source 1 are used for the compared columns. The column headings for columns unique to a source are prefixed with the source identifier 1: or 2:, as appropriate. (For example, the heading for a column named CUST unique to Source 1 is displayed as 1:CUST.)

Unchanged rows (rows in which all compared column values match) are included once. Both the Source 1 and Source 2 versions of changed rows are included. Any rows unique to one source are included and the source is identified. (For a more complete explanation and examples of how the change and source flags are generated, see Processing Flow.)

Within each pair of changed rows, the changed columns are identified by the symbol >. For example, in the following pair of compared rows, the second and third columns are different.

Chg       Src          CUST_ID      CUSTNAME        ADDRESS
------   -----------   ++++++++     -------------   --------------  
 D        1            00100        >CinemaMagic    >726 West State Street
 D          2          00100        >CineMagic      >123 East State Street

This format simplifies interpreting the results of the comparison.

The sidelabels Compare File print report in the following figure is based on the same Compare Process used for the columnar example. However, sidelabels format is especially useful for focusing directly on the changes. Therefore, the report parameters limit this sidelabels report to include only unmatched rows and rows with direct and related changes. For unmatched rows, all columns are included. For those rows with direct and related changes, only the columns containing differences are included. The Match Key columns are always included for every row in the report.

                      Optim - COMPARE File Report

Compare File      : PSTCBR.AUGELV
File Created by   : Job FOPDEMO using SQLID FOPDEMO Optim for z/OS, File ID: CFFI0010
File Created on   : Aug 02, 2014 at 09:48 AM
Processed By      : Job FOPDEMO using SQLID FOPDEMO Optim for z/OS Release: 11.7.0
Processed On      : DB2 Subsystem TDB2
Report Printed on : Aug 02, 2014 at 09:48 AM from DB2 Subsystem TDB2
DB2 CCSIDs        : EBCDIC(290, 930, 300) ASCII(897, 932, 301) Unicode(367, 1208, 1200)
Number of Tables  : 5

Source 1         : Extract File - PSTCBR.TESTJUL
  Created by     : Job PSTCBR using SQLID OPTIM
  Created on     : 03 Aug 2014 at 10:55 AM on DB2 Subsystem TDB2

Source 2         : Extract File - PSTCBR.ALL829
  Created by     : Job PSTCBR using SQLID OPTIM
  Created on     : 03 Aug 2014 at 11:52 AM on DB2 Subsystem TDB2

Table Statistics: 1:PSTSUPP.OPTIM_CUSTOMERS  2:PSTSUPP.OPTIM_CUSTOMERS
   Total Number of Merged Rows                 :    3520
   Total Number of Rows from Source 1          :    3520
   Total Number of Rows from Source 2          :      18
   Number of Unmatched Rows from Source 1      :    3502
   Number of Unmatched Rows from Source 2      :       0
   Total Number of Equal Rows                  :      18
   Total Number of Rows with Direct Changes    :       0
   Total Number of Rows with Related Changes   :     N/A
   Total Number of Orphan Rows on Source 1     :     N/A
   Total Number of Orphan Rows on Source 2     :     N/A
   Sets of Rows with Non-Unique Keys           :      0
   Total Number of S1 Rows with Non-Unique Keys:      0
   Total Number of S2 Rows with Non-Unique Keys:      0

Compare Match Options:

  Match Key Property    : Unique

Only the detail information is affected by the columnar and sidelabel format. Therefore, for details about the report headers and summary information refer to the discussion provided for the columnar format sample.

Details

Details are presented after the summary information. The following headings are provided for the column information: Column Name, Src, and Data.

Column Name

The column name is displayed only once, regardless of whether the data is unique, common, or different between sources.

Src

Indicates the source of the column data. Possible values are:

1

Source 1 only.

2

Source 2 only.

12

Common to Source 1 and Source 2.

KEY

Match Key column.

Data

The data is presented in 50-byte segments. That is, if the data exceeds 50 characters, the next 50 characters are placed on the next line. As many lines as are needed to include all of the data in the column are used.

When all columns are included, the data in the common columns are displayed once and Src contains 12.

The Match Key columns are listed first and are identified by KEY in Src.

A separator line is shown following these headings and after each set of columns from a single row. The following row information is provided:

Source

Indicates the row source. Possible values are:

1

Row is from Source 1. There is no matching row in Source 2.

2

Row is from Source 2. There is no matching row in Source 1.

12

Row is a common row. All compared columns in Source 1 and Source 2 match.

1/2

Compared rows have differences in columns other than Match Key columns.

Use the information in Src to determine the source of any unmatched column values.

Changes

Indicates the type of change. Possible values are:

blank

Row is unique to one source.

D

One or more columns are different, other than the match key columns.

R

One or more columns in a related row are different, other than the match key columns.

U

Identifies rows that are orphans and the pairs of compared rows that result in a different set of related data.

Orphan rows in a table can only be determined when data from multiple tables is compared and the parent table is included.

/   |   \

Identifies rows with duplicate Match Keys. See Details for additional information.

If you want to review the compared data in a relational format, use the Browse option on the COMPARE Process menu. The same information is provided, but you can display the related data from other tables to identify the related changes and review the possible different sets of related data for each source. (See Browse Compare File for information about browsing the compared data online.)