Schema replication with IBM InfoSphere Data Replication, Part 2: Bi-directional schema subscriptions in DB2 for Linux, UNIX, and Windows 10.1

Setup of Q Replication schema subscriptions in an active/active scenario

IBM® InfoSphere® Data Replication allows the synchronization of data between two or more database management systems either on the same or on different operational platforms. Many different usage scenarios exist. Starting in Version 10.1 of IBM InfoSphere Data Replication, replication is now supported at the schema level. This means that defined changes to the database structure such as the creation of new tables are automatically added to the replication system without any need for administration or intervention. This not only eliminates or greatly reduces the administrative efforts when tables are added or changed at the primary database but also significantly increases the reliability of the replication system, especially when used as a synchronization mechanism for an active disaster recovery site. This article is the second in a series which uses a disaster recovery use case to explain how to set up schema-level subscriptions for bi-directional replication topologies available in the Q Replication technology that comes with IBM InfoSphere Data Replication v10.1.3 for Linux, UNIX, and Windows. We encourage the user to replay the scenario. To make this as convenient as possible, we provide various scripts in the Download section of the article. Watch out for more articles in this series that cover schema-level subscriptions for uni-directional topologies and more.

Olaf Stephan (STEPHANO@de.ibm.com), Leading Services Specialist - Information Management, IBM

Author photoOlaf Stephan works as an IT Specialist for the Information Management division of IBM Software Group Services. His focus is to provide IBM customers with knowledge of IBM DB2 for Linux, UNIX, and Windows databases and Data Warehouse technologies as well as Data Integration by consulting, educating, briefing, and development for these platforms. He also worked for the IBM Development and Research Lab in Boeblingen at the IBM Product IBM Smart Analytics Optimizer. Olaf was a co-author on the following IBM publications. IBM Redbook: "Data Mart Consolidation: Getting Control of Your Enterprise Information" (SG24-6653-00). IBM developerWorks: "Porting workshop, Processor porting strategies", "Introducing the 7-part, fast-read series on porting compute-intense applications to the Cell Broadband Engine Architecture".



Christian Lenke (clenke@de.ibm.com), Leading Technical Sales Professional, IBM China

Author photoChristian Lenke works as a Client Technical Professional for the Information Management division of IBM Software Group. His area of expertise is information management with a focus on information integration, including ETL, data quality, data migrations, and metadata management. Christian has accompanied the evolution of IBM data replication technologies for more than 15 years and has introduced enterprise-style data replication solutions at a huge number of German and international customers. Christian was a key writer of the IBM Redbook: "My Mother thinks I'm a DBA - Cross-Platform, Multi-Vendor, Distributed Relational Data Replication with IBM DB2 DataPropagator and IBM DataJoiner - Made Easy!" (SG24-5463-00 ).



30 May 2013

Introduction

Part 1 of this series described how companies in the 'on demand' marketplaces of today are becoming more reliant on electronic data and software applications to serve customers and to comply with government and industry regulations. Those enterprises can’t afford the cost of downtime due to planned or unplanned system outages.

We introduced Q Replication as an alternative or supplement for traditional, and in most cases, hardware-based high availability or disaster recovery solutions, and discussed the pros and cons of a software-based approach. With Q Replication, IBM offers a software based near-real-time transaction replication mechanism with guaranteed data delivery. Technically speaking, it is an asynchronous, log-capture / transaction replay technology that uses WebSphere MQ as transport and staging mechanism between the distributed components of the solution. Q Replication can be configured uni-directionally or multi-directionally. For more information, please refer to Part 1 of this series.

Advantage of DDL replication and schema replication

Imagine that you used a data replication solution to synchronize your mission critical OLTP database with a distant disaster recovery site. Additionally, imagine that due to a local power outage you have to switch your application to the remote site. Immediately after the switch you recognize that the DBA had not yet added the three important new tables which came with the recent release to the replication setup. This potential scenario can be easily avoided using the new schema-level subscriptions introduced with the IBM Q Replication technology that comes with IBM InfoSphere Data Replication for Linux, UNIX, and Windows (IIDR). Schema-level subscriptions and automatic DDL replication limit the risk to spoil the disaster recovery site due to some forgotten checklist items and gives your DBA the time to care about more important tasks.

What is a schema-level subscription

A schema-level subscription is a new way to set up data replication for a large number of tables with just a couple of simple commands. When a schema subscription is created and activated, table-level subscriptions are automatically defined for all tables that correspond to the naming pattern of the schema subscription. When a new table is created which matches the definition of an existing schema subscription, a new table-level subscription is automatically added and activated immediately. Optionally, but recommended, a switch can be set so that certain ALTER TABLE statements also replicate to the target database without any administrative intervention. Currently, the addition of new columns (ALTER TABLE ... ADD COLUMN) and the alteration of a column's data type (ALTER TABLE ... ALTER COLUM) are supported. Schema subscriptions were first introduced with DB2 for Linux, UNIX, and Windows V10.1 and InfoSphere Data Replication V10.1.3.


