MIRROR_COMPARE_LIBRARY procedure

The MIRROR_COMPARE_LIBRARY procedure returns a result table and an optional result set containing one or more rows for the library and for each object in the specified library that is different from the same object on the other node.

For each object being compared, comparison operations can be performed for the definitional attributes of the object, the data contained in the object, or both. Only *DTAQ, *FILE, *OUTQ, *USRIDX, and *USRSPC objects support data comparisons. A value of YES or QUICK must be specified for at least one of the compare-attributes or compare-data parameters. For more information about object comparisons, see MIRROR_COMPARE_OBJECT table function.

Authorization: The privileges held by the authorization ID of the statement must include *ALLOBJ special authority. For the authority needed to use this function, see Authorization.

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 QIBM_DB_SQLADM, function usage authority.

The schema is QSYS2.

library-name

A character or graphic string expression that identifies the library to compare.

compare-attributes

A character or graphic string expression that indicates whether the attributes are compared for each object. The attributes being compared for an object varies based upon the object type. Not all attributes for an object are eligible to be compared.

NO: The attributes are not compared for an object.
QUICK: The attributes are compared for an object until the first difference is found. Only a single row is returned for an object if any differences are detected. This option is faster because a complete list of differences for an object is not returned.
YES: All the attributes are compared for an object. 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 *DTAQ, *FILE, *OUTQ, *USRIDX, and *USRSPC objects. compare-data is ignored for all other objects.

NO: The data is not compared for an object.
QUICK: The data is compared for *DTAQ, *FILE, *OUTQ, *USRIDX, and *USRSPC objects 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 for an object is not returned.
YES: All the data is compared for a *DTAQ, *FILE, *OUTQ, *USRIDX, and *USRSPC object. 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 for comparing the object.

If the Db2® Symmetric Multiprocessing (SMP) feature is not installed, an error is issued if the value is not NONE.

2-256: The maximum degree of parallelism to be used.
NONE: No parallelism will be used. This is the default.

parallel-jobs

An integer value that specifies the maximum number of jobs that can be run at one time to compare individual objects in a library. The default is 1.

result-library

A character or graphic string expression that identifies the name of the library to contain the details from the compare. The library must exist. If this parameter is omitted, QTEMP is the default.

result-file

A character or graphic string expression that identifies the system name of the file to contain the details from the compare. The result file includes rows that contain details for any differences found during the compare and any additional rows returned when verbose-log has a value of YES. If this parameter is omitted, QIBM_MCOMP is the default. If the file already exists, new rows are inserted into the file.

verbose-log

A character or graphic string expression that indicates whether to return at least one row for each object that is compared.

NO: Rows are returned only for objects that do not compare as identical. This is the default.
YES: A row is returned for every object that is compared.

result-set

A character or graphic string expression that indicates whether result rows are returned to the caller using a result set cursor.

NO: Only the result file identified by result-library and result-file is returned. This is the default.
YES: A result set cursor is returned in addition to the result file identified by result-library and result-file. A result set is returned if there is at least one instance where a difference is detected during the compare for an object. The result set only includes the rows added to the result file from this call to the MIRROR_COMPARE_LIBRARY procedure.

object-type-list

A character or graphic string expression containing one or more system object types separated by a comma or blanks that identify the objects in library-name to compare. The default for this parameter is *ALLMIRROR.

The following special values are allowed for object-type-list.

*ALL

All system object types are compared.

*ALLMIRROR

All replication-eligible objects that return a value that is not EXCLUDE or INELIGIBLE from the QSYS2.CHECK_REPLICATION_CRITERIA function are compared. This includes the *LIB library object for library-name.

If library-name is QSYS, only the following replication-eligible object types are allowed to be compared:

*AUTL: Authorization list
*ENVVAR: System-level environment variable
*FCNUSG: Function usage
*SECATR: Security attribute
*SYSVAL: System value
*USRPRF: User profile

*ALLNONMIRROR

All non-mirrored objects that return an EXCLUDE or INELIGIBLE value from the QSYS2.CHECK_REPLICATION_CRITERIA function are compared.

If library-name is QSYS, only the following object types are allowed to be compared:

*ENVVAR: System-level environment variable
*SYSVAL: System value

The table that is created and the result set that is returned contain the following columns:

