VALIDATE_PENDING_REPLICATION_CRITERIA table function

The VALIDATE_PENDING_REPLICATION_CRITERIA table function returns the set of Replication Criteria List (RCL) rules that are affected by a given apply-label. The list of rules returned includes all pending replication criteria rules that match the apply-label and any active rules that will be affected if the apply-label is committed.

Each row is either an active or a pending rule in the RCL. All pending rules are validated to ensure no failures would occur if the apply-label group of pending rules is committed. If a failure is detected for a pending rule, information about the error is returned in the SQLSTATE, MESSAGE_ID, MESSAGE_TEXT, and MESSAGE_SECOND_LEVEL_TEXT columns for that rule. This failure detail can be used to understand why a pending apply-label cannot be committed.

Authorization: The privileges held by the authorization ID of the statement must include the following¹:

For the authority needed to use this procedure, see Authorization.
*EXECUTE authority on the QSYS2 library.
*OBJOPR and *READ authority on QSYS2/MIRROR_RCL

The schema is QSYS2.

iasp-name

A character or graphic string expression that identifies the name of the auxiliary storage pool (ASP) group for the apply-label. Can contain the following special value:

*SYSBAS: The apply-label is associated with the system ASP (ASP 1) and any basic user ASPs (ASPs 2-32).

apply-label

A character or graphic string expression that identifies a label associated with a group of pending replication criteria rules.

The apply-label identifies pending replication criteria rules that have been created by add or remove requests with this label.

*GENERATE: Use the apply-label generated by the system for this job. The generated value is always the same for a specific job. This behavior allows multiple rule changes within a job to be added or removed in PENDING state and then processed in a single operation. This is the default.
label-identifier: An apply-label value that identifies a set of pending rules.

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