Scenario

In Part 1 of this series, a use case was designed to demonstrate how easy it is to set up uni-directional Q Replication leveraging new features like schema subscriptions and automatic DDL replication. Q Replication was used to synchronize an operational database (OLTPDB) with a disaster recovery site (DRDB). In that simple example of an active/active disaster recovery solution, applications can switch to the disaster recovery site in case of an outage of the primary site, but because the data replication is defined in a uni-directional fashion only, a switch back would require the setup of a new replication direction. Without the need of an application switch, the disaster recovery site can be used to offload reporting and read-only applications at any time.

In this article, the use case is extended to synchronize the primary and secondary database bi-directionally. This means Q Replication is configured from the primary database OLTPDB to the secondary database DRDB and from DRDB to OLTPDB. Bi-directional replication is the prerequisite if you want to switch the application from one site to the other site and back, or, if you want to distribute workloads between the two databases (such as separation of tenancy). Q Replication keeps both sites in synch with minimal latency, automatically prevents replication circles (a change which has replicated from site A to site B will not be recaptured and sent back), and resolves conflicts if the same record was changed at both sites simultaneously. In case of a planned or unplanned outage, the applications can switch to the disaster recovery site. If the primary site is back online again, all records changed at the secondary site replicate back, and after that, the application can switch again.

As for the uni-directional use case, only dedicated tables contribute to the solution. Figure 1 outlines the bi-directional scenario. The disaster recovery site has to contain all tables of schema BLUE, but not TAB99. TAB99 is a local configuration table. RED tables must not be synched with the recovery site.

Figure 1. Q Replication architecture overview for bi-directional use case
This figure shows the Bi-directional use case of Q Replication architecture

Table 1 lists all tables at database OLTPDB (primary database) and indicates whether they should replicate to the DRDB (secondary database) or not.

Table 1. List of tables used in the bi-directional use case
Schema and nameReplicate
BLUE.TAB01Y
BLUE.TAB02Y
BLUE.TAB03Y
BLUE.TAB99N
RED.TAB01N
RED.TAB02N

As for the uni-directional use case, you can easily replay this new scenario. For each and every setup step, you can download the tested scripts, which you can access via the Downloads section of this article.

If you have already replayed the uni-directional scenario, you can set up the bi-directional scenario side by side. To do so, you should use a different table schema (BLUE), a different replication schema (QASN_BIDI), and different queue managers (QM_OLTPDB_BI and QM_DRDB_BI).

If you have not already replayed the uni-directional scenario and start from scratch, begin with the creation of the source and target database.


Before you start

This section outlines basic database configuration tasks to enable Q Replication (include schema-level subscriptions) and to prepare for the use case.

Configuration of source and target database

The following database configuration parameters, as shown in Listing 1, need to be set for both databases (because in the bi-directional use case both databases act as replication source database) to enable Q Replication schema subscriptions:

Listing 1. Database configuration
UPDATE DB CFG FOR  OLTPDB using LOGARCHMETH1    LOGRETAIN;
BACKUP DB OLTPDB TO /dev/null;
UPDATE DB CFG FOR  OLTPDB using LOG_DDL_STMTS   YES;
UPDATE DB CFG FOR  OLTPDB using DFT_SCHEMAS_DCC YES;

UPDATE DB CFG FOR    DRDB using LOGARCHMETH1    LOGRETAIN;
BACKUP DB DRDB TO /dev/null;
UPDATE DB CFG FOR    DRDB using LOG_DDL_STMTS   YES;
UPDATE DB CFG FOR    DRDB using DFT_SCHEMAS_DCC YES;
  • LOGARCHMETH1 LOGRETAIN: A switch to LOGRETAIN turns the log mode from circular logging into a log mode where DB2 retains archived logs (or passes archived logs to standard archiving solutions). After you have set the database configuration parameter LOGARCHMETH1 to LOGRETAIN a backup of the database is required.
  • LOG_DDL_STMTS: This parameter was introduced with DB2 for Linux, UNIX, and Windows V10.1. A switch to YES lets DB2 write log records which can be retrieved by Capture through the standard log read API when tables are created or dropped.
  • DFT_SCHEMAS_DCC: This parameter was introduced with DB2 for Linux, UNIX, and Windows V10.1. A switch to YES indicates that tables in all new schemas are created with the DATA CAPTURE CHANGES attribute. The DATA CAPTURE CHANGES attribute is required for all tables being replicated.

Add tablespace and schema in OLTPDB and DRDB

As shown in Listing 2, you have to add an additional tablespace and a new schema in database OLTPDB and DRDB before starting with the setup and configuration of the bi-directional replication.