Table 1. MIRROR_COMPARE_LIBRARY result table
Column Name	System Column Name	Data Type	Description
COMPARE_TIME	COMP_TIME	TIMESTAMP	The timestamp when this object was compared.
LIBRARY_NAME	LIBNAME	VARCHAR(10)	The library containing the object.
OBJECT_NAME	OBJNAME	VARCHAR(128) Nullable	The name of the object. Contains the null value when `OBJECT_TYPE` is `*SECATR` or when `COMPARE_RESULT` is `ERROR`.
OBJECT_TYPE	OBJTYPE	VARCHAR(8) Nullable	The system object type.
COMPARE_RESULT	RESULT	VARCHAR(9)	The result of the compare. COMPARED No difference is found and verbose-log is set to `YES`. DIFFERENT The comparison identified differences between the nodes. One or more rows are returned for a single object depending on the type of compare (YES or QUICK) that was requested. The `ATTRIBUTE_NAME`, `VALUE_ON_PRIMARY`, and `VALUE_ON_SECONDARY` columns contain details about the difference. ERROR The comparison failed. One example of a failure is when the object only exists on one node. The `COMPARE_SQLSTATE` and `COMPARE_ERROR_TEXT` columns contain extended details about the failure.
ATTRIBUTE_NAME	ATTRIBUTE	VARGRAPHIC(512) CCSID 1200 Nullable	The name of object attribute or the relative record number (RRN) that is not identical. Contains the null value when `COMPARE_RESULT` is `COMPARED` or `ERROR`.
VALUE_ON_PRIMARY	PRI_VALUE	VARGRAPHIC(2048) CCSID 1200 Nullable	The value from the primary node. When a compare using the `QUICK` option detects a difference, contains the value `DIFFERENT`. Contains the null value when `COMPARE_RESULT` is `COMPARED` or `ERROR`
VALUE_ON_SECONDARY	SEC_VALUE	VARGRAPHIC(2048) CCSID 1200 Nullable	The value from the secondary node. When a compare using the `QUICK` option detects a difference, contains the value `DIFFERENT`. Contains the null value when `COMPARE_RESULT` is `COMPARED` or `ERROR`.
IASP_NAME	IASP_NAME	VARCHAR(10) Nullable	Name of the independent ASP group where this object is stored. Contains either the name of the ASP group or the following special value: *SYSBAS The object is stored in the system ASP (ASP 1) or any basic user ASPs (ASPs 2-32). Contains the null value when `COMPARE_RESULT` is `ERROR`.
INCLUSION_STATE	INC_STATE	VARCHAR(10) Nullable	The existing replication inclusion state for this object. DEFINITION The object is included in replication. Only the objects definitional attributes are replicated. EXCLUDE The object is excluded from replication. INCLUDE The object is included in replication. Both the objects definitional attributes and data are replicated. INELIGIBLE The object is not eligible to be included in replication. Either the object-type or an attribute of the object identified by iasp-name, library-name, object-type, and object-name makes it ineligible. Contains the null value if the inclusion state is not available.
COMPARE_SQLSTATE	COMP_STATE	CHAR(5)	The SQLSTATE value returned for this compare.
COMPARE_ERROR_TEXT	ERROR_TEXT	VARCHAR(2000) Nullable	Error information that is returned as part of a non-zero SQLSTATE. Contains the null value if no error information is available.

Examples

Compare the following replicated object types: *AUTL, *USRPRF, *SYSVAL, *ENVVAR, *FCNUSG, *SECATR. They are requested by specifying the library name as QSYS.

CALL QSYS2.MIRROR_COMPARE_LIBRARY(LIBRARY_NAME => 'QSYS',
                                  RESULT_SET => 'YES', 
                                  RESULT_LIBRARY => 'DB2M_COMP',
                                  RESULT_FILE => 'JUNE262018');

Compare all the objects in APPLIB, returning at least one row for each object. Do a complete attribute compare and a quick data compare. If the data is mismatched, a deeper data compare can be requested for the objects with differences.

CALL QSYS2.MIRROR_COMPARE_LIBRARY(LIBRARY_NAME => 'APPLIB',
                                  COMPARE_ATTRIBUTES => 'YES',
                                  COMPARE_DATA => 'QUICK',
                                  VERBOSE_LOG => 'YES', 
                                  RESULT_LIBRARY => 'DB2M_COMP',
                                  RESULT_FILE => 'JULY112020');