INSPECT_REPLICATION_CRITERIA table function

The INSPECT_REPLICATION_CRITERIA table function returns one row for each rule that applies to the object identified by the iasp-name, library-name, object-type, and object-name parameters. The list of rules generated with and without a pending apply-label can be examined to understand the effect of applying a pending rule.

Each row describes a rule in the Replication Criteria List (RCL) that is used to determine if the specified object will be included or excluded from replication through Db2® Mirror.

This function identifies which RCL rules are applicable for the specified object. Generally, up to four rows can be returned for an object in a priority order. The rows that can be returned match the following rule combinations.

The rule that matches iasp-name, library-name, object-type, and object-name.
The rule that matches iasp-name, library-name, and object-type.
The rule that matches iasp-name and library-name.
The rule that matches iasp-name. This rule is always returned, reflecting the default inclusion state.

It is possible to have identical RCL rules with RULE_SOURCE values of SYSTEM and USER. In this case, additional rows would be returned that have the same rule identifier and precedence order.

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. Can contain the following special value:

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

library-name

A character or graphic string expression that identifies the library name.

object-type

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

Any system object type can be specified. However, only the following type values are eligible for replication. All others will return a row for this object with an INCLUSION_STATE column value of INELIGIBLE.

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

object-name

A character or graphic string expression that identifies the system name of the object, system-level environment variable, or system value. When the object-type is *SECATR, object-name must be omitted. In all other cases, object-name is required.

apply-state

A character or graphic string expression that indicates the replication criteria rules to use for evaluating the inclusion state for the object.

ACTIVE: Only consider active replication criteria rules. This is the default.
PENDING: Consider active replication criteria rules and any pending replication criteria rules with a label of apply-label.

apply-label

A character or graphic string expression that identifies a label for the pending replication criteria rules to be considered.

The apply-label identifies pending replication criteria rules that have been created by add or remove requests with this label. Rules with this label will be considered when apply-state has a value of PENDING.

*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. INSPECT_REPLICATION_CRITERIA table function
Column Name	Data Type	Description
RULE_IDENTIFIER	BIGINT	A system-assigned value that identifies this RCL rule. Identical rules can exist with `RULE_SOURCE` values of `SYSTEM` and `USER`. These will have the same rule identifier. Contains the null value if `INCLUSION_STATE` is `INELIGIBLE` or `RESTRICTED`.
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. 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. RESTRICTED The operation against the object on this node requires replication, but the specified object is restricted from replication. Either the object-type or an attribute of the object identified by iasp-name, library-name, object-type, and object-name makes it restricted. Contains the null value when `APPLY_STATE` is `PENDING REMOVE`.
PRECEDENCE_ORDER	SMALLINT	Indicates the order, in ascending sequence, in which this rule would be applied for the specified object. If the rule with precedence order of 1 is removed from the RCL, the rule numbered 2 would be used for replicating the object. 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 FCNUSG Function usage FILE Database file JOBD Job description JOBQ Job queue JRN Journal OUTQ Output queue PGM Program SECATR Security attribute 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.

Examples

List all the active replication criteria rules that are used to determine the inclusion state for table MYTABLE in library APPLIB1.

SELECT * FROM TABLE(QSYS2.INSPECT_REPLICATION_CRITERIA(
                               IASP_NAME    => '*SYSBAS',
                               LIBRARY_NAME => 'APPLIB1',
                               OBJECT_TYPE  => '*FILE', 
                               OBJECT_NAME  => 'MYTABLE'))
ORDER BY PRECEDENCE_ORDER;

Determine whether there is a RESTRICTED attribute that would prevent the addition of a new rule for the QPFRADJ system value.

SELECT DISTINCT PRECEDENCE_ORDER AS ORDER, IASP_NAME AS IASP, 
                LIBRARY_NAME AS LIBRARY, OBJECT_TYPE AS TYPE, 
                OBJECT_NAME AS NAME, RESTRICTED, REMOVABLE
    FROM TABLE(QSYS2.INSPECT_REPLICATION_CRITERIA(
                            IASP_NAME => '*SYSBAS',
                            LIBRARY_NAME => 'QSYS',
                            OBJECT_TYPE => '*SYSVAL',
                            OBJECT_NAME => 'QPFRADJ'))
ORDER BY PRECEDENCE_ORDER ;

The rows that are returned contain the following information.


ORDER  IASP     LIBRARY  TYPE     NAME    RESTRICTED  REMOVABLE
1      *SYSBAS  QSYS     *SYSVAL  (null)  NO          NO
2      *SYSBAS  QSYS     (null)   (null)  YES         NO
3      *SYSBAS  (null)   (null)   (null)  NO          NO

A row specific to the QPFRADJ system value was not returned. The first row indicates that adding system values in QSYS is not restricted. Since the first row matches the object type you want to add and it is not restricted, a new rule for QPFRADJ would be allowed.

The second row indicates that you cannot generally add a rule to an object in library QSYS. The third row describes the default inclusion state for SYSBAS.

Determine whether there is a RESTRICTED attribute that would prevent the addition of a new rule for the QDATE system value.

SELECT DISTINCT PRECEDENCE_ORDER AS ORDER, IASP_NAME AS IASP, 
                LIBRARY_NAME AS LIBRARY, OBJECT_TYPE AS TYPE, 
                OBJECT_NAME AS NAME, RESTRICTED, REMOVABLE
    FROM TABLE(QSYS2.INSPECT_REPLICATION_CRITERIA(
                            IASP_NAME => '*SYSBAS',
                            LIBRARY_NAME => 'QSYS',
                            OBJECT_TYPE => '*SYSVAL',
                            OBJECT_NAME => 'QDATE'))
ORDER BY PRECEDENCE_ORDER ;

The rows that are returned contain the following information.


ORDER  IASP     LIBRARY  TYPE     NAME    RESTRICTED  REMOVABLE
1      *SYSBAS  QSYS     *SYSVAL  QDATE   YES         NO
2      *SYSBAS  QSYS     *SYSVAL  (null)  NO          NO
3      *SYSBAS  QSYS     (null)   (null)  YES         NO
4      *SYSBAS  (null)   (null)   (null)  NO          NO

The first row indicates that a specific rule for the QDATE system value already exists. Since the first row exactly matches the system value and the rule is restricted and cannot be removed, a new rule for QDATE or any changes to the existing rule for QDATE would not be allowed.

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