Batch Utility

Edit online

Most options that may be specified using the online Compare panel are also available using the COMPARE batch processing statement. Use a COMPARE statement to compare two sets of related data. You can compare a set of rows that resides in database tables, VSAM or sequential files, or rows extracted previously and stored in an Extract or Archive File. Results of a comparison are stored in a Compare File or can be used to create a report.

COMPARE Batch Utility Statement

COMPARE
  COMPARE_FILE ( File Allocation Parameters )
   {COMPARE_DEFINITION_DEFINE  (parameters) ; |
    COMPARE_DEFINITION  group.user.name
     [ SOURCE_FILE_OVERRIDE dsname ]
     [ SOURCE_CID OVERRIDE { 1 | 2 } { cid | % }
     [MAX_EXTRACT_ROWS  nn ]
     [ UNLOAD_UTILITY { BMC | IBM }
         [ IMAGE_COPY
             [ MODE { L | A | B | D }
             [ DATE yyyy-mm-dd ]
             [ TIME hh.mm.ss ]
             [ DSNAME dsname ] ) ]
     [ PARTITIONS_n ( part1,...partn ) ] ]
        [OTHER_PARTITIONS_SAME {YES | NO }]]
     [ MATCH_KEY ( creatorid.tablename,oldkeycol,newkeycol ) ]
     [ REPORT_MODE { YES | NO | ONLY }
         REPORT_FILE ( File Allocation Parameters )
        [ REPORT_LINES nn ]
        [ REPORT_TABLE  [ creatorid. ] tablename]
        [ REPORT_TYPE  { SUMMARY | DETAIL | BOTH } ]
        [ REPORT_UNTRANSLATED YES | NO ]
        [ ROW_DISPLAY (DIRECT |RELATED |UNMATCHED |ORPHAN |DUPLICATE |UNUSED ) ]
        [ WRAP_LINES { EXTEND | WRAP } ] ]
        [ REPORT_FORMAT { COLUMN | SIDE_DIFF | SIDE_ALL | EXTERNAL} ]
            [ TABLE (cid.tablename,dsname)]
               [ STRING_DELIMITER 'c']
               [ ESCAPE_CHARACTER 'c']
               [ FIELD_DELIMITER ',']
               [ GENERATE_HEADER {YES | NO}]
                  [ BEGIN_LABEL label ]
                  [ END_LABEL label ]
                  [ HEADER_DELIMITER  ',']
                  [ MATCH_KEY_LABEL  'c' ]
                  [ REL_KEY_LABEL 'c' ]
                  [ USE_COLUMN_LABELS {YES | NO}]
               [ USE_DATA_LABELS {YES | NO}
               [ CHANGE_DATA_LABEL 'c' ]
               [ EQUAL_DATA_LABEL 'c']
               [ DATA_CCSID { 0 | PLAN | TABLE | ASCII | EBCDIC | UNICODE | nnnn } ]
               [ CONTROL_CCISD { 0 | PLAN | ASCII | EBCDIC | UNICODE | nnnn } ]
   }

COMPARE_FILE: Name of the Compare File that is to contain the results of the comparison. Use the keywords in File Allocation Parameters to name and allocate the Compare File. For details, refer to the File Allocation Parameters section in the Batch Utility Guide.; If you use the REPORT_MODE ONLY keyword, you need only specify the COMPARE_FILE dataset name.

Batch Utility Compare Definition Parameters: Use the following keywords to identify the Compare Definition and override or augment various parameters in it.

COMPARE_DEFINITION_DEFINE: The Optim™ online process generates this keyword when it creates a COMPARE job for batch execution.
When you create a COMPARE job outside of the Optim online process, the best practice is to use the COMPARE_DEFINITION keyword to refer to a named Compare Definition in the Optim Directory. Place the COMPARE_DEFINITION_DEFINE parameters within parentheses, with a semicolon after the last parenthesis.

COMPARE_DEFINITION

The name of the Compare Definition. COMPARE_DEFINITION must be included in the COMPARE statement and must precede any keywords that override parameters in the Compare Definition (for example, SOURCE_FILE_OVERRIDE_1).

group.user.name: The three-part Compare Definition name.

SOURCE_FILE_OVERRIDE

Override for the name of a file specified as a source in the Compare Definition. To override the Source 1 specification, use SOURCE_FILE_OVERRIDE_1. Use SOURCE_FILE_OVERRIDE_2 to override the Source 2 specification.

dsname: The fully qualified name of the Archive File, Extract File or Access Definition.

SOURCE_CID_OVERRIDE_{ 1 | 2 }

Override for the default creator ID for a source in the Compare Definition. To override the Source 1 specification, use SOURCE_CID_OVERRIDE_1. Use SOURCE_CID_OVERRIDE_2 to override the Source 2 specification.

cid

Creator ID to override the default Creator ID for the source.

If the source is an Extract File, you can use the wildcard (%) to use the default Creator ID defined in the Extract File.

This Creator ID applies to all tables that were defined with the initial default Creator ID in the Compare Definition. Tables in the Compare Definition not defined with the initial default Creator ID will not be altered by the specified Creator ID override. This override also changes the names of the tables in the relationships processed by Compare to use the override default Creator ID. If a relationship is not found for the updated table name, an error occurs when the compare is performed. If the source is an extract file, and a table initially defined in the Compare definition with default Creator ID is not in the extract file, the table will be skipped, a warning message will be produced, but the Compare job will continue.

MAX_EXTRACT_ROWS

The maximum number of rows that can be extracted from the source table. If this value is exceeded, the Compare process terminates. The default is the site option value for Maximum Extract Rows. The Compare Definition must specify a table or an Access Definition as a source. Use MAX_EXTRACT_ROWS_1 for Source 1 and MAX_EXTRACT_ROWS_2 for Source 2.

nn: The maximum number of rows to extract during the Compare process.

Use these parameters only for a Compare Definition that specifies a DB2® table or an Access Definition that includes a DB2 table as a source.

UNLOAD_UTILITY

The name of the unload utility used to retrieve the data. If omitted, DB2 is used to retrieve the data. This keyword is required to extract data from an image copy.

Use these keywords only for a Compare Definition that specifies a DB2 table or an Access Definition that includes a table as a source. Use UNLOAD_UTILITY_1 for Source 1 and UNLOAD_UTILITY_2 for Source 2.

BMC: Use the UNLOAD PLUS utility.

IBM®: Use the High Performance Unload utility.

Note: You must add the appropriate utility DD statements. Refer to the utility documentation for the required DD statements.

IMAGE_COPY

The image copy file(s) to be used as input for a source. Use this parameter only if a DB2 table or an Access Definition that includes a DB2 table has been specified as a source, and the UNLOAD_UTILITY parameter has been specified. If omitted, the DB2 database is used as input. IMAGE_COPY_1 is allowed only if UNLOAD_UTILITY_1 is used; IMAGE_COPY_2 is allowed only if UNLOAD_UTILITY_2 is used.

MODE

The image copy file to use:

L: Use the latest file.

A: Use the first file created on or after the specified DATE and TIME.

B: Use the first file created on or before the specified DATE and TIME.

D: Use the file named in DSNAME.

DATE: The date criteria for the image copy file in this format: yyyy-mm-dd.

TIME: The time criteria for the image copy file in this format: hh.mm.ss. If omitted, a value of 00.00.01 is assumed.

DSNAME: The fully qualified name of the image copy file.

PARTITIONS

The partitions to be processed by an unload utility. Valid only if the Start Table for the source is a partitioned DB2 table. Use PARTITIONS_1 for Source 1 and PARTITIONS_2 for Source 2. Omit this keyword to process all partitions.

(part1,...partn): The partition numbers. Partitions that are not specified are not compared.

OTHER_PARTITIONS_SAME

Use this operand to indicate that the partitions specified in the PARTITIONS operand for the Start Table should be applied to all tables in the Compare Definition.

YES: The partitions specified in the PARTITIONS operand for the Start Table will be applied to all tables in the Compare Definition.

NO: The partitions specified in the PARTITIONS operand will apply only to the Start Table. All partitions for all other tables in the Compare Definition will be processed. This is the default.

MATCH_KEY

Replace a key column in the Match Key for the specified table.

(creatorid.tablename,oldkeycol,newkeycol): The fully qualified tablename, old key column name, new key column name.

REPORT_MODE

Specify whether a report is produced.

YES: Produce a report as part of the Compare process.

NO: Do not produce a report (default).

ONLY: Produce a report from a Compare file created previously and specified in the COMPARE_FILE keyword.

REPORT_FILE: Name of the file for the report. Use the parameters in File Allocation Parameters to name and allocate the file. This keyword is required if REPORT_MODE is YES or ONLY.

REPORT_LINES

The number of lines per page for the report.

nn: Number of lines per page. Valid values are 0-99. The default is 57. Specify 0 to suppress all page breaks and title lines.

REPORT_TABLE

The name of a table for which the report is generated when multiple tables have been compared. Omit this keyword to include all tables.

[ creatorid .]tablename: The name of the table for which the report is generated. If the creator ID is omitted, all tables with the specified name are included, regardless of the creator ID.

REPORT_TYPE

The type of report to be produced.

SUMMARY: Produce a report containing summary information.

DETAIL: Produce a report of detailed information.

BOTH: Produce a report containing both summary and detailed information (default).

REPORT_UNTRANSLATED

Specify whether the column values in the detail report are translated into the plan CCSID or not.

YES: The column values are translated to the plan CCSID.
NO: The column values are displayed on three lines. Line 1 contains the value translated to the plan CCSID. Lines 2 and 3 contain a hexadecimal representation of the untranslated value.

ROW_DISPLAY

Rows that appear on a detailed report. If you omit this keyword, all rows are included. Operands must be enclosed in parentheses, separated by commas, and may appear in any order.

DIRECT: Include rows with the same Match Key value that have different values in one or more other columns.

RELATED: Include rows marked as having related changes.

UNMATCHED: Include rows with match key value that does not match any row from the other source.

ORPHAN: Include rows that do not have a parent.

DUPLICATE: Include rows that have duplicate match key values.

UNUSED: Include unused columns in the report.

WRAP_LINES

Option for rows that exceed the line width of the report file for a detailed report.

WRAP: Wrap the report data (default).

EXTEND: Increase the record length of the report file to fit the row.

REPORT_FORMAT

The format of a detailed report.

COLUMN: Report is in columnar format (default).

SIDE_ALL: Report is in sidelabels format and includes all columns.

SIDE_DIFF: Report is in sidelabels format and includes only changed columns and Match Key columns.

EXTERNAL: Report is in CSV (Comma Separated Values) format.

The following parameters are used only when REPORT_FORMAT=EXTERNAL is specified.

TABLE

Write the output for a specific table to an external file other than the file specified in the REPORT_FILE keyword. All file allocation parameters for the REPORT_FILE parameter, except dsname, apply to this file. You can use multiple TABLE parameters, provided each refers to a different table.

(cid.tablename,dsname): The fully qualified table name and data set name, enclosed in parentheses and separated by a comma.

STRING_DELIMITER: Specify a single character used to separate character literal values.

ESCAPE_CHARACTER: Specify a single character used to generate the value of a character normally used as a delimiter.

FIELD_DELIMITER: Specify a single character used to separate values in a row. A comma (“,”) is the default.

GENERATE_HEADER

Generate headers using the column names.

Y: Generate headers

N: Do not generate headers. This is the default.

BEGIN_LABEL: Place a label before the first column in the table. This parameter applies only when GENERATE_HEADER=Y. Specify “$table” to include the table name in the label.

END_LABEL: Specify a label to be placed after the last column in table. This parameter applies only when GENERATE_HEADER=Y. There is no default.

HEADER_DELIMITER: Character used to separate column headings. This parameter applies only when GENERATE_HEADER=Y. A comma (“,”) is the default.

MATCH_KEY_LABEL: Specify a single character to be placed before each column that was used as part of the Match Key between Source 1 and Source 2 during compare processing.. This parameter applies only when GENERATE_HEADER=Y.

REL_KEY_LABEL: Specify a single character to be placed before each column that was part of a relationship when tables were joined during compare processing. This parameter applies only when GENERATE_HEADER=Y.

USE_COLUMN_LABELS

Indicator for using DB2 column labels or column names in the header, if a header is generated. Specify:

Y: Use column labels.

N: Do not use column labels. Use column names. This is the default.

USE_DATA_LABELS

Specify whether to place a single character before the data of each column to indicate if the column data in Source 1 and Source 2 is equal or unequal.

Y: Use data labels.

N: Do not use data labels. This is the default.

CHANGE_DATA_LABEL: Specify a single character to be placed before column data that differs between Source 1 and Source 2. This parameter applies only when USE_DATA_LABELS=Y.

EQUAL_DATA_LABEL: Specify a single character to be placed before column data that is equal between Source 1 and Source 2. This parameter applies only when USE_DATA_LABELS=Y.

DATA_CCSID

Specifies the CCSID of the column data. Valid values are:

0: Use the CCSIDs of the Plan used to extract the source 1 data.
PLAN: Use the Compare Plan CCSIDs.
TABLE: Use the CCSIDs of the two source 1 table.
ASCII: Use the connected DB2 subsystem's ASCII CCSIDs.
EBCDIC: Use the connected DB2 subsystem's EBCDIC CCSIDs.
UNICODE: Use the connected DB2 subsystem's UNICODE CCSIDs.
nnnnn: Select an SBCS or MBCS CCSID. If there are MBCS or DBCS columns in the table an MBCS CCSID is required.

CONTROL_CCISD

Specifies the CCSID of the control information (that is, header, labels and delimiters). Valid values are:

0: Use the CCSIDs of the Plan used to extract the source 1 data.
PLAN: Use the Compare Plan CCSIDs.
ASCII: Use the connected DB2 subsystem's ASCII CCSIDs.
EBCDIC: Use the connected DB2 subsystem's EBCDIC CCSIDs.
UNICODE: Use the connected DB2 subsystem's UNICODE CCSIDs.
nnnnn: Select an SBCS or MBCS CCSID.

This example compares two sets of data defined by the Compare Definition PSTUSER.INV.CDQ4. The Extract File PSTUSER.ITEMS is used in place of the Extract File specified for Source 1 in the Compare Definition:

COMPARE_FILE (DSNAME PSTUSER.INVCOMP30)
   COMPARE_DEFINITION PSTUSER.INV.CDQ4
   SOURCE_FILE_OVERRIDE_1 PSTUSER.ITEMS