Listing 2. Create tablespace blue and create schema data capture changes
CONNECT TO OLTPDB;
CREATE TABLESPACE BLUE_TS;
CREATE SCHEMA BLUE DATA CAPTURE CHANGES;

CONNECT TO DRDB;
CREATE TABLESPACE BLUE_TS;
CREATE SCHEMA BLUE DATA CAPTURE CHANGES;

DDL script CREATE_2_BLUE_TABLES.DDL creates and populates the first two tables in schema BLUE in database OLTPDB. DDL script CREATE_RED_TABLES.DDL creates and populates all tables in schema RED in database OLTPDB (you can find the scripts in the Downloads section of this article). All other steps which are specific to the replication setup or the definition of schema subscriptions are explained in detail during the following sections.

Creation of all required MQ objects

In a bi-directional replication setup, captured messages have to flow from OLTPDB to DRDB and back. This requires message queues for each replication direction. To create all required MQ objects for bi-directional replication, a similar ASNCLP script is used as described in "Listing 3" in Part 1 of the series. To create queues for the bi-directional setup, you can simply change the CONFIG TYPE from U (uni-directional) to B (bi-directional), as shown in Listing 3.

Listing 3. ASNCLP commands to create MQ objects for bi-directional replication
ASNCLP SESSION SET TO Q REPLICATION;

CREATE MQ SCRIPT 
CONFIG TYPE B 
MQSERVER 1 NAME OLTPDB MQHOST "192.168.8.146" 
MQPORT 2014 
QMANAGER QM_OLTPDB_BI QNAME_QUAL QASN_OLTPDB,
MQSERVER 2 NAME DRDB  MQHOST "192.168.8.144" 
MQPORT 2014  
QMANAGER QM_DRDB_BI   
QNAME_QUAL QASN_DRDB;
QUIT;

The ASNCLP commands generate a UNIX shell script or a Windows batch script. The scripts contain commands to create all required MQ objects like Q Managers, Administration Queue, Send and Receive Queue, and the Restart Queue.

Note: If you are not yet familiar with ASNCLP, review the documentation how to execute ASNCLP commands. If you store the ASNCLP commands in a file, the easiest way to run the commands is using asnclp -f <filename>.

The ASNCLP command CREATE MQ SCRIPT was introduced with DB2 9.7.

Preparation of ASNCLP template

As in Part 1 of the series, Q Replication ASNCLP command language is used for each and every setup step, which lets you recreate the scenario easily. Each of the following ASNCLP commands requires an the same header specifying the environment. The setup of bi-directional schema subscriptions requires a different header compared to uni-directional subscriptions. The code shown in Listing 4 displays the header for the bi-directional use case.

Listing 4. ASNCLP header used for all following ASNCLP commands
ASNCLP SESSION SET TO Q REPLICATION;

SET SUBGROUP BLUEandRED;
SET BIDI NODE 1 SERVER DBALIAS OLTPDB DBNAME OLTPDB        
ID USERID PASSW0RD "PASSW0RD"  SCHEMA QASN_BIDI;

SET BIDI NODE 2 SERVER DBALIAS DRDB   DBNAME DRDB          
ID USERID PASSW0RD "PASSW0RD"  SCHEMA QASN_BIDI;

SET QMANAGER "QM_OLTPDB_BI" FOR NODE 1;
SET QMANAGER "QM_DRDB_BI"   FOR NODE 2;

SET CAPTURE SCHEMA SOURCE QASN_BIDI;
SET APPLY   SCHEMA        QASN_BIDI;

SET OUTPUT MULTIDIR  ;
SET RUN SCRIPT LATER ;

The ASNCLP header section defines source and target database, the queue managers, and the Q Capture and Q Apply schema. The header section names the output files that will contain the generated SQL commands. The option RUN SCRIPT LATER indicates that the generated SQL commands are not immediately executed, but can be reviewed before execution. Alternatively, the option RUN NOW can be used to execute the generated scripts immediately.

Creation of the Q Replication control tables

To be able to set up the bi-directional schema subscriptions in parallel to the uni-directional use case described in Part 1 of the series, you should create a separate set of control tables with a different schema (QASN_BIDI). The Q Capture Control Tables now have to be created in both databases because OLTPDB, as well as DRDB, now act as the replication source. The Q Apply control tables have to be created in both databases, too. The ASNCLP commands shown in Listing 5, together with the previously described ASNCLP header, generate SQL statements to create the Q replication control tables in both databases.

Listing 5. ASNCLP commands to create control tables for bi-directional replication
#requires header as listed in Listing 4
				
CREATE CONTROL TABLES FOR NODE 1 
USING CAPPARMS 
RESTARTQ "QASN_OLTPDB.OLTPDB.RESTARTQ"   
ADMINQ   "QASN_OLTPDB.OLTPDB.ADMINQ";

