Deploying QReplication in DB2: what are the tasks involved?

The following sections describe the necessary decisions and operations required to deploy QReplication, from planning to system settings and production operations. The operations to be performed include: 1. pre-installation verification and capacity planning, including enabling replication for DB2 2. install and configure the WebSphereMQ and QReplication licenses. 3. replication Subscription definition; that is, you want to copy

The following sections describe the necessary decisions and operations required to deploy Q Replication, from planning to system settings and production operations. The operations to be performed include: 1. pre-installation verification and capacity planning, including enabling replication for DB2 2. installation and configuration of WebSphere MQ and Q Replication licenses 3. replication Subscription definition; that is, you want to copy

The following sections describe the necessary decisions and operations required to deploy Q Replication, from planning to system settings and production operations. The operations to be performed include:

1. Pre-installation verification and capacity planning, including enabling replication for DB2
2. installation and configuration of WebSphere MQ and Q Replication licenses
3. Definition of replication subscription; that is, what tables do you want to copy?
4. Operation: Start and Stop replication to process interruptions
5. Monitoring, adjustment, and troubleshooting to maximize the return from the solution.

Q Replication pre-installation steps and capacity planning

Q Replication is a log capture/transaction Replay Technology. That is, data changes are captured from the DB2 recovery log and SQL statements are reconstructed to replay them on the target. For the purpose of DB2 capacity planning and adjustment, DB2 instances must have sufficient capacity to handle the copied actual SQL workload. In general, any performance tuning required by the application on the source system also applies to the target system.

In the source database, the main DB2 Q Capture activity is to read logs, which generally does not require DB2 adjustment. However, you may want to refer to the database parameter settings for Linux, UNIX, and Windows in the DB2 information center to learn about adjustment suggestions, especially when the transaction volume to be copied is very large.

CPU requirements

Both the Q Capture and Q Apply replication programs and WebSphere MQ add a small amount of overhead to the replication DB2 workload.

As an empirical rule, for the target, the CPU load required to change the application of DB2 from the source database to the target database in the future, Q Apply and WebSphere MQ increase the overhead by 20% to 25% in total. That is to say, about 75% of the CPU resources required for application changes on the target are spent in DB2, which is equivalent to the CPU resources required to execute these identical statements on the source system, about 25% of the required CPU resources are spent on Q Apply and WebSphere MQ. The CPU overhead from Q Apply and WebSphere MQ is used only for members running these programs. The CPU overhead produced by replication on other members is usually negligible. For a large number of replication changes from systems that contain a large number of Members, it may be useful to assign a dedicated member to run the Q Apply program on the target.

In the large-capacity performance experiment where each of the four members consumes 50% of their CPU capacity in the source system, the Q Capture program adds about 10% of the CPU overhead to the members running it, use this overhead to capture changes from all other members. The overhead of capturing log records on other members is negligible.

Generally, the CPU requirements for different environments and workloads vary greatly. We recommend that you use your own applications for testing.

Disk Space Requirements

Temporary changes in the target WebSphere MQ receiving queue require a certain amount of disk space. At a minimum, this space is sufficient to handle the maximum number of DB2 transactions that can be replicated, but it should be sufficient to store the amount of changes that are expected to accumulate during a failure. For example, if you want to copy 1000 rows per second, each row has an average of 200 bytes, and you want to keep the cumulative changes for 24 hours when the target database is shut down, then, you can allocate 1000 rows * 200 bytes/row * 3600 seconds/hour * 24 = 17.3 GB space to the receiving queue on the target system. The WebSphere MQ message header has a minimum overhead, which is usually several hundred bytes for each replicated DB2 transaction. You can use this overhead to round the estimation results. However, it is not a problem if the receiving queue is full. When the replication space is insufficient (for both the source and target queues), the Q Capture program either stops or enters the retry mode based on the qfull_retry_delay and qfull_num_retries parameters. The disk space used for the transmission queue is sufficient to store the maximum database transaction that can be copied.

Space depletion is not a big problem. Q Replication stores the progress of reading DB2 logs and information about the WebSphere MQ messages it has processed in persistent storage, so any component of the Replication process can be interrupted at any time (or even suddenly interrupted ), data will not be lost.

In this article, we use the same file system for all DB2 and WebSphere MQ data. However, to achieve optimal performance, we recommend that you use an independent disk and file system for DB2 logs, DB2 data, WebSphere MQ logs, and WebSphere MQ data. The space required for WebSphere MQ logs on the source system is proportional to the amount of messages that may be accumulated in XMITQ; on the target, it is proportional to the number of messages that can be applied simultaneously by Q Apply. In both cases, it requires much less space than WebSphere MQ data. Generally, 200 MB is enough to store WebSphere MQ logs unless you copy a single DB2 transaction with a larger capacity than this one.

Prepare the database for replication

To prepare a database for replication, perform the following operations:

1. cataloguing each DB2 database on another site so that the Q Apply program can remotely connect to it as needed, such as during initial table loading. We use aliases to catalog databases, such as QSAMPLE1 on site 1 and QSAMPLE2 on site 2.

2. Set LOGARCHMETH1 = LOGRETAIN on the database. Loop logs cannot be used, because a log file still needs to be copied can be reused.

3. Adjust the table you want to copy to enable data capture changes.

Q Replication installation and configuration

To install and configure Q Replication, perform the following steps:

1. Install WebSphere MQ.
2. Create a WebSphere MQ object.
3. If necessary, obtain a Q Replication license.
4. initialize the shell environment.
5. Create a control table for Q Replication.

1. Download and install WebSphere MQ

See Appendix 2 for commands and instructions for downloading and installing WebSphere MQ.

2. Create a WebSphere MQ object

We need to create the queue manager, WebSphere MQ queue, channel and listener, for this task, you need to know the IP host name of each data member running WebSphere MQ and the port used by WebSphere MQ (Port 1414 by default ). You also need to use the name of the Shared File System for WebSphere MQ. In our example, the name is/db2fs/data on site 1 and/db2fs/data2 on site 2.

We will create a queue manager with the same name as the database alias, that is, the same name as QSAMPLE1 of Site 1 and QSAMPLE2 of Site 2. We provide steps for a pureScale instance and repeat these steps on another site.