- Components of the solution
- What is a schema-level subscription
- Before you start
- Creating and activating uni-directional schema-level subscriptions
- Operating Q Capture and Q Apply
- Monitoring the replication status
- Automatic creation of additional subscriptions
- Automatic replication of table alterations
- Current limitations
- Conclusion for uni-direction schema subscriptions
- Downloadable resources
- Related topics
Schema replication with IBM InfoSphere Data Replication, Part 1
Uni-directional schema subscriptions in DB2 for Linux, UNIX, and Windows 10.1
Setting up Q Replication schema subscriptions in an active/active scenario
This content is part # of # in the series: Schema replication with IBM InfoSphere Data Replication, Part 1
This content is part of the series:Schema replication with IBM InfoSphere Data Replication, Part 1
Stay tuned for additional content in this series.
In today's on demand marketplaces, companies 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 direct cost of downtime due to planned or unplanned system outages. Even more impacting can be indirect, longer-term effects of downtime.
IT departments usually run various, and more often, multiple high availability and disaster recovery solutions to prevent planned and unplanned outages. Most of the common technologies are hardware-based (and require identical hardware at primary and secondary sites), require idle iron (in case no disaster occurs), require a standby-site at short distance (which does not protect against regional outages), do not protect against application failure, or require a significant amount of time to activate the secondary site in case of an outage of the primary.
Active/Active disaster recovery with Q Replication
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, so that all data flows from site A to site B and vice versa. MQ-based data replication is an interesting alternative or supplement compared to physical DR solutions because of the following:
- There is no distance limitation between the primary and secondary site.
- The standby database is fully active and accessible (read and write),
which allows the following:
- Workload balancing.
- Routing of dedicated tasks (such as dedicated tenants or real time-reporting) to the secondary site.
- Immediate application re-route in case of a disaster.
- Allows support of more than one standby site.
- Allows support of heterogeneous hardware at the primary and standby site, even different OS platforms.
- Allows the possibility of data subsetting.
- Allows the possibility of time-delayed replication.
- Allows support for geographically-dispersed applications, which usually work with local data but switch to a remote site in case of disaster.
- Has no idle iron at secondary site.
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 limits 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.
Components of the solution
IBM InfoSphere Data Replication (IIDR) enables high-volume, low-latency movement of changed data for solutions that range from data warehousing and business intelligence, database migrations, application consolidation, and master data management to high availability, business continuity, and active-active databases.
InfoSphere Data Replication delivers log capture support for a variety of IBM and non-IBM database management systems and contains three major data replication technologies, known as Change Data Capture, Q Replication, and SQL Replication. Users of InfoSphere Data Replication can independently choose among those technologies, or use them in combination.
This article focuses on Q Replication that comes with IBM InfoSphere Data Replication (IIDR). A Q Replication solution consists of independently operable runtime components, Q Capture and Q Apply, MQ as data transport and buffering mechanism, graphical and command line-based administration components, and a browser-based management console named Q Replication Dashboard.
Figure 1 shows how the distributed components of the Q Replication solution interact.
Figure 1. Q Replication component overview
Q Replication Capture
The Q Capture program processes the DB2 recovery log through the standard DB2 log interface, determines relevant log records, and sends MQ messages at transaction level. Q Capture processes all log records as result of DML statements and certain DDL related log records.
Q Replication Apply
The Q Apply program rebuilds the transactions received from Q Capture via MQ, and applies the transactions to target tables or stored procedures. The Q Apply program is designed to keep up with rapid changes by applying transactions in parallel while maintaining data consistency and referential integrity between related target tables.
WebSphere MQ is IBM’s versatile messaging platform. For Q Replication the MQ infrastructure is mainly used to pass captured transactions from Q Capture to Q Apply. Clustered MQ servers can be used to achieve high availability. Q Capture and Q Apply optionally can use MQ client interfaces to connect to the MQ server.
The WebSphere MQ Explorer is an optional component to display the status of queue managers, queues and channels.
Replication Center and ASNCLP
The Replication Center is the graphical front end to interactively define replication subscriptions. As an alternative, the subscription setup can be easily automated using the ASNCLP command language, especially to set up a huge number of replication objects. Both the Replication Center and ASNCLP populate the replication control tables, stored in the source and target database.
Q Replication Control Tables
The Q Replication Control Tables are a set of DB2 tables (some exist at the replication source database and some at the replication target database) which contain the replication configuration, the replication status, and statistical information which can be used for monitoring and tuning purposes. The Q Replication Control Tables can be understood as open interface. Each and every column is documented in the Q Replication literature.
Q Replication Dashboard
The Q Replication Dashboard is a browser-based front end to display all replication subscriptions, their status, alert situations, performance indicators, and so on. The dashboard can be used to automatically alert administrators in case of a failure and contains wizards for certain scenarios.
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. Target tables are optionally created at the target database.
In addition, always when a new table is created at the replication source database, a new table-level subscription is created automatically (including a target table at the target database). The replication mechanism even creates all indexes of the target table which existed at the source database at the time of the first insert into the source table. Optionally, but recommended, is to set an option 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.
Before you start
This section outlines basic database configuration tasks to enable Q Replication (including schema-level subscriptions) and describes the setup of a simple test case to demonstrate the capabilities.
If you like, you can easily replay the test case. For each and every setup step, tested scripts are provided in the Downloadable resources section of this article.
Setup of a simple test case
The test case consists of a primary database called OLTPDB and a newly created secondary database as disaster recovery site, called DRDB. Only dedicated tables contribute to the scenario. In part 1 of this series, you will see how to set up uni-directional Q Replication leveraging the new feature of schema subscriptions. The use case is a 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 off-load reporting and read-only applications at any time. Figure 2 outlines the use case.
Figure 2. Q Replication architecture overview for uni-directional use case
Table 1 displays 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 uni-directional use case
|Schema and Name||Replicate|
Summarizing, the replication solution for the disaster recovery site has to contain all GREEN tables, but not TAB99. TAB99 is a local configuration table. RED tables must not be synched with the recovery site.
To replay the use case, there is a zip file with all scripts in the Downloadable resources section of this article.
DDL script CREATE_2_GREEN_TABLES.DDL creates and populates the first two tables in schema GREEN in database OLTPDB. DDL script CREATE_RED_TABLES.DDL creates and populates all tables in schema RED in database OLTPDB. All other steps which are specific to the replication setup or the definition of schema subscriptions are explained in detail in the following sections.
Configuration of source and target database
The following database configuration parameters should be set at least at the primary database (or at both databases in a bi-directional replication scenario) to enable Q Replication schema subscriptions, as shown in Listing 1.
Listing 1. Source database configuration
UPDATE DB CFG FOR OLTPDB using LOGARCHMETH1 LOGRETAIN; UPDATE DB CFG FOR OLTPDB using LOG_DDL_STMTS YES; UPDATE DB CFG FOR OLTPDB using DFT_SCHEMAS_DCC YES;
- LOGARCHMETH1 LOGRETAIN: A switch to LOGRETAIN (if not set anyhow) turns the primary log files 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 that 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.
Creation of the target database
The commands shown in Listing 2 were used to create the target database target database DRDB.
Listing 2. Target database setup and configuration
CREATE DB DRDB; UPDATE DB CFG FOR DRDB USING LOGARCHMETH1 LOGRETAIN; BACKUP DB DRDB TO /DEV/NULL; UPDATE DB CFG FOR DRDB USING DFT_SCHEMAS_DCC YES; UPDATE DB CFG FOR DRDB USING LOG_DDL_STMTS YES; CONNECT TO DRDB; CREATE SCHEMA GREEN DATA CAPTURE CHANGES; CREATE SCHEMA RED DATA CAPTURE CHANGES; CREATE TABLESPACE GREEN_TS; CREATE TABLESPACE RED_TS;
The SQL statement CREATE SCHEMA schema_name DATA CAPTURE CHANGES indicates that all tables that will be created in this schema are automatically created with the DATA CAPTRUE CHANGES option. This option is required for all tables used as replication source.
Creation of all required MQ objects
DB2 9.7 introduced a very interesting extension to the ASNCLP command language that reduces the complexity of creating the MQ objects required for Q Replication, especially interesting for DBAs with limited MQ knowledge.
The ASNCLP commands shown in Listing 3 generates 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.
Listing 3. ASNCLP commands to create a script for all MQ objects for uni-directional replication
ASNCLP SESSION SET TO Q REPLICATION; CREATE MQ SCRIPT CONFIG TYPE U MQSERVER 1 NAME OLTPDB MQHOST "192.168.8.145" MQPORT 2014 QMANAGER QM_OLTPDB QNAME_QUAL QASN_OLTPDB, MQSERVER 2 NAME DRDB MQHOST "192.168.8.144" MQPORT 2014 QMANAGER QM_DRDB QNAME_QUAL QASN_DRDB;
Note: If you are not yet familiar with ASNCLP, review the documentation on 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>.
Creation of the Q Replication control tables
Before replication subscriptions can be created, the replication control structures (also referred to as control tables) have to be created. The Q Capture Control Tables have to be created in each database that acts as replication source (in this case OLTDB), the Q Apply Control Tables have to be created in each database that acts as replication target (in this case DRDB), as shown in Listing 4.
Listing 4. ASNCLP commands to create the Replication Control Tables
ASNCLP SESSION SET TO Q REPLICATION; SET SERVER CAPTURE TO DBALIAS OLTPDB ID USERID PASSW0RD "PASSW0RD"; SET SERVER TARGET TO DBALIAS DRDB ID USERID PASSW0RD "PASSW0RD"; SET QMANAGER QM_OLTPDB FOR CAPTURE SCHEMA; SET QMANAGER QM_DRDB FOR APPLY SCHEMA; SET CAPTURE SCHEMA SOURCE QASN_OLTPDB; SET APPLY SCHEMA QASN_DRDB; SET OUTPUT TARGET SCRIPT "create_control_tables.sql"; SET RUN SCRIPT LATER ; CREATE CONTROL TABLES FOR CAPTURE SERVER USING RESTARTQ "QASN_OLTPDB.RESTARTQ" ADMINQ "QASN_OLTPDB.ADMINQ"; CREATE CONTROL TABLES FOR APPLY SERVER ;
Creating and activating uni-directional schema-level subscriptions
The following sections explain how to set up the uni-directional replication scenario for the tables described previously in Figure 2. The Q Replication ASNCLP command language is used for each and every setup step, which lets you recreate the scenario easily and is the enabler for an automated replication setup.
Preparation of ASNCLP template
All subsequent replication setup steps require the same ASNCLP header. Therefore, the common header section is documented for all following setup step of uni-directional replication in this code section, as shown in Listing 5.
Listing 5. ASNCLP header used for all following ASNCLP commands
ASNCLP SESSION SET TO Q REPLICATION; SET SERVER CAPTURE TO DBALIAS OLTPDB ID USERID PASSW0RD "PASSW0RD"; SET SERVER TARGET TO DBALIAS DRDB ID USERID PASSW0RD "PASSW0RD"; SET QMANAGER QM_OLTPDB FOR CAPTURE SCHEMA; SET QMANAGER QM_DRDB FOR APPLY SCHEMA; SET CAPTURE SCHEMA SOURCE QASN_OLTPDB; SET APPLY SCHEMA QASN_DRDB; SET RUN SCRIPT LATER ; SET LOG "q_map.log"; SET OUTPUT CAPTURE SCRIPT "capture.sql"; SET OUTPUT TARGET SCRIPT "target.sql";
The ASNCLP header section defines source and target database, the queue managers, and the Q Capture and Q Apply schema. As ASNCLP generates SQL to populate the replication control tables, the header section names the output files which will contain the SQL commands. The option RUN SCRIPT LATER indicates that the 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 a replication queue map
Before a Q subscription (either a schema-level subscription or single table subscription) can be defined, a replication queue map has to be created. The queue map ties together queue names and performance specific settings that define a replication direction, such as send queue, receive queue, admin queue, the number of Apply agents, and so on, as shown in Listing 6.
Listing 6. Creation of a replication queue map
#requires header as listed in Listing 5 CREATE REPLQMAP OLTPDB_TO_DRDB USING ADMINQ "QASN_OLTPDB.ADMINQ" RECVQ "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA" SENDQ "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA";
The command requires the ASNCLP header shown previously in Listing 5.
Validation of Q Replication setup for uni-directional replication
For quality assurance purposes, you will have to use two ASNCLP commands to validate the MQ setup and message flow before defining the first subscriptions.
As shown in Listing 7, the VALIDATE WSMQ ENVIRONMENT command verifies that the required WebSphere MQ objects exist and that they have the correct properties. The VALIDATE WSMQ MESSAGE FLOW command sends test messages that validate the MQ message flow for the replication queue map.
Listing 7. ASNCLP commands to validate the replication setup
#requires header as listed in Listing 5 VALIDATE WSMQ ENVIRONMENT FOR REPLQMAP OLTPDB_TO_DRDB; VALIDATE WSMQ MESSAGE FLOW FOR REPLQMAP OLTPDB_TO_DRDB;
Creation of schema option
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 the example shown in Listing 8, the following schema subscription options are defined.
- SUBTYPE U: Indicates that these options apply to uni-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 where 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.
- CONFLICT ACTION I: Tells Q Apply to ignore replication conflicts. The handling of replication conflicts is less important for uni-directional subscriptions, but very important for bi-directional subscriptions in the second example (2nd part of the series).
- 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, and so on. All changes send by Q Capture are buffered in the queue until the error is fixed and Q Apply continues.
- LOAD TYPE 2 EXIST DATA REPLACE: Lets Apply invoke EXPORT/IMPORT (instead of EXPORT/LOAD or Cross Loader) in case of an initial refresh.
Listing 8. ASNCLP commands to create subscription option
#requires header as listed in Listing 5 CREATE SUBSCRIPTION OPTIONS UNIDIR_STANDARD SUBTYPE U CAPTURE_LOAD W REPLICATE ADD COLUMN Y CONFLICT ACTION I ERROR ACTION Q LOAD TYPE 2 EXIST DATA REPLACE;
Note: Due to an 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 which 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 following SQL update statement 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 shown in Listing 9 after the issue has been finally resolved).
Listing 9. Update table IBMQREP_SUBS
UPDATE QASN_OLTPDB.IBMQREP_SUBS SET REPL_ADDCOL = 'Y';
Creation of schema subscriptions
Ensure the task was to replicate all tables in the GREEN schema except table TAB99. Further more, tables in the RED schema must not replicate in this example, because all tables in the RED schema contain sensitive data. The ASNCLP command (CREATE SCHEMASUB) defines the schema subscription accordingly.
The schema subscription shown in Listing 10 uses the previously defined schema options UNIDIR_STANDARD.
Listing 10. ASNCLP commands to create schema subscription
#requires header as listed in Listing 5 CREATE SCHEMASUB GREEN_SCHEMA SUBTYPE U REPLQMAP OLTPDB_TO_DRDB FOR TABLES OWNER LIKE GREEN EXCLUDE OWNER GREEN NAME TAB99, OWNER RED OPTIONS UNIDIR_STANDARD
Note that flexible options exist to include and exclude tables from schema-level subscriptions. Schema-level subscriptions could be easily defined for patterns like GREEN.MYTAB%, GREE%.%, or %.%. But, for one queue map, schema patterns must not be overlapping, causing that the same table would match two schema-level subscriptions.
Activating the schema subscriptions
A new schema subscription is automatically activated (initialized) when Q Capture starts. In case Q Capture was already active when a new schema subscription was created (and you do not want to stop or restart Q Capture), the subscription has to be activated with the START SCHEMASUB command. The command generates a SQL INSERT into IBMQREP_SIGNAL which let's Q Capture start capturing of all changes for the specified schema.
In our example, Q Capture and Q Apply were not started yet. Therefore, it is not necessary to start the schema subscription with the START SCHEMASUB command, as shown in Listing 11.
Listing 11. Create ASNCLP command to start schema replication
#requires header as listed in Listing 5 START SCHEMASUB GREEN_SCHEMA ALL;
Operating Q Capture and Q Apply
To get the data replication finally going, the next step is to start the Q Capture and Q Apply processes. Several ways exist to operate Q Capture and Q Apply and to specify or change the parameters of Q Capture and Q Apply. You can use the Replication Center, system commands, or system services alternatively. Parameters that influence the behavior of Q Capture or Q Apply can be specified during start up, or can be permanently stored in the Q replication control tables IBMQREP_CAPPARMS or IBMQREP_APPLYPARMS.
In the following sections, all the necessary steps to start Q Capture and Q Apply for the scenario are explained.
Create the encrypted PASSW0RD file
If Q Capture or Q Apply remotely connect to the source or target database, or if Q Capture and Q Apply should connect with dedicated user IDs, an encrypted PASSW0RD file is required that contains connection credentials. Be aware that Q Capture only connects to the replication source database, and that Q Apply connects to source and target databases (to the source database just in case Q Apply performs a full refresh). The PASSW0RD file has to be stored in the working directory of Q Capture and Q Apply or in the path specified by the parameter ASNPATH. The password file can be created or initialized and populated with the command asnpwd, as shown in Listing 12.
Listing 12. Create PASSW0RD 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
Note: The asnpwd command is not an ASNCLP command and therefore does not require the common header.
Q Capture and Q Apply start commands
The asnqcap command starts the Q Capture task. CAPTURE_SERVER is the only mandatory parameter. Other parameters are optional and can be alternatively defined during startup or in the control table IBMQREP_CAPPARMS.
Hint: When starting Q Capture and Q Apply for the first time, it is recommended to start Q Capture first and Q Apply thereafter. If Q Capture and Q Apply have been running for a while, both can be stopped and started independently of each other and no startup sequence has to be maintained.
The command shown in Listing 13 starts Q Capture for database OLTPDB.
Listing 13. Q Capture startup command
asnqcap capture_server=OLTPDB capture_schema=QASN_OLTPDB logstdout=y pwdfile=encrypt.passwd
The following is an explanation of the Q Capture startup parameters.
- CAPTURE_SERVER directs Q Capture to connect to the replication source database.
- CAPTURE_SCHEMA has to be specified if the replication control tables were not created in the default schema QASN. In this use case, QASN_OLTPDB was chosen as Q Capture schema.
- LOGSTDOUT=Y lets Q Capture write all log messages to standard out (additionally log table IBMQREP_CAPTRACE and the log file *.QCAP.LOG).
- PWDFILE=encrypt.passwd specifies the password file to be used as created previously in Listing 12.
- The asnqapp command starts the Q Apply task. APPLY_SERVER is the only mandatory parameter. Other parameters are optional and can be alternatively defined during startup or in the control table IBMQREP_APPLYPARMS.
The command shown in Listing 14 starts Q Apply for database DRDB.
Listing 14. Q Apply startup command
asnqapp apply_server=DRDB apply_schema=QASN_DRDB logstdout=y pwdfile=encrypt.passwd
The following is an explanation of the Q Apply startup parameters.
- APPLY_SERVER directs Q Apply to connect to the replication target database.
- APPLY_SCHEMA has to be specified if the replication control tables were not created in the default schema (QASN). In this use case, QASN_DRDB was chosen as Q Apply schema.
- LOGSTDOUT=Y lets Q Apply write all log messages to standard out (additionally log table IBMQREP_APPLYTRACE and the log file *.QAPP.LOG).
- PWDFILE=encrypt.passwd specifies the password file to be used as created in Listing 12.
If Q Capture and Q Apply are started after the creation of the schema subscription, as in our scenario, they automatically activate the schema subscription and automatically generate table-level subscriptions for all tables corresponding to the schema. As part of the creation of the table-level subscriptions, Q Apply creates the target tables in database DRDB if they do not yet exist. In the described use case, two subscriptions are created for tables GREEN.TAB01 and GREEN.TAB02. According to the subscription naming pattern, the names of the table-level subscriptions are TAB010001 and TAB020001.
As part of the subscription activation, Q Apply performs the initial load of the target tables. This optional behavior was specified as schema subscription option LOAD TYPE 2 EXIST DATA REPLACE.
The following two listings shows the most important log messages issued by Q Capture and Q Apply during the startup phase.
The Q Capture message ASN7247I logs that the schema subscription was successfully activated. Message ASN7010I logs the activation of the automatically created corresponding table-level subscriptions for TAB01 (TAB010001) and TAB02 (TAB020001), as shown in Listing 15.
Listing 15. Capture log snippet after startup
013-04-09-14.22.07.894361 ASN7247I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : The Q Capture program successfully loaded the schema-level subscription "GREEN_SCHEMA" and its corresponding profile. The Q subscription uses replication queue map "OLTPDB_TO_DRDB" and specifies that Q subscriptions should automatically be created in schema "GREEN" for "TAB0%" objects. 2013-04-09-14.22.08.033952 ASN7010I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : The program successfully activated publication or Q subscription "TAB020001" (send queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", publishing or replication queue map "OLTPDB_TO_DRDB") for source table "GREEN.TAB02". 2013-04-09-14.22.08.054131 ASN7010I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : The program successfully activated publication or Q subscription "TAB010001" (send queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", publishing or replication queue map "OLTPDB_TO_DRDB") for source table "GREEN.TAB01".
Listing 16 shows the Q Apply message ASN7528I and ASN7529I log for the initial load of the target table (in this example TAB01). ASN7606I logs that the subscription TAB010001 (for table TAB01) was activated successfully. Q Apply logs the same messages for TAB02.
Listing 16. Apply log snippet after startup
2013-04-09-14.33.14.029664 ASN7528I "Q Apply" : "QASN_DRDB" : "BR00000SP001" : The Q Apply program for the Q subscription "TAB010001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB") will use the "IMPORT" utility to load table "GREEN.TAB01". 2013-04-09-220.127.116.115830 ASN7529I "Q Apply" : "QASN_DRDB" : "BR00000SP001" : The "IMPORT" utility for table "GREEN.TAB01" completed successfully for the Q subscription "TAB010001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB"). The message from the utility is "Rows imported: 100, Rows rejected: 0, Rows skipped: 0". 2013-04-09-18.104.22.1686135 ASN8999D Browser for queue 'QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA' received a 'ASNMQ_LOADDONE_RCVD' message. 2013-04-09-22.214.171.1241637 ASN7606I "Q Apply" : "QASN_DRDB" : "BR00000" : Q subscription "TAB010001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB") is active.
Monitoring the replication status
The overall replication status, the status of all replication objects, and the throughput of the replication processes can be easily monitored using the Q Replication Dashboard. As shown in Figure 3, a summary overview indicates all send and receive queues are active (diamond symbol), that two table-level subscriptions exist within the one queue map, and that all table-level subscriptions are active (diamond symbol).
Figure 3. Initial replication status monitored with the Q Replication Dashboard
The bar graphs display the monitored latency and throughput. The Q Replication Dashboard retrieves the replication status and throughput from the Q Replication control tables.
Alternatively, you can query the replication control tables using simple SQL. The query shown in Listing 17 retrieves all table-level subscriptions (including status) from control table IBMQREP_SUBS at the replication source database OLTPDB.
Listing 17. Subscriptions for schema GREEN
db2 select state, substr(subname,1,10) as subname from QASN_OLTPDB.IBMQREP_SUBS STATE SUBNAME ----- ---------- A TAB010001 A TAB020001 2 record(s) selected.
The query shows the two Q subscriptions TAB010001 (for GREEN.TAB01) and TAB020001 (for GREEN.TAB02). These subscriptions were automatically created by Q Capture and Q Apply as a result of the schema subscription. No subscription was created for GREEN.TAB99 because this table does not match the schema subscription naming pattern.
Automatic creation of additional subscriptions
In this section, you will learn how to automatically add new tables to the replication solution without the need to create new subscriptions manually.
A new subscription for table GREEN.TAB03
To create a new table (GREEN.TAB03), which matches the schema subscription naming pattern, a SQL script CREATE_GREEN_TAB03 is prepared, as shown in Listing 18.
Listing 18. Create addition table GREEN.TAB03
db2 -tvf CREATE_GREEN_TAB03.DDL
Note: The script also is included in the zip file, which can be found in the Downloadable resources section of this article.
After the creation of TAB03, Q Capture 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 GREEN_SCHEMA and immediately creates the new table-level subscription at the source database.
Additionally, Q Capture sends a message to Q Apply to let Q Apply create the subscription-specific metadata at the target database. Q Apply also creates a target table with identical structure as the source table. Q Apply creates all indexes for the target table that existed when the first record was inserted into the source. Q Apply immediately activates the new subscription so that all inserts, updates, deletes after the table creation are replicated instantaneously. No full refresh is required.
Q Capture's log message ASN7210I indicates that the new table-level subscription for table GREEN.TAB03 was automatically created at the source database, as shown in Listing 19.
Listing 19. Snippet of Q Capture log during creation of table GREEN.TAB03
2013-03-28-126.96.36.1995000 ASN7210I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : Q subscription "TAB030001" that corresponds to schema-level subscription "GREEN_SCHEMA" was successfully created for the source table "GREEN.TAB03" that uses send queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA" and replication queue map "OLTPDB_TO_DRDB".
The Q Apply log message ASN7711I indicates that the new table-level subscription for table GREEN.TAB03 was automatically created at the target database, as shown in Listing 20.
Listing 20. Snippet of Q Apply log during creation of table GREEN.TAB03
2013-03-28-188.8.131.526229 ASN7711I "Q Apply" : "QASN_DRDB" : "BR00000" : Q subscription "TAB030001" was successfully created for the target table ""GREEN".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 "GREEN_SCHEMA" was defined at the source.
Summarizing, Q Capture and Q Apply added the table GREEN.TAB03 to the replication setup and started the replication of the new table immediately, without any administrator intervention or application downtime. This automatic operation will occur for all new tables matching the schema subscription's naming pattern. The automatic operation not only significantly reduces the administration efforts, but also reduces the risk of errors and downtimes.
Monitoring the replication status
Again, you can use the Q Replication Dashboard to review the replication status. The Q Replication Dashboard immediately adds the new subscription to the overview page. If you compare Figure 4 with the summary shown previously in Figure 3, you will notice that the subscription counter has been increased from 2 to 3.
Figure 4. Q Replication Dashboard after adding table GREEN.TAB03
Notice that all three subscriptions are active, which is visualized by the diamond symbol.
As already explained, the Q Replication Dashboard can be used for a further investigation of all Q Replication objects (q maps, queues, q depth, subscriptions, source and target tables, and so on) and for the monitoring of the Q Replication status and throughput. See the Resources section for a link to a Q Replication Dashboard tutorial that reviews all options in more detail.
You can use the Q Replication Dashboard next to review the state of the schema subscription GREEN_SCHEMA. If you open the Subscriptions tab of the Q Replication Dashboard, all existing subscriptions will be displayed. For schema-level subscriptions, it is possible to display all table-level subscriptions grouped by the replication schema. Notice that in Figure 5, the Group by schema-level subscription check box is selected.
Figure 5. Group by schema-level subscription
In this case, you can see the properties of the schema-level subscription (schema pattern, object pattern, schema options, and so on) and all table-level subscriptions that were automatically created according to the replication schema. As shown previously in Figure 5, you see three table-level subscriptions. The diamond symbol shown in column State indicates that all three subscriptions are active and replicating.
More details can be displayed when following the hyperlinks in the Dashboard view. For example, if you click the UNIDIR_STANDARD link, (the schema subscription profile name), all options of the schema profile are displayed. Figure 5 displayed the schema options which were defined in Listing 8. Some of those were not explicitly specified when creating the profile. The default is displayed instead, as shown in Figure 6.
Figure 6. Profile information for defined schema option
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 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.
For the described use case, let's assume that table GREEN.TAB01 needs additional columns. The ALTER TABLE statement shown in Listing 21 adds four columns to the table GREEN.TAB01, which is already being replicated.
Listing 21. Add columns to table GREEN.TAB01
ALTER TABLE GREEN.TAB01 ADD COLUMN VALID_DESC CHAR(5) ADD COLUMN IS_VALID CHAR(1) ADD COLUMN VALID_START DATE ADD COLUMN VALID_END DATE;
After the execution of the ALTER TABLE statement, Q Capture and Q Apply will automatically add the new columns to the existing subscription. The DDL statement is replicated to the target site and Q Apply automatically adds the columns to the target table.
As shown in Listing 22, Q Capture's log message ASN7209I indicates that a new column was automatically added to an existing subscription.
Listing 22. Snippet of capture log during adding columns on table GREEN.TAB01
2013-04-09-184.108.40.2062765 ASN7209I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : Column "VALID_DESC" of table "GREEN.TAB01" was automatically added to publication or Q subscription "TAB010001". 2013-04-09-220.127.116.113950 ASN7209I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : Column "IS_VALID" of table "GREEN.TAB01" was automatically added to publication or Q subscription "TAB010001".
As shown in Listing 23, Q Apply logs the same with message ASN7612I.
Listing 23. Snippet of apply log during adding columns on table GREEN.TAB01
2013-04-09-18.104.22.1685914 ASN7612I "Q Apply" : "QASN_DRDB" : "BR00000" : Column "VALID_DESC", has been added to Q subscription "TAB010001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB"). 2013-04-09-22.214.171.1246721 ASN7612I "Q Apply" : "QASN_DRDB" : "BR00000" : Column "IS_VALID", has been added to Q subscription "TAB010001" (receive queue "QASN_OLTPDB.OLTPDB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB").
Another example of DDL replication could be the change of a data type for an existing column. Let's assume that the column VALID_DESC was created with a wrong data type (CHAR(5)). The new data type should be a VARCHAR(20), as shown in Listing 24.
Listing 24. Alter column VALID_DESC for table GREEN.TAB01
ALTER TABLE GREEN.TAB01 ALTER COLUMN VALID_DESC SET DATA TYPE VARCHAR(20);
After the execution of the previous statements, Q Capture and Q Apply will automatically replicate the ALTER TABLE statement to the target database, and will also change the subscription metadata accordingly.
As shown in Listing 25, Q Capture's log message ASN7208I indicates that a column alteration was detected and that the subscription was automatically changed.
Listing 25. Snippet of capture log after alter column VALID_DESC on table GREEN.TAB01
2013-04-09-15.06.38.795886 ASN7208I "Q Capture" : "QASN_OLTPDB" : "WorkerThread" : An ALTER TABLE ALTER COLUMN statement was detected for column "VALID_DESC" of table "GREEN.TAB01" with a new data type of "VARCHAR(20)". The column was automatically altered for Q subscription "TAB010001"
As shown in Listing 26, Q Apply logs the same with message ASN7718I.
Listing 26. Snippet of apply log after alter column VALID_DESC on table GREEN.TAB01
2013-04-09-15.06.38.989796 ASN7718I "Q Apply" : "QASN_DRDB" : "BR00000" : Column "VALID_DESC" has been altered in table "TAB010001" (receive queue "QASN_OLTPDB.OLTPB_TO_QASN_DRDB.DRDB.DATA", replication queue map "OLTPDB_TO_DRDB")
The following are the current limitations with regards to schema-level subscriptions and automatic DDL replication in Q Replication as part of InfoSphere Data Replication V10.1.3.
- InfoSphere Data Replication V10.1.3 supports schema-level subscriptions only for DB2 for Linux, UNIX, Windows platforms.
- DDL replication (ALTER TABLE) is supported for DB2 for Linux, UNIX, Windows and for DB2 for System z as well.
- If a table-level subscription is automatically created due to a matching schema-level subscription, all indexes are created at the target database that existed when the first record was inserted into the source table.
- Indexes created later will not replicate to the target database.
Supported ALTER TABLE statements:
- ALTER TABLE ... ADD COLUMN ...
- ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE.
- Other ALTER TABLE statements do not replicate or lead to replication errors.
Target table types:
- Schema subscriptions do not yet support CCD or History target tables.
- Create Tablespace does not replicate automatically. Create new tablespaces in source and target database.
Range partitioned tables:
- ADD, ATTACH, and DETACH do not replicate automatically.
See the Resources section for a complete list of more general limitations on schema-level subscriptions.
Conclusion for uni-direction schema subscriptions
In this article, a scenario has been outlined that uses Q Replication as a synchronization mechanism for an active/active disaster recovery solution. We have also demonstrated how easy it is to maintain and manage data replication with new features like schema-level subscriptions and DDL replication, which were delivered with IBM's Q Replication technology as part of IBM InfoSphere Data Replication V10.1.3. We consider schema subscriptions and automatic DDL replication very useful improvements because the automatic maintenance of the replication solution reduces the administration efforts, and therefore the total cost ownership of the solution significantly. Just imagine a common application release which easily contains hundreds of changes to the database schema.
Perhaps as important is that automatic processing of changes reduces the risk of failure and application downtime. This especially applies to disaster recovery solutions, but also to other use cases that rely on data replication.
Schema subscriptions and DDL replication make replication maintenance very easy.
Finally, if you liked this article, read Part 2 of the series, which extends the use case of active/active disaster recovery to bi-directional schema replication. Bi-directional replication allows for immediate switch and switch back because replication subscriptions are defined from the primary database to the standby site, and vice-versa.
- Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
- Refer to the "Schema replication with IBM InfoSphere Data Replication, Part 2" developerWorks article.
- Refer to the Creating schema-level subscriptions section of the IBM DB2 Information Center for a complete list of general limitations.
- Refer to the IBM DB2 Version 10.1 Information Center for more information.
- Refer to the "InfoSphere Data Replication (Q Replication)" roadmap for more information.
- Read the " Replication live: Use the Q Replication Dashboard for real-time monitoring" developerWorks tutorial.
- 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.
- 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.