CREATE CONTROL TABLES FOR NODE 2 
USING CAPPARMS 
RESTARTQ "QASN_DRDB.DRDB.RESTARTQ"       
ADMINQ   "QASN_DRDB.DRDB.ADMINQ";

Creating and activating bi-directional schema-level subscriptions

This section explains how to set up the bi-directional replication scenario for the tables described in Figure 1.

Creation of a replication queue map

The replication queue map ties together all MQ and performance specific configuration parameters for a replication direction. Because a bi-directional replication setup consists of two independently operated replication directions, two queue maps are required. The code shown in Listing 6 describes the ASNCLP commands to create both replication queue maps.

Listing 6. ASNCLP commands to create replication queue maps for bi-directional replication
#requires header as listed in Listing 4

CREATE REPLQMAP OLTPDB_TO_DRDB (NODE 1, NODE 2) USING 
ADMINQ "QASN_OLTPDB.OLTPDB.ADMINQ" 
RECVQ "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA" 
SENDQ "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA";

CREATE REPLQMAP DRDB_TO_OLTPDB (NODE 2, NODE 1) USING 
ADMINQ "QASN_DRDB.DRDB.ADMINQ"     
RECVQ "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA" 
SENDQ "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA";

Definition of schema options for bi-directional schema subscriptions

Q replication subscriptions can be flexibly customized in a huge number of ways. Schema options specify the customization for the complete set of subscriptions belonging to a replication schema. Usual customizations specify the initial refresh behavior, the behavior in case of an error, and whether new columns should be automatically added to the subscription and to the replication target.

In our example we define the following schema subscription options for bi-directional schema subscriptions as follows.

SUBTYPE B: Indicates that these options apply to bi-directional schema-level subscriptions.

CAPTURE_LOAD W: Lets Q Capture issue a warning if a table belonging to the schema subscription was loaded with a utility. Note: Utility operations are not logged in a way which Q Capture could utilize through the standard DB2 log interface.

REPLICATE ADD COLUMN Y: Indicates that Q Capture automatically replicates certain table alterations such as ADD COLUMN or ALTER COLUMN ALTER DATA TYPE.

ERROR ACTION Q: Lets Q Apply stop all subscriptions which are being replicated via the same queue/qmap in case of an error (such as tablespace full for one table, and so on). All changes send by Q Capture are buffered in the queue until the error is fixed and Q Apply continues. This behavior guarantees transaction consistency even in error cases.

LOAD TYPE 1 EXIST DATA REPLACE: Lets Apply invoke LOAD FROM CURSOR in case of an initial refresh.

CONFLICT ACTION: For bi-directional schema subscriptions, both replication directions leverage the same set of subscription options. This is adequate for most options. Although, one of the options is used to control which database should take precedence in case the same record (same key) is updated at both sides simultaneously. This database is sometimes also referred to as master database. The name of the parameter controlling the precedence is called CONFLICT ACTION and has the following meanings:

  • I: (Ignore) Tells Q Apply to ignore replication conflicts.
  • F: (Force) Tells Q Apply to overwrite the existing values with the replicated ones.

Although the replication latency for Q Replication is minimal (usually around 1-2 seconds), the chance for conflicts is very low during normal replication (when all processes are active), but the chance for a conflict is a lot higher when part of the replication processes is down (in case of an outage). Therefore it is important that CONFLICT_ACTION is not set to the same value for both directions. The concept of subscription properties fails here because it is not possible to define a different conflict behavior for each direction.

Until this issue (observed in IBM InfoSphere Data Replication V10.1 Fixpack 2) is resolved, in case of bi-directional schema subscriptions you have to care for it yourself that all automatically created subscriptions for one direction (such as OLTPDB to DRDB) receives CONFLICT_ACTION = F, and the other direction (such as DRDB to OLTPDB) receives CONFLICT_ACTION = I. This can be achieved by a periodically invoked SQL script setting CONFLICT_ACTION = 'F' in table IBMQREP_TARGETS at one of the databases (such as DRDB), or by doing the same with a trigger automatically. That would mean that conflicts are overwritten with the transaction from the other database (such as OLTPDB).

The ASNCLP code shown in Listing 7 sets all relevant subscription options (except CONFLICT_ACTION) for this scenario.

Listing 7. ASNCLP commands to define schema options for bi-directional replication
#requires header as listed in Listing 4

CREATE SUBSCRIPTION OPTIONS BIDIR_STANDARD
SUBTYPE B 
CAPTURE_LOAD W
REPLICATE ADD COLUMN Y
ERROR ACTION Q
LOAD TYPE 1 EXIST DATA REPLACE;

Note: Due to another unsolved issue in IBM InfoSphere Data Replication V10.1 Fixpack 2, the subscription option REPLICATE ADD COLUMN YES does not propagate to table-level subscriptions that were automatically generated from a schema-level subscription. You can fix this problem by manually updating the Q Replication control table IBMQREP_SUBS after new table-level subscriptions have been added. Perform the SQL update statement shown in Listing 8 after you have created the schema subscriptions, and after new table-level subscriptions have been automatically generated from the schema subscriptions (you can skip the SQL UPDATE in Listing 8 after the issue has been finally resolved).

