Modified by QUTY_Dylan_Murphy
IBM has a comprehensive new Redbook for Q Replication that should appeal to both new and long-time users. It builds on a Redbook published several years ago, but is not a simple dusting off of an old book. Instead, the writers took the time to pick the brains of experts at IBM's Silicon Valley Lab and add information that's never been published. They've also worked hard to make sure the entire Redbook is easily understood by new users. One example I like is this new graphic that shows the 10 factors that affect end-to-end data replication latency:
People always want to understand this stuff, but it's never been shown so clearly, especially the details around MQ. To make sure you follow the graphic, the Redbook discusses each numbered point individually. Another area I like is the chapter called "Managing Q Replication in a DB2 z/OS Environment". Lots of good stuff there. I believe it will be a huge help to mainframe shops that have Q Replication in production.
You can read or download the Redbook at the following link:
Understanding and Using Q Replication for High Availability Solutions on the IBM z/OS Platform
We have various QREPLICATION Peer to Peer setups, with some tables over 20 gb in size. There are times we have identified were the two sets of tables need to be synchronized. What we have been doing is running ASNTDIFF / ASNTREP on one set of the tables, and then repeating the process on the other set of tables.
Because of the size of the tables we have found that ASNTDIFF / ASNTREP is an inefficient process to get the qreplication peer to peer tables synchronized.
Does anyone have any alternative, performance oriented solution to ASNTDIFF / ASNTREP?
A new WebSphere MQ enhancement may give Q Replication a significant throughput boost in those situations where you have either (1) workload spikes that increase replication latency or (2) changed data that has built up in queues because of the temporary unavailability of a target database. The new feature is tailored for the sequential access of messages and causes MQ to read messages into the MQ buffer before the messages are needed. This method lets both the Q Apply program and MQ channel agents pull messages from memory instead of disk.
Why is this possible? When the number of messages overruns the buffer pool allocation for the queue, messages are spilled to disk and must then be retrieved from disk when needed. With the read-ahead enhancement, messages can be in memory. In addition to greatly improving throughput in these situations, the enhancement lowers overall replication latency. This new MQ feature also benefits performance at the source transmission queue.
In our test, we achieved the 55 percent increase in rows per second processed by Q Apply on a DB2 for z/OS Version 9 non-data sharing system with Q Replication Version 10.1 and WebSphere MQ Version 7. The average message size for the test was 50K. We have observed significantly higher improvements in replication throughput with smaller messages, for example 10K. Your throughput numbers could be noticeably different from these because replication performance can be heavily influenced by environment, row lengths, replication configuration, and more.
The buffer pool read ahead enhancement is available starting with WebSphere MQ V7 APAR PM63802. You can enable the function by issuing the following MQSC command:
RECOVER QMGR(TUNE READAHEAD ON)
You can add the read ahead function to both the source and target queue managers for optimal performance. No changes are required to your Q Replication environment to take advantage of this feature.
For more information, see the text for APAR PM63802
While it's true that DB2 z/OS v7 has been out of service since 2008
, that doesn't mean no one's using it :) We know because we regularly get questions about whether any IBM data replication product still supports it. Some people want to use replication as a database migration tool (e.g., for moving from for DB2 v7 to DB2 v9). Others just want a typical data replication solution such as copying data to another system for reporting purposes. Whatever the situation, IBM provides WebSphere Replication Server for z/OS v9 (5655-R55) to support DB2 z/OS v7
. You can use either Q or SQL Replication*. These are both compatible with with the Q and SQL Replication found in newer versions of IBM replication products. For example, you could replicate data between DB2 z/OS v7 and DB2 z/OS v10 by having WebSphere Replication Server v9 running with DB2 v7 and InfoSphere Data Replication v10 running with DB2 v10. Here's a picture of what that looks like:
Note that IBM's newest replication product, InfoSphere Data Replication for DB2 for z/OS v10
, does not support DB2 z/OS v7. The same is true of latest versions of InfoSphere Replication Server for z/OS and InfoSphere Change Data Capture for z/OS, both of which are part of the new Data Replication product. Neither support DB2 z/OS v7.
* Both Q and SQL Capture work with z/OS V1.4 as well.
In my previous post, I showed which IBM products contain Q and SQL Replication. However, I left one question unanswered. What's the difference between InfoSphere Replication Server and the IBM Homogeneous Replication Feature (HRF)? The answer is simple. The HRF is just a tool to license a subset of Replication Server found in DB2 Enterprise and InfoSphere Warehouse.
The HRF was introduced with DB2 9.5 and provides what's shown in the following table.
Licensing Differences - Replication Server vs the HRF
|Product||SQL Replication||Q Replication|
|InfoSphere Replication Server|| || || || |
|IBM Homogeneous Replication Feature|| |
In other words, Replication Server provides Q and SQL Replication for all types of databases - DB2, Oracle, and so on. That diversity of databases is what's called 'heterogeneous' replication. On the other hand, the HRF is a way to buy just the subset of Q Replication that's for DB2 databases. This subset is called 'homogeneous' because it licenses components for only one type of database - DB2.
This functional difference also means the HRF has a cheaper per PVU price. Or at least it still did when I started this post :) If you want to verify this yourself, check Passport Advantage or go to the product web pages on ibm.com and look at the features of DB2 ESE and the prices for Replication Server.
A related difference is that the HRF really is a feature instead of a product. As such, it can be purchased for any of the following IBM products:
This feature aspect also affects how you calculate the total cost of a replication purchase. Specifically, there is a metric called Processor Value Unit (PVU mentioned above) that is a factor in determining the overall price of a replication purchase. The metric is calculated differently for the HRF than for Replication Server.
- For the HRF, the metric is the same as the PVU count for the database for which the feature is purchased.
- I've always been told that DB2 features are based on the same PVU count as DB2 and are not charged for on warm standby systems. (DB2 people are always the best ones to verify the current state of charges for DB2 features.)
- There is no requirement to count the PVU of remote source or target databases (if you do remote capturing or remote applying with the HRF).
- For Replication Server, the metric is based on the system where the software runs, not the PVU rating of any source or target database.
The last point here is that Replication Server and the HRF are completely compatible. In other words, the Capture program in one can provide changed data to the Apply in the other and vice versa. The following picture is a very simple example that illustrates the compatibility of the two.
Replication customers who are using DB2 Version 9.1 for z/OS compatibility mode (CM) or lower and want to upgrade from WebSphere Replication Server V9.1 to InfoSphere Replication Server V10.1 must ask their IBM sales representative to request a copy of the software through the IBM z/OS archive-retrieval process.
IBM has removed all Replication Server products from marketing, and the Q Replication and SQL Replication technologies are now part of IBM InfoSphere Data Replication for z/OS.
WebSphere Replication Server V9.1 is scheduled to be withdrawn from support in September 2014. However, customers who want to upgrade to the newest version of Q Replication or SQL Replication in InfoSphere Data Replication V10.2.1 must be running DB2 V9.1 for z/OS new function mode (NFM) or higher.
If you are running with DB2 V9.1 for z/OS CM or lower, you need to request InfoSphere Replication Server V10.1 (5655-IRS) through the archive-retrieval process. Replication Server V10.1 is not available from Shopz. You must be current on IBM Software Subscription and Support to move from Replication Server V9.1 to Replication Server V10.1.
Ask your sales representative to use the Withdrawn Order Process and Template for Archived Product Code. You can point them to more information at the following internal IBM website: https://w3-connections.ibm.com/blogs/dbafaebb-0ed1-4425-8396-47f73dbb57f6/entry/is_replication_server_v10_z_os_still_available_to_current_replication_server_v9_customers?lang=en.
For more information about migrating to Q Replication and SQL Replication Version 10.2.1, see this presentation.
I've recently posted a problem with a dpropr update anywhere configuration not replicating from the source to the master. That is being addressed. The purpose of this post is how to replicate db2xml columns. (DB2XML has been replaced with purescale xml)
My questions are centered around purescalexml. Given that we need to convert from db2xml to purescale xml:
1) is there a reference guide on how to convert db2xml to purescale xml?
2) purescale xml is defined to a db2 table as data type xml
-- is data type xml supported by dpropr (sql replication, qrep, or HADR). The replication guide. states db2 xml not supported, but is this db2xml or purescale xml data type?
replication manual data type restrictions
Data type restrictions
SQL Replication cannot replicate the following data types:
v DB2 XML
This post is not
about whether you need some kind of event publishing* function for DB2. It's about whether you already have the function found in InfoSphere Data Event Publisher and just don't know it.
InfoSphere Data Event Publisher is built from IBM's Q Replication technology. More specifically, it provides a subset of the function found in the Q Capture program. The question is, if event publishing is a subset of Q Capture and I already have Q Replication, do I need to buy Data Event Publisher? The answer is almost always no. Why "almost always"? Q Replication is found in several IBM products and features
. For example, no-cost, two-site Q Replication
was recently added to certain editions of DB2 and InfoSphere Warehouse. Some of these products have license restrictions on how Q Replication function can be used. I have the products listed here along with the answer about whether you need to buy Data Event Publisher with it:
- InfoSphere Data Replication or InfoSphere Replication Server - No.
- You do not need to buy Data Event Publisher. The function is included in all Replication Server products. This is true for z/OS and LUW. The LUW product can publish from Oracle sources as well.
- The IBM Homogeneous Replication Feature - Maybe.
- Event publishing for DB2 databases is included. However, if you want to publish from Oracle sources, you must buy either InfoSphere Data Event Publisher of InfoSphere Replication Server.
- The free two-site Q Replication in DB2 and InfoSphere Warehouse - Maybe.
- Event publishing can only be used to set up replication of data from one DB2 LUW database to another. If you want to publish changed data for any other destination, you must buy either InfoSphere Data Event Publisher or InfoSphere Replication Server.
Wait... what? I can do replication with Q Capture's event publishing? Yes :) but the primary reason you would consider this is when you want to run data through a transformation engine such as InfoSphere DataStage and don't want to run an apply program at the target or stage data in tables or files. The following picture shows an example of what I mean:
* For those of you who aren't sure what data event publishing is, it's function that lets you capture changed data from a database log and publish it to consuming applications. The format of the published data is such that you can determine transaction boundaries, see the SQL operations (insert, update, delete) as well as the order they occurred, and have both before and after values for updates. Depending on the tool you use, data can be published to relational tables, WebSphere MQ, or flat files. The consuming app (or apps) can be one you develop such as for SOA or an off-the-shelf app like InfoSphere DataStage.
This post focuses on publishing to WebSphere MQ queues because that's what's found in InfoSphere Data Event Publisher.
(Oh, I can hear the outcry now... people saying that no one's ever used the term event publishing when the destination is tables or files. Seriously? Those are just staging mechanisms. They don't change the end result. You still get data events published. And those events are still available to consumers. For comparison, see the IOD 2010 presentation about providing changed data to InfoSphere DataStage via tables
No, not if you have InfoSphere Data Replication
or InfoSphere Replication Server on UNIX or Windows. On those operating systems, Data Replication and Replication Server contain a complete set of IBM's data federation technology for use in data replication solutions*. For example, you can replicate to a PostgreSQL
or an Oracle database using the Federation Server technology found in the Replication Server install. If you have any other data replication product, you may be able to use a copy of Federation Server to go to target databases not supported by that replication product. You'd need to work with an expert for that product to know for sure. Note also that, if you have Federation Server, the Federation Server license
does not allow general use of the replication components found in Federation Server. That replication technology can only be used in support of building a database cache using cache tables
* Data Replication and Replication Server solutions for mainframe data sources such as IMS and VSAM (called 'Classic' data sources) require a 'Classic' data replication product instead of data federation. Therefore, the mainframe federation technology for Classic data (Classic Federation Server) is not included in the purchase of Data Replication or Replication Server on UNIX or Windows.
I've gotten this question a lot lately. It's usually from people who want to move from DB2 ESE to DB2 Advanced ESE (AESE). They want an answer from both a technical perspective (will I need to migrate?) and a licensing perspective (do I still need my current replication product?). The answer to these questions, like the answer to so many other questions, is :) "it depends." I'll use this post to cover the points from both perspectives.
You need to answer one question - are you changing versions of DB2 when you upgrade to DB2 AESE? For example, are you moving from DB2 ESE 9.7 to DB2 AESE v10? If the answer is, no, you are not changing your DB2 version, then the answer to the Q Replication question is, no, Q Replication is not affected by the upgrade to DB2 AESE. This is because AESE changes DB2 license terms related to Q Replication, not the DB2 install, the Q Replication code, or the DB2 function that Q Replication uses.
On the other hand, if your answer is, yes, you are changing DB2 versions, then the change will likely require a migration of Q Replication. To know for sure, you need to consider whether you're using the copy of Q Replication in your DB2 install or a separate copy of Q Replication that was installed through a product such as InfoSphere Replication Server (IRS) or IBM InfoSphere Data Replication
- The other case is that the you installed a separate copy of IRS or IIDR and are using the Q Replication in it to work with your DB2 instance. For this case, the Q Replication code only needs to be upgraded if it is capturing changes from DB2 v10 (applying changes to DB2 is not affected). Again, see the DB2 v10 Information Center for information about upgrading/migrating Q Replication.
One of the major benefits of upgrading to DB2 AESE is that you get two- or three-site Q Replication at no additional cost. That's important to licensing because, before DB2 AESE, you had to purchase Q Replication for DB2 through one of three products - the IBM Homogeneous Replication Feature (HRF), InfoSphere Replication Server (IRS), or IBM InfoSphere Data Replication (IIDR). The question is - do you still need to maintain a license for a data replication product? The answer depends on the configurations you've built with Q Replication. Specifically, the DB2 AESE license covers your current Q Replication needs for a given DB2 AESE server if you are using the Q Replication in DB2 AESE and one of the following is true:
- Your DB2 AESE server is 9.7 and you are only replicating with one other DB2 LUW server (of any release).
- Your DB2 AESE server is v10, and you are replicating with no more than two other
DB2 LUW servers (of any release).
Recently a dpropr (sql replication) v10.1 was setup in an update anywhere configuration. A refresh was done from the master databases (whos_on_first='S')
I performed the following tests that show replication from the master to the replica works, but replication from the replication does not work. Because the replica capture read the change and inserted the record to the associated CD table, I believe the issue is with the apply whos_on_first='F'
performed table refresh to verify in update anywhere refreshed from master tables to replica tables. This was succesful.
changed a table row on master table (whos_on_first='S'). Data replicated to replica table.
on replica table updated the same row changing the value back to its original master table value.
-- Capture read the change to the cd table
-- Applytrail shows no data for whos_on_first='F replicating to the master table, though it shows successfull replication cycles.
How do I resolve and get the dpropr apply whos_on_first='F' to replicate data back to the master table?
The absolute best answer to this question is in the IBM Distributed Software Licensing Reference Guide. It can be downloaded from the Passport Advantage Licensing home page on ibm.com
(see picture below). The document contains a general discussion about Hot, Warm, and Cold Standbys for IBM distributed software. While it does not mention IBM InfoSphere Data Replication (IIDR) specifically, the discussion applies to IIDR because IIDR does not have its own unique license terms in regards to standby servers. If you read the guide and have a question in relation to IIDR, feel free to post it in the comment section of this blog entry.
The following is an excerpt from that document as of the date of this post. It's just here just to give you a feel for what to look for in the Reference Guide. You shouldn't view this post as a replacement for reading the Guide since the Guide could change .
This post is the answer to one of the FAQs found in License Tips for IBM Data Replication
If you download IBM products from Passport Advantage (PPA), you may now have access to IIDR but don't know why. The reason may be that all of the following conditions are true for your shop:
- You had an older IBM replication product on UNIX, Windows, or System i.
- You were current on S&S (subscription and support).
- You were unaware of the automatic conversion to IIDR on March 12, 2013 :)
If you don't understand, keep reading... In December of 2012, IBM announced that it would no longer market the following three data replication products after March 11, 2013:
- InfoSphere CDC
- InfoSphere Replication Server
- InfoSphere Data Event Publisher
The announcement also said that IBM InfoSphere Data Replication (IIDR) would be the replacement product. IIDR contains all replication technologies from all three of the older products
. However, the announcement did not say how you get IIDR if you already own one or more of the older products. Well, it turns out that IBM automatically converted your older product(s) on UNIX, Windows, or System i to IIDR on March 12, 2013 as long as you were current on S&S. There was no announcement. The only way you'll see this is if you look in your PPA account. It should now list IIDR.
If you are unfamiliar with IBM's changes to its data replication portfolio, see either of the following posts for more information:
Feel free to post comments if you have questions.
I recently posted a white paper on developerWorks
and a blog on ChannelDB2
that talk about using Q Replication with HADR. One obvious follow-on question is the title of today's post. People want to know what they need to purchase to be in compliance with IBM license terms* when they use Q Replication with HADR.
The best answer is found in the IBM license information document for the product that provides you Q Replication (remember that Q Replication comes in several different IBM products). These documents are maintained on ibm.com. However, most people don't know how to navigate them :) so I'll provide links to the most current (as of this post) for each product** and my reply. I'll also use the following picture. It came from the previously-mentioned white paper and blog.
Replication Server has Hot, Idle/Warm, and Cold Standby terms as do DB2 and InfoSphere Warehouse. Data replication products cannot process transactions on an HADR secondary. That means the HADR secondary is a Cold Standby from Replication Server's perspective. You need to purchase Replication Server for the HADR primary, but not the secondary.
IBM InfoSphere Data Replication (IIDR) 10.1 is a packaging of InfoSphere CDC and InfoSphere Replication Server
. The IIDR license basically says that it provides unrestricted use of these two products and that the terms in those licenses apply unless overridden by IIDR's. In the case of HADR, the Replication Server Hot, Idle/Warm, and Cold Standby terms apply to Q Replication. That means you have the same answer that I wrote for Replication Server above: Data replication products cannot process transactions on an HADR
secondary. That means the HADR secondary is a Cold Standby from
Replication Server's perspective. You need to purchase Data Replication for the HADR primary, but not the secondary.
If you have questions, feel free to post them to the message board of this group.
Modified by DavidT
With a mere 4 weeks until IBM's 2013 Information on Demand, the data replication team thought it might be helpful to have a complete listing of all data replication sessions at IOD. From client presentations and our product roadmap to sneak peeks at new IBM Data Replication functionality, our sessions run the gamut!
Simply take a gander at the sessions below then go to the IOD agenda builder, click on Create Sign In, and then enter your confirmation number and the email address that you used to register for the conference. Create your agenda today!