Application considerations for database replication

QDBMSRVR jobs

Db2 Mirror’s synchronous database replication applies to SQL and native data definition and data changes. That is, the database will replicate object level changes and data within database files as those changes occur. To accomplish the task of replication, the source node job has an associated QDBMSRVR job on the target node. Db2 Mirror uses this partner QDBMSRVR job for implementing the replication activities. For more information about QDBMSRVR jobs, see Db2 Mirror jobs.

Db2 Mirror database replication corresponds to a QDBMSRVR job based on the activation group used by the source node. When the first database replication action is executed within a job on the source node in an actively replicating environment, a remote connection is established to a QDBMSRVR job on the target node, based on the activation group in use. The CPI9170 informational message is written to the joblog on the source node, indicating the qualified jobname that has been established as the partner job on the target node. When the remote connection is established, a corresponding CPI9172 informational message is written to the joblog on the target node, indicating the qualified jobname of the application or user on the source node.

The associated QDBMSRVR job will remain active and connected to the source job until either the source job ends, the activation group is reclaimed, or Db2 Mirror replication is suspended.

The following two figures show a pair of CPI9170 and CPI9172 messages. The message replacement tokens provide insight into what caused replication to begin and the identity of the partner job. Applications and users do not need to use this replication information for Db2 Mirror to function, but the insight is useful if joblogs need to be reviewed for an entire replication operation.

When the connection ends, a CPI9171 informational message is written to the joblog on the source node. The corresponding informational message on the target node is CPI3E02.

If the joblog on the source system has been deleted, or if it's necessary to track backwards from the QDBMSRVR job, the connection logger may be of use.

SELECT * FROM QUSRSYS.MIRROR_CONNECTION_LOG 
  WHERE LOCAL_JOB = '123456/QSYS/JOBNAME' 
  ORDER BY TIME ASC

To find all the source jobs which used a target side QDBMSRVR job, run the following SQL, providing the qualified job name for the remote job:

SELECT * FROM QUSRSYS.MIRROR_CONNECTION_LOG 
  WHERE REMOTE_JOB = '123456/QUSER/QDBMSRVR' 
  ORDER BY TIME ASC

Inquiry messages

Db2 for i issues other, less common, inquiry messages. These other inquiry messages always appear on the source node, but in some cases could also appear on the target node. If they are not included in the system reply list on the target node, your job can appear to hang while waiting for a reply.

In general, if you use the system reply list for such inquiry messages, the best practice is to use matching system reply list entries on both nodes of the pair.

Activation groups

A single job can use more than one activation group, with each activation group using its own unique transaction and associated QDBMSRVR job. For example, if a job has three activation groups and a database replication action has been executed in each activation group, there will be three messages in the joblog, identifying the three associated QDBMSRVR jobs.

When commitment control is being used, pending changes will appear in the activation group of choice in the user’s job and within the default activation group (*DFTACTGRP) of the corresponding QDBMSRVR job. When a COMMIT or ROLLBACK is issued for the activation group in the user’s job, the action is also taken to the corresponding QDBMSRVR job. If the user’s job is running on the primary node and communication is severed, the transaction is rolled back in the QDBMSRVR job on the secondary node and tracked on the primary node.

Interacting with the corresponding QDBMSRVR job

If you need to execute an SQL statement within the QDBMSRVR job, a special form of SQL and 3-part naming can be used.

By using QIBM_DB2M_00nnn (where nnn is 000 for SYSBAS or the ASP number being used ) as the RDB name, the SQL statement will execute in or against the corresponding QDBMSRVR job. The activation group in effect when the statement is executed will determine which QDBMSRVR job is used for execution of the SQL statement.

Applications and users should avoid using 3-part naming to initiate a replication-eligible operation. If such an operation is initiated within a QDBMSRVR job, the replication of the action will not use the application connection.

The QIBM_DB2M_00nnn form of 3-part naming for Db2 Mirror uses the MIRROR_DATABASE NRG. If this NRG is unavailable, use of this 3-part name will fail with SQ30080.

When 3-part names are used to interact with a QDBMSRVR job, the statement and cursor will fail when the replication state changes from ACTIVE to any other replication state. The failure symptom is unpredictable.

For more detail and for setup steps, see Relational database directory entries.

SQL Plan Cache

The SQL Plan Cache is an IBM® i temporary construct that represents database query activity since the last IPL. The plan cache includes access plans, maintained temporary indexes, and other structures to aid in query optimization and implementation.

Db2 Mirror does not replicate the SQL Plan Cache. If the Db2 Mirror configuration is active/passive, you might want to build and execute performance critical queries on the passive node to partially populate the plan cache. This will improve the performance characteristics of query execution after a role swap.