Listing 8. Update table IBMQREP_SUBS
UPDATE QASN_OLTPDB.IBMQREP_SUBS SET REPL_ADDCOL = 'Y';

Creation of schema subscriptions for bi-directional replication

The task is now to replicate all tables in the BLUE schema except table TAB99 in a bi-directional fashion. Further more, tables in the RED schema must not replicate in this example. The ASNCLP command (CREATE SCHEMASUB) defines the schema subscription accordingly.

The schema subscription created in Listing 9 uses the previously defined schema options BIDIR_STANDARD.

Listing 9. ASNCLP commands to create schema subscriptions for bi-directional replication
#requires header as listed in Listing 4

SET CONNECTION SOURCE OLTPDB.QASN_BIDI 
TARGET DRDB.QASN_BIDI   
REPLQMAP OLTPDB_TO_DRDB;

SET CONNECTION SOURCE DRDB.QASN_BIDI   
TARGET OLTPDB.QASN_BIDI 
REPLQMAP DRDB_TO_OLTPDB;

CREATE SCHEMASUB BLUE_SCHEMA SUBTYPE B 
FOR TABLES NODE 1 OWNER LIKE BLUE
EXCLUDE OWNER BLUE NAME TAB99, OWNER RED
OPTIONS BIDIR_STANDARD;

The ASNCLP commands generate SQL scripts to save the schema subscription in the replication control tables of schema QASN_BIDI. As well, the CREATE TABLE statements to create the new BLUE tables in database DRDB are generated when creating the schema subscription. The following query (list tables command) shown in Listing 10, performed after the generated SQL was executed shows that 2 BLUE tables were created during the creation of the schema subscription BLUE_SCHEMA.

Listing 10. Tables in schema BLUE on DRDB
db2 connect to DRDB
db2 list tables for schema blue

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TAB01                           BLUE            T     2013-04-26-11.12.24.357848
TAB02                           BLUE            T     2013-04-26-11.12.24.773629

Note that flexible options exist also for bi-directional schema subscriptions to include and exclude tables from a replication schema. Schema-level subscriptions could be easily defined for patterns like BLUE.MYTAB%, BL%.%, or %.%. But, for one replication queue map, schema patterns must not be overlapping, causing that the same table would match two schema-level subscriptions.


Operating Q Capture and Q Apply for bi-directional replication

As both databases now act as replication source and as replication target, you have to start two Q Capture processes and two Q Apply processes. You can use the asnqcap command to start the Q Capture task - now for capture_server OLTPDB and for capture_server DRDB. Use the asnqapp command to start the Q Apply tasks for apply_server DRDB, and for apply_server OLTPDB.

Create the encrypted PASSW0RD file

All four processes can leverage the same password file. If you have not created the password file for the uni-directional use case, create a new password file with the commands shown in Listing 11.

Listing 11. Create PASSWORD file for capture and apply
asnpwd INIT USING encrypt.passwd
asnpwd add alias OLTDB id USERID PASSW0RD PASSW0RD USING encrypt.passwd
asnpwd add alias DRDB  id USERID PASSW0RD PASSW0RD USING encrypt.passwd

To start Q Capture and Q Apply, you can use the same startup parameters as explained in the uni-directional use case. Note that you are again starting two Q Capture processes and two Q Apply processes, as shown in Listing 12.

Listing 12. Q Capture and Q Apply startup commands
asnqcap capture_server=OLTPDB capture_schema=QASN_BIDI logstdout=y pwdfile=encrypt.passwd
asnqcap capture_server=DRDB   capture_schema=QASN_BIDI logstdout=y pwdfile=encrypt.passwd

asnqapp apply_server=DRDB     apply_schema=QASN_BIDI   logstdout=y pwdfile=encrypt.passwd
asnqapp apply_server=OLTPDB   apply_schema=QASN_BIDI   logstdout=y pwdfile=encrypt.passwd

What happens to existing tables?

If Q Capture and Q Apply are started after the creation of the schema subscription (as in this scenario) they activate the schema-level and the underlying table-level subscriptions immediately. According to the naming pattern of the schema-level subscription, the names of the two generated table-level subscriptions for BLUE.TAB01 and BLUE.TAB02 are as follows. Subscriptions: TAB010001 and TAB020001.

The messages for Q Capture and Q Apply for the replication direction OLTPDB to DRDB are the same as the messages you had listed for the uni-directional use case. However, the Q Capture log for DRDB now shows the following entries.

Q Capture's message ASN7010I indicates that the table-level subscriptions were successfully activated, as shown in Listing 13.

