COMPARE_FILE table function

The COMPARE_FILE table function returns differences between the specified files, including SQL tables, physical files, and source physical files.

A value of YES or QUICK must be specified for at least one of the compare-attributes or compare-data parameters.

When comparing attributes, one row is returned for each attribute that is different between the files.

When comparing data, one row is returned for each row of data that is different between the files. The relative record number (RRN) is returned to identify the row. Files with multiple members, including partitioned tables and source physical files, where significant differences are found in the definition are not eligible for data comparison. All the member names must match. A result row will be returned to indicate that data comparison was not performed.

If a quick compare is requested, one row is returned if any difference is found. This compare option is faster because a complete list of differences is not returned.

If no differences are found, no rows are returned.

Authorization: The caller must have:

*EXECUTE authority on the library containing the file, and
*OBJOPR and *READ authority to the file.

The caller must have *ALLOBJ or *AUDIT special authority to compare the object auditing attribute for the files. If the caller does not have this authority, a difference in this value will not be reported.

To specify a parallel degree value other than NONE, the privileges held by the authorization ID of the statement must include *JOBCTL special authority or have QIBM_DB_SQLADM function usage.

The schema is QSYS2.

library1

A character or graphic string expression that identifies the library that contains file1. It must exist on the current server and cannot be QTEMP.

file1

A character or graphic string expression that identifies the first file for the compare. This must be the system name of the file.

library2

A character or graphic string expression that identifies the library that contains file2. It must exist on the server implicitly or explicitly identified by rdb2 and cannot be QTEMP.

If library2 is omitted, library1 is used.

file2

A character or graphic string expression that identifies the second file for the compare. This must be the system name of the file. The two files cannot be the same file object.

If file2 is omitted, file1 is used.

rdb2

A character or graphic string expression that identifies the relational database where library2 exists. If this parameter is omitted, library2 must exist on the current server.

compare-attributes

A character or graphic string expression that indicates whether attributes are compared for the file.

NO: The attributes are not compared for the file.
QUICK: The attributes are compared for the file until the first difference is found. Only a single row is returned if any differences are detected. This option is faster because a complete list of differences is not returned.
YES: All the attributes are compared for the file. A row is returned for each difference found. This is the default.

compare-data

A character or graphic string expression that indicates whether the data is compared for the file, including the relative record number (RRN) of each row.

NO: The data is not compared for the file.
QUICK: The data is compared for the file until the first difference is found. Only a single row is returned if any differences are detected. This option is faster because a complete list of differences is not returned.
YES: All the data is compared for the file. A row is returned for each difference found. This is the default.

parallel-degree

A character or graphic string that specifies the maximum degree of parallelism to use when comparing the file.

If the Db2® Symmetric Multiprocessing (SMP) feature is not installed, a warning is issued if the value is not NONE and the compare will not use parallelism.

2-256: The maximum degree of parallelism to be used. Using parallelism can cause the compare to complete sooner, but it will have an impact on system resources.
NONE: No parallelism will be used. This is the default.

The result of the function is a table containing rows with the format shown in the following table. All columns are nullable.

Table 1. COMPARE_FILE table function
Column Name	Data Type	Description
ATTRIBUTE_NAME	VARGRAPHIC(512) CCSID 1200	The name of file attribute or the relative record number (RRN) that is not identical.
FILE1	VARGRAPHIC(2048) CCSID 1200	The value for file1.
FILE2	VARGRAPHIC(2048) CCSID 1200	The value for file2.

Examples

Compare the attributes and data for file T1 between the TEST library and the PROD library.

SELECT * FROM TABLE(QSYS2.COMPARE_FILE(
                                       LIBRARY1=>'TEST', FILE1=>'T1', 
                                       LIBRARY2=>'PROD', FILE2=>'T1'));

Compare the data for file T1 between the TEST library and the PROD library. As soon as any difference is detected, stop the compare.

SELECT * FROM TABLE(QSYS2.COMPARE_FILE(
                                       LIBRARY1=>'TEST', FILE1=>'T1', 
                                       LIBRARY2=>'PROD', FILE2=>'T1', 
                                       COMPARE_ATTRIBUTES=>'NO',
                                       COMPARE_DATA=>'QUICK'));