MIRROR_COMPARE_OBJECT table function

Edit online

The MIRROR_COMPARE_OBJECT table function returns differences between the source and target nodes for the object identified by object-type, library-name, and object-name. The object does not need to be a currently replicated object.

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. For the object being compared, a value of YES or QUICK must be specified for at least one of the compare-attributes or compare-data parameters.

When compare-attributes is YES:

One row is returned for each attribute that is different between the nodes.
The value contained in a *DTAARA object is considered an attribute.

When compare-data is YES:

For a *FILE object, one row is returned for each row of data that is different between the nodes. The relative record number (RRN) is returned to identify the row.
- Files with active row or column access control, files with read triggers, and files in check pending state are not eligible for data comparison. A result row will be returned to indicate that data comparison was not performed.
- 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.
For a *USRIDX object, one row is returned for each entry in the user index that is different between the nodes.
For a *USRSPC object, the row returned indicates whether the user space is different between the nodes.
For a *DTAQ object, one row is returned for each entry in the data queue that is different between the nodes.
For a *OUTQ object, one row is returned for each spooled file that is different between the nodes. The comparison uses the information returned by the QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC view. The content of the spooled file is not compared.

When compare-data or compare-attributes is QUICK:

A single row is returned indicating that at least one difference exists for the object between nodes. No additional details about the type of difference is included in the row returned.

If no differences are found, no rows are returned. If the object is not eligible to be compared, no rows are returned along with a warning SQLSTATE of '01H53'. If the object does not exist on both nodes, no rows are returned along with an error SQLSTATE of '42704'. If the nodes are not communicating, no rows are returned along with an error SQLSTATE of '560DE'.

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.

object-type

A character or graphic string expression that identifies the object type.

Any system object type can be specified. However, only the following object types are instrumented for comparison operations. All others will return no rows and a warning SQLSTATE of '01H53'.

*AUTL: Authorization list. library-name must be QSYS or must be omitted.
*DTAARA: Data area, including SQL sequence attributes
*DTAQ: Data queue
*ENVVAR: System-level environment variable. library-name must be QSYS or must be omitted.
*FCNUSG: Function usage. library-name must be QSYS or must be omitted.
*FILE: Database file
*JOBD: Job description
*JOBQ: Job queue
*JRN: Journal
*LIB: Library
*OUTQ: Output queue
*PGM: Program, including SQL routine attributes
*SECATR: Security attribute. library-name must be QSYS or must be omitted. object-name must be null or must be omitted.
*SQLPKG: SQL package
*SQLUDT: SQL user-defined type
*SQLXSR: SQL XML schema repository
*SRVPGM: Service program, including SQL routine attributes
*SYSVAL: System value. library-name must be QSYS or must be omitted.
*USRIDX: User index
*USRPRF: User profile. library-name must be QSYS or must be omitted.
*USRSPC: User space

library-name

A character or graphic string expression that identifies the library name for the object. If this parameter is omitted, QSYS is the default.

object-name

A character or graphic string expression that identifies the system name of the object to compare. For *FCNUSG, the name is the Function ID. An object-name is required for all object types except *SECATR; object-name is ignored for the *SECATR object type. If this parameter is omitted, null is the default.

compare-attributes

A character or graphic string expression that indicates whether the replication-eligible attributes are compared for the object. Object type other than *FILE, *USRIDX, *USRSPC, *DTAQ, or *OUTQ must specify YES or QUICK.

NO: The attributes are not compared for the object.
QUICK: The attributes are compared for the object 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 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 a *FILE, *USRIDX, *USRSPC, *DTAQ, or *OUTQ object. compare-data is ignored when object-type is not *FILE, *USRIDX, *USRSPC, *DTAQ, or *OUTQ.

NO: The data is not compared for the object.
QUICK: The data is compared for a *FILE, *USRIDX, *USRSPC, *DTAQ, or *OUTQ object 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 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.

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

Table 1. MIRROR_COMPARE_OBJECT table function
Column Name	Data Type	Description
ATTRIBUTE_NAME	VARGRAPHIC(512) CCSID 1200	The name of object attribute or the relative record number (RRN) that is not identical.
VALUE_ON_PRIMARY	VARGRAPHIC(2048) CCSID 1200	The value from the primary node. When a compare using the `QUICK` option detects a difference, contains the value `DIFFERENT`.
VALUE_ON_SECONDARY	VARGRAPHIC(2048) CCSID 1200	The value from the secondary node. When a compare using the `QUICK` option detects a difference, contains the value `DIFFERENT`.

Examples

List all the replication-eligible attributes that are different between the two nodes for output queue MYOUTQ in library APPLIB1. The content of the spooled files in the output queue will not be compared.

SELECT * FROM TABLE(QSYS2.MIRROR_COMPARE_OBJECT(
                                       OBJECT_TYPE  => '*OUTQ',
                                       LIBRARY_NAME => 'APPLIB1',
                                       OBJECT_NAME  => 'MYOUTQ', 
                                       COMPARE_ATTRIBUTES => 'YES',
                                       COMPARE_DATA => 'NO'))
ORDER BY ATTRIBUTE_NAME;

For file TEST_TABLE in library TESTLIB, find all the data rows that are different between the two nodes. The file attributes will not be compared.

SELECT * FROM TABLE(QSYS2.MIRROR_COMPARE_OBJECT(
                                       LIBRARY_NAME => 'TESTLIB',
                                       OBJECT_NAME  => 'TEST_TABLE',
                                       OBJECT_TYPE  => '*FILE',
                                       COMPARE_DATA => 'YES',
                                       COMPARE_ATTRIBUTES => 'NO'));