Listing 13. Q Capture log snippet after startup for capture_server=DRDB
2013-04-26-12.03.56.274568 ASN7010I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
The program successfully activated publication or Q subscription 
"TAB010002" (send queue "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA", 
publishing or replication queue map "DRDB_TO_OLTPDB") for source table "BLUE.TAB01".
2013-04-26-12.03.56.290544 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"REPLICATE_LOAD" signal was received and will be processed.
2013-04-26-12.03.56.498189 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"P2PNEW2MEMB" signal was received and will be processed.
2013-04-26-12.03.56.502238 ASN7010I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
The program successfully activated publication or Q subscription 
"TAB020002" (send queue "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA", 
publishing or replication queue map "DRDB_TO_OLTPDB") for source table "BLUE.TAB02".
2013-04-26-12.03.56.503481 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"REPLICATE_LOAD" signal was received and will be processed.
2013-04-26-12.04.02.174941 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"REPLICATE_LOAD" signal was received and will be processed.

Q Apply logs the messages shown in Listing 14 for apply_server=OLTPDB. The Q Apply message ASN7606I indicates that the table-level subscriptions were successfully activated.

Listing 14. Q Capture logfile for OLTPDB
2013-04-26-12.03.56.622063 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' 
received a 'ASNMQ_SUBSCHEMA_MSG' message.
2013-04-26-12.03.56.747141 ASN7606I  "Q Apply" : "QASN_BIDI" : "BR00000" : 
Q subscription "TAB010002" (receive queue "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA"
, replication queue map "DRDB_TO_OLTPDB") is active.
2013-04-26-12.03.56.748527 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' 
received a 'ASNMQ_SUBSCHEMA_MSG' message.
2013-04-26-12.03.56.828917 ASN7606I  "Q Apply" : "QASN_BIDI" : "BR00000" : 
Q subscription "TAB020002" (receive queue "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA"
, replication queue map "DRDB_TO_OLTPDB") is active.

Monitoring the bi-directional replication status

Again, you can use the browser-based Q Replication Dashboard to monitor the overall replication status, the status of all replication objects, and the throughput of the replication processes. As shown in Figure 2, for bi-directional replication, the Q Replication Dashboard screen for live graphs is divided into two parts: One for the replication direction "OLTPDB to DRDB", and one for the replication direction "DRDB to OLTPDB".

Figure 2. Q Replication Dashboard overview for bi-directional
This figure shows the Q Replication Dashboard Live screens for replication directions.

Due to the fact that the replication setup was defined bi-directionally, the Q Replication Dashboard health summary displays separate replication objects for each direction for subtype 'B' (bi-directional). The health summary lists the status of the four Q Capture and Q Apply processes and the status of the queue maps (send & receive queues). As shown in Figure 3, the diamond symbols shows successful processing for all status indicators.

Figure 3. Q Replication Dashboard Health Summary
This figure shows the diamond symbols that indicate successful processing of all status indicators.

Next, we used the dashboard subscription view to display the status of the individual replication subscriptions.

Figure 4. Q Replication Dashboard Schema Subscriptions
This figure shows the Q Capture and Q Apply limited subscriptions for individual replications.

The two table-level subscriptions are grouped under the one schema-level subscription that you had created. The diamond symbols signal that both the schema-level subscriptions and the table-level subscriptions are active and replicating without any problems.


Automatic creation of additional subscriptions

In the uni-directional schema subscriptions section of Part 1, it is demonstrated how easy it is to add new tables to the replication setup when leveraging the new feature of schema subscriptions. But, due to the fact that an uni-directional setup has been chosen, new tables would only be added in database OLTDB which acts as a replication source. Bi-directional replication allows you to now add new tables on either side because both databases OLTPDB and DRDB act as a replication source. The defined schema subscription guarantees that the table will also be created in the other database.

A new subscription for table BLUE.TAB03

To demonstrate the automatic creation of new subscriptions, you can create a new table that matches the schema subscription naming pattern (table BLUE.TAB03) this time in database DRDB, as shown in Listing 15.

Listing 15. Create addition table BLUE.TAB03
db2 connect to DRDB
db2 -tvf CREATE_BLUE_TAB03.DDL

After the creation of TAB03, Q Capture (DRDB) immediately recognizes the CREATE TABLE DDL statement within the DB2 transaction log (due to database configuration parameter LOG_DDL_STMTS=Y). Q Capture determines that the table matches the naming pattern for schema subscription BLUE_SCHEMA and immediately creates the new table-level subscription. Additionally, Q Capture sends a message to Q Apply (OLTPDB) to let Q Apply create the subscription-specific metadata at the target database.

Q Apply also creates a target table with the same structure as the source table. Q Apply (OLTPDB) then triggers Q Capture (OLTPDB) to add the subscription for the other replication direction (OLTPDB to DRDB) of this bi-directional setup. Finally, the new table exists in both databases and both replication directions are active so that all inserts, updates, and deletes after the table creation are replicated instantaneously. No full refresh is required.

