Should You Switch from IBM's SQL Replication to Q Replication?

SQL Replication is a data replication technology that's been used with great success in many shops for over 20 years. Yet, IBM also provides the Q Replication technology that has developed an excellent reputation for high performance, continuous availability, and zero-downtime database migration. Naturally, that leads SQL Replication users to ask if they should switch from SQL to Q. The answer, like the answer to many other questions, is... "it depends." :)


You have your choice of using the best technology for your requirements. I'll use this post to list the top reasons I hear from people switching to Q Replication from other replication technologies.  I'll also list the top reasons people give for staying with SQL Replication.


In a Nutshell


First, as I said, Q Replication can be used for many things, but people tell me they switch to it from other technologies primarily for one or more of the following reasons:

  • Q has the lowest overall CPU consumption (lowest impact), especially on source systems.
  • Q is the most robust solution for Active-Active Databases and Continuous Availability.
  • Q provides the lowest latency of data in target databases.
  • Q delivers the highest volume (highest throughput) of changed data to target databases.

Next, for contrast, people tell me they prefer SQL Replication over other technologies primarily for one or more of the following reasons:

I'll use the remaining headings of this post to talk a little more about these.


Q for Performance - Overhead, Latency, Throughput


Q Replication can't be beat for performance. It provides low latency, high volume replication and has a low overhead, especially on source systems. Equally as important is that IBM is continually looking for ways to improve on what Q does. Of course, these are the things people usually think when they think performance. However, Q Replication also offers excellent performance monitoring through its Q Replication Dashboard. IBM's been making regular updates to this tool to enhance the value of your Q Replication solution. For example, one recent addition is a Performance Advisor to help you with understanding and tuning Q Replication performance.


One last point about performance - remember that your results may not be the same as someone else's.  That's because many factors affect data replication performance.  For example, if you use mainframe DB2 z/OS's Not Logged tablespaces with SQL Replication, you'll likely see less of a difference between SQL and Q Replication performance than if you don't use this type of tablespace.  Second, there are no benchmarks for data replication.  That means, to get an accurate estimate of the benefit to you, you'll really need to do a proof-of-concept with a production-like workload .


Q for Active-Active and Continuous Availability


Q Replication has many features that make it superior to anyone here.  Performance, conflict detection and resolution, tooling and techniques for failover and switchback, statistical data, and health monitoring all stand out compared to other solutions. It works so well that it's become the basis for integrated multi-site availability offerings such as IBM's new GDPS Active-Active solution for z/OS and the one for DB2 pureScale described in a recent white paper.

To compliment this information, there were some excellent sessions about Q Replication at IBM's IOD conference in Las Vegas last year.  If you missed them, you can still access the presentations at  These show what people are doing with Q for availability.  They include:


There was also a detailed discussion by the senior Q Replication architect, Serge Bourbonnais.


SQL for Data Distribution


If you need to replicate data between a central site and many other locations, I have yet to see a better model than SQL Replication's.  Changed data is pulled to target systems using SQL and standard database client-server interfaces.  That means it can scales on the source like any other database application.  You can add new targets without impacting existing targets or recapturing data on the source.  Equally as important, an outage at one target does not affect the other targets.  What more, it's two tier, source and targets only.  An optimal solution does not require a middle tier between as is recommended with many other solutions.  And, you can do one-way or two-way replication between the source and targets.  


SQL for DBA Friendliness


Many DBA's love the fact that they can see changed data if they need to.  It's staged in tables, which means they can query it to verify it's moving or modify data to correct a problem before it goes to the target system.   It can easily be integrated with other database function such as DB2 LUW's HADR.  SQL Replication also tends to get more advantage from database enhancements than the average replication technology.  For example, if your source is a mainframe (z/OS) DB2, SQL Replication can benefit from the previously mentioned Not Logged tablespaces and possibly from zIIP offload of SQL work since the SQL Apply program pulls data to target systems via DRDA over TCP/IP (i.e., the same way ERP and CRM apps can benefit from zIIP).


SQL for Cost


Many people don't switch from SQL Replication because they don't want to incur the cost of retraining and rebuilding infrastructure for a new replication technology.  For others, SQL Replication is a no-brainer because it's included at no additional cost in DB2-based products on UNIX and Windows.  However, Q Replication has gained a little of this advantage due to the fact that two-DB2 Q is no extra cost in DB2 Advanced ESE and InfoSphere Warehouse 9.7.2 and higher.


Evaluating Your Options 


Consider three things when evaluating your options.

  1. Q and SQL Replication have a lot in common. For example, tools like the Replication Center, asnclp, the Replication Alert Monitor, and the data comparison utility (asntdiff). You'll also find that the control tables and commands are very similar. These make the transition a little easier.


  2. If you have one-way SQL Replication today, there's a sample on IBM's developerWorks that can help you evaluate how easy it would be for you to move from SQL to Q Replication.


  3. As I mentioned earlier, I always recommend a proof-of-concept with a real-world workload when trying out a data replication technology.




Views: 3511


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service