Table 1. VALIDATE_PENDING_REPLICATION_CRITERIA table function
Column Name	Data Type	Description
RULE_IDENTIFIER	BIGINT	A system-assigned value that identifies this RCL rule. Identical active rules can exist with `RULE_SOURCE` values of `SYSTEM` and `USER`. These will have the same rule identifier.
APPLY_STATE	VARCHAR(14)	The state of the replication criteria rule in the RCL. ACTIVE This replication criteria rule is used to determine the inclusion state for objects. PENDING ADD This replication criteria rule is an addition to the RCL that has not been committed. The rule has no effect on determining the inclusion state for objects during active replication. PENDING REMOVE This replication criteria rule is a removal from the RCL that has not been committed. The rule has no effect on determining the inclusion state for objects during active replication. TRACKED This replication criteria rule is currently active and is used on this partition to determine the inclusion state for objects. The rule was applied while the partition was in tracked state so it must be reconciled with the RCL on the other node when node resynchronization occurs.
INCLUSION_STATE	VARCHAR(10)	The replication inclusion state for this rule in the RCL. DEFINITION Objects that best match this replication criteria rule will be replicated. Only the definition of the object is replicated. EXCLUDE Objects that best match this replication criteria rule are excluded from replication. INCLUDE Objects that best match this replication criteria rule are replicated. PROCESSING This rule is currently being committed to the RCL. Its final inclusion state has not been determined. Contains the null value when `APPLY_STATE` is `PENDING REMOVE`.
PRECEDENCE_ORDER	SMALLINT	System generated value that indicates the relative order, in ascending sequence, in which pending rules are applied. While all changes to the RCL will occur simultaneously, the precedence order is used to illustrate the logical steps any pending rule changes will be applied to the RCL. Identical rules can exist with `RULE_SOURCE` values of `SYSTEM` and `USER`. These will have the same precedence order.
APPLY_LABEL	VARCHAR(26)	The apply-label assigned to a pending rule. All pending rules with the same label will be applied as a group. Contains the null value if `APPLY_STATE` is not `PENDING ADD` or `PENDING REMOVE`.
IASP_NAME	VARCHAR(10)	Name of the independent ASP group associated with the replication criteria rule. Contains either the name of the ASP group or the following special value: *SYSBAS This rule is associated with objects stored in either the system ASP (ASP 1) and any basic user ASPs (ASPs 2-32).
LIBRARY_NAME	VARCHAR(10)	Name of the library associated with the replication criteria rule. Contains the null value if this replication criteria rule does not use the library name in its definition.
OBJECT_TYPE	VARCHAR(8)	Object type associated with the replication criteria rule. AUTL Authorization list DTAARA Data area DTAQ Data queue ENVVAR System-level environment variable FILE Database file JOBD Job description JOBQ Job queue JRN Journal OUTQ Output queue PGM Program SQLPKG SQL package SQLUDT SQL user-defined type SQLXSR SQL XML schema repository SRVPGM Service program SYSVAL System value USRIDX User index USRPRF User profile USRSPC User space Contains the null value if this replication criteria rule does not use the object type in its definition.
OBJECT_NAME	VARCHAR(128)	System name of the object, system-level environment variable, or system value associated with the replication criteria rule. Contains the null value if this replication criteria rule does not use the object name in its definition.
RULE_SOURCE	VARCHAR(6)	Indicates the source of this replication criteria rule. SYSTEM The rule was implicitly added by the IBM® i operating system. USER This rule was explicitly added by a user.
REMOVABLE	VARCHAR(3)	Indicates whether this replication criteria rule can be removed by using the QSYS2.REMOVE_REPLICATION_CRITERIA procedure. NO The rule cannot be removed. YES The rule can be removed.
RESTRICTED	VARCHAR(3)	Indicates whether this replication criteria rule restricts the addition of other rules. A value of `YES` can only occur when `RULE_SOURCE` is `SYSTEM`. NO This rule does not restrict adding related rules. YES This rule restricts adding related rules.
SQLSTATE	CHAR(5)	SQLSTATE value associated with the validation of this pending rule. When no warning or exception conditions are detected for a pending rule, `SQLSTATE` will have a value of '00000'. Otherwise, `SQLSTATE` will contain the value that would be returned if an attempt is made to commit this group of pending rules. Values that represent warnings are of the form '01xxx'.
MESSAGE_ID	VARCHAR(7)	The message identifier for the warning or failure that was detected during the validation of this pending rule. This rule is part of a group of pending rules identified by the apply-label parameter that will encounter an error when any replication service attempts to make use of this rule. The error must be resolved before the apply-label group of rules can be successfully committed to the RCL. Contains the null value if the `APPLY_STATE` column is `ACTIVE` or `TRACKED` or if this is a pending rule with no failure information.
MESSAGE_TEXT	VARGRAPHIC(1024) CCSID 1200	First level message text corresponding to `MESSAGE_ID`. Contains the null value if `MESSAGE_ID` is null.
MESSAGE_SECOND_LEVEL_TEXT	VARGRAPHIC(4096) CCSID 1200	Second level message text corresponding to `MESSAGE_ID`. Contains the null value if `MESSAGE_ID` is null.

Examples

List all the active and pending replication criteria rules associated with apply-label TESTGROUP1.


SELECT * 
FROM TABLE(QSYS2.VALIDATE_PENDING_REPLICATION_CRITERIA(IASP_NAME => '*SYSBAS', 
                                                       APPLY_LABEL => 'TESTGROUP1'))
ORDER BY PRECEDENCE_ORDER;

Determine if any of the pending replication criteria rules associated with apply-label TESTGROUP2 will prevent the rules from being committed because a warning or an exception condition is detected.
```
SELECT SQLSTATE, RULE_IDENTIFIER, LIBRARY_NAME, OBJECT_NAME, OBJECT_TYPE, 
       INCLUSION_STATE, MESSAGE_ID, MESSAGE_INFO, MESSAGE_INFO_TEXT 
FROM TABLE(QSYS2.VALIDATE_PENDING_REPLICATION_CRITERIA(IASP_NAME => '*SYSBAS',
                                                       APPLY_LABEL => 'TESTGROUP2')) 
WHERE SQLSTATE <> '00000' 
ORDER BY RULE_IDENTIFIER;
```
Only pending replication criteria rules that have detected a warning or an exception condition will be returned. These rules will prevent the group of pending replication criteria rules from being committed.

¹ For each database IASP, a separate RCL table exists in library QSYS2nnnnn/MIR_RCLnnn; the authorization rules apply to these tables as well.