Take a look at the Q Capture and Q Apply logs to see how the replication processes handled the creation of the new table. You can monitor the replication status with the Q Replication Dashboard.

First of all, you can see that Q Capture for database DRDB recognizes the creation of the new table, which was created in database DRDB. The Q Capture log message ASN7210I indicates that the table matches the naming pattern of schema subscription BLUE_SCHEMA, and that a new table-label subscription (TAB030001) was created, as shown in Listing 16.

Listing 16. Q Capture logfile for DRDB
2013-04-26-13.05.36.810989 ASN7210I  "Q Capture" : "QASN_BIDI" 
: "WorkerThread" : Q subscription "TAB030001"  that corresponds 
to schema-level subscription "BLUE_SCHMEA" was successfully created 
for the source table "BLUE.TAB03"  that uses  send queue 
"QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA" and 
replication queue map "DRDB_TO_OLTPDB".
2013-04-26-13.05.36.968191 ASN7010I  "Q Capture" : "QASN_BIDI" : 
"WorkerThread" : The program successfully activated publication or 
Q subscription "TAB030001" (send queue "QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA", 
publishing or replication queue map "DRDB_TO_OLTPDB") for source table "BLUE.TAB03".

After Q Apply (OLTPDB) receives the ASNMQ_CREATESUB_MSG messages from Q Capture (DRDB), Q Apply immediately generates subscription metadata to add the new subscription. Q Apply creates the table BLUE.TAB03 in database OLTPDB and activates the subscription, as shown in Listing 17.

Listing 17. Q Apply logfile for OLTPDB
2013-04-26-13.05.37.075516 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' 
received a 'ASNMQ_CREATESUB_MSG' message.
2013-04-26-13.05.37.121398 ASN7711I  "Q Apply" : "QASN_BIDI" : 
"BR00000" : Q subscription "TAB030001" was successfully created 
for the target table ""BLUE".TAB03" that uses receive queue 
"QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA" and replication queue map 
"DRDB_TO_OLTPDB" because a schema-level subscription 
"BLUE_SCHMEA" was defined at the source.
2013-04-26-13.05.37.125379 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' received a 'ASNMQ_DDL_MSG' message.
2013-04-26-13.05.37.981128 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' received a 'ASNMQ_DDL_MSG' message.
2013-04-26-13.05.38.483614 ASN8999D Browser for queue 
'QASN_DRDB.DRDB_TO_QASN_OLTPDB.OLTPDB.DATA' received a 'ASNMQ_SUBSCHEMA_MSG' message.
2013-04-26-13.05.38.517015 ASN8999D conflicting entries 
in IBMQREP_TRG_COLS for schema-level sub
2013-04-26-13.05.38.589793 ASN7606I  "Q Apply" : "QASN_BIDI" : "BR00000" : 
Q subscription "TAB030001" (receive queue "QASN_DRDB.DRDB_TO_QASN_OLPDB.OLTPDB.DATA", 
replication queue map "DRDB_TO_OLTPDB") is active.

Next Q Apply (OLTPDB) triggers Q Capture (OLTPDB) with a signal to add the new bi-directional subscription to the other replication direction. The Q Capture log message ASN7019I indicates that the signal (P2PCREATESUB) was received. The Q Capture log message ASN7210I indicates that the new replication direction (OLTPDB to DRDB) was added for BLUE.TAB03, as shown in Listing 18.

Listing 18. Q Capture logfile for OLTPDB
2013-04-26-13.05.37.631132 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"P2PCREATESUB" signal was received and will be processed.
2013-04-26-13.05.37.743520 ASN7210I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
Q subscription "TAB030001"  that corresponds to schema-level subscription 
"BLUE_SCHMEA" was successfully created for the source table "BLUE.TAB03"  
that uses  send queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA" 
and replication queue map "OLTPDB_TO_DRDB".
2013-04-26-13.05.39.066060 ASN7019I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
"P2PNEW2MEMB_BYTAB" signal was received and will be processed.
2013-04-26-13.05.39.083223 ASN7010I  "Q Capture" : "QASN_BIDI" : "WorkerThread" : 
The program successfully activated publication or Q subscription 
"TAB030001" (send queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", 
publishing or replication queue map "OLTPDB_TO_DRDB") for source table "BLUE.TAB03".

Finally, Q Apply (DRDB) receives a message from Q Capture (OLTPDB) to add the subscription metadata for the second replication direction (OLTPDB to DRDB). The Q Apply log message ASN7711I indicates that the subscription was successfully created, as shown in Listing 19.

