MIRROR_COMPARE_LIBRARY procedure
The MIRROR_COMPARE_LIBRARY procedure returns a result table and an
optional result set containing one or more rows for each object in the specified library that is
different from the same object on the other node. The library is included in the
comparison.
The objects that are compared are those that return a value that is not EXCLUDE or INELIGIBLE from the QSYS2.CHECK_REPLICATION_CRITERIA function. For replication-eligible objects, comparison operations can be performed for the definitional attributes of the object, the data contained in the object, or both. Only *FILE, *USRIDX, *USRSPC, *DTAQ, and *OUTQ 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.
- library-name
- A character or graphic string expression that identifies the library to compare.The following object types in the library will be compared.
- *DTAARA
- Data area
- *DTAQ
- Data queue
- *FILE
- Database file
- *JOBD
- Job description
- *JOBQ
- Job queue
- *JRN
- Journal
- *LIB
- Library
- *OUTQ
- Output queue
- *PGM
- Program
- *SQLPKG
- SQL package
- *SQLUDT
- SQL user-defined type
- *SQLXSR
- SQL XML schema repository
- *SRVPGM
- Service program
- *USRIDX
- User index
- *USRSPC
- User space
If library-name is QSYS, only the following object types are compared:- *AUTL
- Authorization list
- *ENVVAR
- System-level environment variable
- *FCNUSG
- Function usage
- *SECATR
- Security attribute
- *SYSVAL
- System value
- *USRPRF
- User profile
- compare-attributes
- A character or graphic string expression that indicates whether the replication-eligible
attributes are compared for each object.
- 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 *FILE,
*USRIDX, *USRSPC, *DTAQ, and *OUTQ objects. compare-data is ignored
for all other objects.
- NO
- The data is not compared for an object.
- QUICK
- The data is compared for *FILE, *USRIDX, *USRSPC, *DTAQ, and *OUTQ 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 *FILE, *USRIDX, *USRSPC, *DTAQ, or *OUTQ 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 includes rows for miscompare details 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 of a miscompare. The result set only includes the rows added to the result file from this call to the MIRROR_COMPARE_LIBRARY procedure.
The table that is created and the result set that is returned contain the following columns:
| 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.
|
| 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:
Contains the null value when COMPARE_RESULT is ERROR. |
| 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');