Listing 19. Q Apply logfile for DRDB
2013-04-26-13.05.37.802440 ASN8999D Browser for queue 
'QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA' 
received a 'ASNMQ_CREATESUB_MSG' message.
2013-04-26-13.05.37.836751 ASN7711I  "Q Apply" : "QASN_BIDI" : 
"BR00000" : Q subscription "TAB030001" was successfully created 
for the target table ""BLUE".TAB03" that uses receive queue 
"QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA" and replication queue map 
"OLTPDB_TO_DRDB" because a schema-level subscription 
"BLUE_SCHMEA" was defined at the source.
2013-04-26-13.05.39.321092 ASN8999D Browser for queue 
'QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA' received a 'ASNMQ_SUBSCHEMA_MSG' message.
2013-04-26-13.05.39.334311 ASN8999D conflicting entries 
in IBMQREP_TRG_COLS for schema-level sub
2013-04-26-13.05.39.398148 ASN7606I  "Q Apply" : "QASN_BIDI" : "BR00000" : 
Q subscription "TAB030001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", 
replication queue map "OLTPDB_TO_DRDB") is active.

After all four processes have added and activated the subscription for BLUE.TAB03, the table BLUE.TAB03 can be updated on either side (OLTPDB or DRDB) and these changes replicate in both replication directions. No administrative interaction was necessary and no application downtime had to be scheduled because BLUE.TAB03 matched the naming pattern of the schema subscription BLUE_SCHEMA, and the subscription was created automatically.

Monitoring the replication status

You can use the Q Replication Dashboard to review the replication status after BLUE.TAB03 is added. The Q Replication Dashboard immediately adds the new subscriptions to the health summary page. Figure 5 shows both replication directions for the bi-directional setup.

Figure 5. Q Replication Dashboard after adding new table
This figure shows the replication directions for the bi-directional setup.

The subscription counter in has been increased from 2 to 3 for both directions. All six subscriptions (three for each direction) are active, which is visualized by the diamond symbols.


Automatic replication of table alterations

Besides adding new tables to a schema, the alteration of existing tables is another common use case that can now be handled automatically with InfoSphere Data Replication V10.1.3, and above. This applies to all table-level subscriptions, not only those generated from schema subscriptions.

While not all alterations are supported, the most common alterations are currently supported. Be aware that the automatic replication of table alterations is optional and has to be explicitly switched on. Table alterations automatically replicate for subscriptions that were only created with option REPLICATE ADD COLUMN Y. You can check if the option is set by querying the subscription attributes (column REPL_ADDCOL) in control table IBMQREP_SUBS at the Q Capture server.

We have demonstrated how new columns or altered columns automatically replicate in the "Automatic replication of table alterations" section in Part 1 of the series. All observations made for uni-directional subscriptions also apply to bi-directional subscriptions. In case of bi-directional subscriptions, tables and columns can be altered on either side of the setup (OLTPDB or DRDB).


Current limitations

Please review Part 1 of the series and the product documentation (DB2 Information Center: Schema Subscriptions) for a complete list of limitations.


Conclusion

In the second part of the series, you have learned how easy it is to set up and manage bi-directional Q Replication with just a few ASNCLP commands. As for the uni-directional use case, the new features schema subscriptions and automatic DDL replication were used. Also, the Q Replication Dashboard is used to display the replication status and to monitor the automatic creation of replication subscriptions in case a new table was created in one of the databases.

The bi-directional use case enables a full active/active DR solution with simple commands and full control. The application can switch from one site to the other and can work with current data due to the minimal latency and high throughput of Q Replication. Procedures that check that all data has replicated before performing the switch can be implemented easily by querying the replication control tables. In a bi-directional setup, new tables can be added at either side (primary or secondary database). Schema subscriptions guarantee that the table is created at the other side immediately and that replication starts without the need of an initial full load.

The new schema subscriptions and the new automatic DDL replication reduce the administrative cost significantly. Due to the increased complexity of bi-directional replication compared to uni-directional replication, this advantage is even more important for this second use case. The DBA can really focus on more important tasks instead of having to define new replication subscriptions whenever a new table has been created. Another advantage of automatic subscription creation and automatic DDL replication is that the replication subscriptions are created or changed at the right time, before the first insert into the table occurs, or before the first record with the new table format is written, without any application downtime.

Perhaps as important is that automatic processing of changes reduces the risk of failure and application downtime. This applies to disaster recovery solutions, but also to other use cases that rely on data replication.


Download

DescriptionNameSize
Sample ASNCLP and DDL scripts for this articleScripts.zip41KB

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment.
  • IBM InfoSphere Data Replication Dashboard
  • Evaluate IBM WebSphere MQ IBM WebSphere MQ - Free 90 Day trial code. Ensure you experience what open, scalable, industrial-strength messaging can do for your business.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=930062
ArticleTitle=Schema replication with IBM InfoSphere Data Replication, Part 2: Bi-directional schema subscriptions in DB2 for Linux, UNIX, and Windows 10.1
publish-date=05302013