RECOVER QMGR(TUNE READAHEAD ON)
IBM Data Replication's Q and SQL news.
ReplPerformanceTeam 270005S618 Tags:  q_replication performance db2 mq replication 3,409 Views
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.
For more information, see the text for APAR PM63802.
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:
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.
IBM's Data Replication product (IIDR) is now part of IBM's Request for Enhancement (RFE) community. This gives you [the customer] an opportunity to collaborate directly with the IBM product development teams and other product users. That's a quote from the RFE Community site. If you're not familiar with RFE, the site's main page has FAQs, tutorials, and more to help you get started. Once you're ready to go, InfoSphere Data Replication is found under IBM's Information Management brand along with products such as DB2 for z/OS. From the RFE main page, simply click on "Information Management" in the "Brands" box on the right After that, you'll see a pull-down that will let you select IIDR. Click the arrow on the right. Once you have IIDR, click Submit RFE:
If you browse IIDR requirements today (as of this post), you'll only see a few RFEs since this is new. You change that by submitting a requirement :) Once you start that process, you'll need to select a component and so on. The following screenshot shows some of what you'll need to provide:
Notice that the components are not specific to a technology such as Q Replication or CDC. You can add information about that in the description or use case. Have fun :)
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 (IIDR):
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:
For any other Q Replication configuration with that DB2 AESE server, you need to license an IBM data replication product. For more information and some examples, see the posts titled Is Q Replication Free? and When Do I Need Buy Q Replication?
DavidT 120000JC6D Tags:  homogeneous_replication_f... licensing db2 what_do_i_need data_replication 5,715 Views
This post is the answer to one of the FAQs found in
Over the years, IBM has provided its data replication technologies through a lot of different products. That can be a little confusing, even to some IBM people :) So, to simplify things, IBM has consolidated it's replication technologies into a single product. It's called IBM InfoSphere Data Replication (IIDR). To go along with that, IBM is also reducing the number of products it uses to sell those technologies. That affects the IBM Homogeneous Replication Feature (HRF) for DB2.
To be clear, the HRF was an alternate way to buy Q Replication for DB2 LUW and InfoSphere Warehouse (ISW), 9.5 and 9.7. That is no longer available for new purchases. The same is true for v10. The affect on you depends on whether you are (1) new to Q Replication with DB2/ISW or (2) an existing 9.5 or 9.7 HRF customer:
* Alternatively, InfoSphere Replication Server (IRS) is still available for use with DB2 LUW 9.5 and 9.7. However, IRS is not available for DB2 LUW v10. You must use IIDR with DB2 LUW v10.
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.
If you have questions, feel free to post them to the message board of this group.
* If you need help understanding DB2 LUW licensing for HA, see the developerWorks article titled Licensing distributed DB2 10.1 servers in a high availability (HA) environment.
** I do not discuss the IBM Homogeneous Replication Feature (HRF) for DB2 ESE because it is no longer available for purchase.
DavidT 120000JC6D Tags:  cdc q_replication db2 data_replication zos sql_replication 2 Comments 4,579 Views
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.
DavidT 120000JC6D Tags:  what_do_i_need licensing infosphere sql_replication cdc federation replication db2 q_replication database oracle replication_server change_data_capture 4,685 Views
This post is the answer to one of the FAQs found in
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.
DavidT 120000JC6D Tags:  db2 licensing homogeneous_replication_f... replication_server what_do_i_need q_replication infosphere_warehouse 5,025 Views
This post is the answer to one of the FAQs found in License Tips for IBM Data Replication.
A few posts back, I answered the question "Is Q Replication Free?" Or, at least I thought I did :) To clear up some lingering confusion, people have asked me to give examples of when they need to buy Q Replication for DB2 Advance Enterprise Server Edition (AESE) and InfoSphere Warehouse. I'll do that in this post using these examples:
All examples are applicable to InfoSphere Warehouse in addition to DB2 AESE. In other words, you can substitute "InfoSphere Warehouse" wherever you see "DB2 AESE." If you have DB2 v10 servers, you get more no-cost Q Replication than was available in 9.7. This examples highlight this where applicable.
Replicating Between DB2 AESE and DB2 z/OS
You must buy IBM InfoSphere Data Replication (IIDR)*. You cannot use DB2 AESE's no-cost Q Replication to replicate with DB2 z/OS. For example, that is the case with the case in the following picture where you have one DB2 AESE replicating with DB2 z/OS:
However, you may be able to extend this topology in such a way that DB2 AESE's no-cost Q Replication could be used for the extension. For example, let's say you want to add another DB2 AESE that replicates with your original DB2 AESE. You still have to purchase IIDR for the original systems (systems #1 and #2 in the picture below), but you can use DB2 AESE's no-cost Q Replication for the new system (system #3) because it only replicates with one other DB2 LUW.
Replicating Between DB2 AESE and DB2 ESE or Workgroup Edition
You can use DB2 AESE's no-cost Q Replication to replicate between a given DB2 AESE and any other edition of DB2 LUW. However, the following requirements must be met:
For example, say you have the following topology. In it DB2 AESE v10 is on system #2 and replicates with a DB2 ESE on system #1 and a DB2 Workgroup Edition on system #3. You can use the AESE no-cost Q Replication because it only replicates with two other DB2 LUW servers. IIDR must be purchased for systems #1 and #3.
Running Replication in a Peer-to-Peer Topology with Three DB2 AESE Servers
For me, this is the case that justifies upgrading your AESE from 9.7 to v10. For DB2 LUW 9.7, you must buy IIDR for DB2 AESE if your DB2 AESE replicates with more than one other DB2 LUW server:
With DB2 AESE v10, you can use the no-cost Q Replication fro all three DB2 servers:
Free free to post questions if you have other topologies you're interested in.
* InfoSphere Replication Server is now part of InfoSphere Data Replication and will no longer be marketed by IBM starting in March 2013. However, from a technical perspective, the Q Replication from either product can be used in these topologies.
DavidT 120000JC6D Tags:  oracle data_replication mq infosphere_warehouse db2 datastage q_replication event_publishing 1 Comment 5,993 Views
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:
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.)
DavidT 120000JC6D Tags:  infosphere zos sql_replication database dbms replication replication_server db2 z/os 1 Comment 11,244 Views
I've been surprised recently by the number of people who still have an old IBM product called DataPropagator, or 'DProp'. More suprising is that they think they're at a dead end with no replacement. Nothing could be further from the truth. DataPropagator was a product name. Product names, as I'll show later, can change. However, the underlying technology remains. In DataPropagator's case, the technology inside it is what IBM calls SQL Replication. SQL Replication is alive and well. You can find it in several IBM products.
For example, if you're on z/OS and currently running DB2 DataPropagator or DataPropagator Relational, the DProp function can now be found in the SQL Replication feature of WebSphere Replication Server v9.1 and InfoSphere Replication Server v10 as well as IBM's newer InfoSphere Data Replication product. This is important information because, if you move to DB2 z/OS v9 or v10, you must upgrade to one of these newer replication products because they contain the only level of DProp function that supports the newer DB2s.
Getting back to the subject of name changes, here is a graphic that shows the transformation of 'DProp' names on z/OS.
What If You're Current on S&S?
You may want to know what you're entitled to if you had purchased DataPropagator and are current on S&S. The easy thing to do is to search IBM announcement letters for 'datapropagator'... okay :) maybe it's easier said than done, so here's the ones that answer the question:
In a nutshell, they say that DataPropagator (v7 and v8) have been replaced by WebSphere Replication Server. v9. Of course, in 2010, v10 of Replication Server was released and its brand was changed to InfoSphere. Customers can move to Replication Server v10 if they are current on DataPropagator S&S.
The following lists add some comments about what else changed for each of these name changes:
If anyone knows of anything I missed, feel free to add it to the comments :)
DavidT 120000JC6D Tags:  licensing q_replication warehouse mq wmq mqseries what_do_i_need infosphere webspheremq db2 1 Comment 5,926 Views
This post is the answer to one of the FAQs found in
Yes, a limited-use copy of WebSphere MQ comes with all products that license use of Q Replication. "Limited-use" of MQ simply means that you can only use this copy of MQ with Q Replication. However, some products allow a few other things, too. For example, most DB2 LUW and InfoSphere Warehouse editions bundle MQ because DB2 LUW has a several integration points with MQ, not just Q Replication.
Do you have to use the bundled copy of MQ? No. If you already have a fully licensed copy of MQ, Q Replication will work with that. The one thing to remember is that a best practice is to have dedicated queue managers for Q Replication. In other words, create new ones, don't use queue managers that have other work going through them.
For UNIX and Windows, do you have to install the WebSphere MQ server software on the same system as where Q Replication is installed? No. The MQ server software can be installed on a different system and queue managers created there. Q Replication will access those queue mangers through MQ client software installed on the same system as Q Replication. However, for optimal performance, queue managers should be co-located on the same system(s) as Q Replication. For those of you who want an example of what's allowed, here's one :)
For z/OS, are there any special considerations? Yes, but only for how you order the bundled copy that comes with a z/OS product. See the last heading of this post for ordering information.
How do you verify everything I just said about licensing? :) Check the IBM license information documents. They are the official and final word about what you're entitled to. In fact, what they say overrides anything anyone says in a blog :) You can find IBM license information documents on ibm.com by using IBM's search interface:
Simply enter the product name and date, then follow the on-screen instructions. To read the MQ related terms, search for the string 'MQ'. Here are links to two license information documents that tend to be of interest to people:
You may want to use a date range to limit the results returned.
Ordering Considerations for z/OS
If you plan to use the copy of MQ that comes with InfoSphere Replication Server v10 for z/OS, please note that Replication Server is in Shopz's DB2 zone and the bundled WebSphere MQ is in Shopz's MVS zone. For example, Shopz will show something like the following in the DBS DB2 group/zone:
Shopz will show something like the following for MQ in the MVS group/zone:
DavidT 120000JC6D Tags:  replication_server purescale what_do_i_need licensing oracle warehouse change_data_capture cdc database sql_replication infosphere q_replication db2 replication 10,077 Views
IBM offers a lot of flexibility with its data replication technologies (CDC, Q Replication, and SQL Replication). As a result, we get a lot of questions about what's needed under various circumstances. This post is a collection of links to answers for the most common questions I get asked. Of course, as you read these, remember that IBM's license documents are the final word on what's allowed. If there's ever a conflict between them and what's posted here, the license document win :)
Note: After the links, I also have a little background about why flexibility drives these questions.
We don't have a lot, but I divided them by headings to make them easier to navigate visually.
What Do I Need...?
What Replaces Old Products?
If you have any suggestions for questions to add, feel free to use the comment section of this blog.
I regularly get some variation of the following three questions:
To determine or understand the answers, you may need a little background related to three areas of flexibility. First, you can get IBM's data replication technologies a number of ways:
Second, IBM data replication technologies allow a lot of differences between source and target systems:
Third, IBM data replication technolgies offer a number of ways they can be set up:
DavidT 120000JC6D Tags:  q_replication replication_server infosphere_warehouse what_do_i_need licensing homogeneous_replication_f... db2 3 Comments 10,932 Views
This post is the answer to one of the FAQs found in Licensing Tips for IBM Data Replication.
In October 2010, IBM announced a new edition of DB2 called DB2 Advanced Enterprise Server Edition (AESE). In the same letter, IBM announced that two-site Q Replication is now provided at no additional cost in DB2 AESE and all editions of InfoSphere Warehouse. While this started with 9.7, it is continued and enhanced in v10. This great news for customers who want to use data replication as the basis for a DB2 high availability (HA), disaster recovery (DR), or active-active solution. (If you're not familiar with Q Replication for availability, see the video on ChannelDB2).
So, what are you really entitled to? The license is the final word, but, in simple terms, the bundled Q Replication can only be used at no cost when a 9.7 DB2 LUW data server (an instance*) is replicating data with only one other DB2 LUW server**. Or, if your DB2 data server is v10 instead of 9.7, it can replicate with up to two other DB2 LUW servers**. That applies to the DB2 LUW in InfoSphere Warehouse and the InfoSphere Warehouse in either the Smart Analytics Systems or the PureData System for Operation Analytics. For example, you could use the free Q Replication in the following bidirectional replication configuration:
The question is - do you ever need to buy Q Replication now? If you're doing anything more than what's described in this post, then, yes, you'll need to buy Q Replication for DB2 AESE and InfoSphere Warehouse (ISW). The two most common replication configurations that require this are ones where you:
If you need to understand more about these examples, see the post titled When Do I Need to Buy Q Replication? If you determine you need to buy Q Replication, IBM recommends you buy IBM InfoSphere Data Replication (IIDR) instead of the older InfoSphere Replication Server product, which has no v10 equivalent and recently had an end-of-marketing announced for it.
Not Found in Passport Advantage?
People often look for a Q Replication product under DB2 ESE or InfoSphere Warehouse in Passport Advantage. For example, they look for the DB2 Homogeneous Replication Feature or InfoSphere Replication Server under the DB2 AESE eAssemblies. They won't find one because it's not needed. To be clear, you do not need anything from Passport Advantage to be able to run the Q Replication found in DB2 AESE and InfoSphere Warehouse. Q Replication should run out of the box in these products without a Q Replication install or a Q Replication license enablement (.lic) file. In other words, you should be able to run Q Capture and Q Apply without them giving you an error that a license is required.
* Multiple DB2 instances can be created from a single DB2 install. Each instance can use the bundled Q Replication to replicate up to the entitled number of other instances.
** The version and edition of the other DB2 LUW server(s) does not matter.
DavidT 120000JC6D Tags:  licensing data_replication replication_server db2 change_data_capture z/os what_do_i_need cdc infosphere_warehouse db2_connect sql_replication 6,495 Views
This post is the answer to one of the FAQs found in License Tips for IBM Data Replication.
The answer depends on your configuration.* Specifically, where you plan to run SQL Replication's Capture and Apply programs. I'm using this post to document the basic answers for future reference. I'll cover three scenarios:
There is one other configuration possible. I will not discuss it here because it is very rarely used:
For those of you who don't know what 'push' and 'pull' mean, these are simple concepts:
Each section has pictures that illustrate push and pull.
From DB2 z/OS to DB2 LUW, Pull Configuration
In this configuration, SQL Capture runs on z/OS and SQL Apply runs on UNIX or Windows. SQL Apply 'pulls' changed data from DB2 z/OS to DB2 LUW:
You need two things in addition to DB2 z/OS and DB2 LUW:
As an aside, you may be wondering why I show DDF in the picture here. It's because SQL Apply's workload should be zIIP eligible when it accesses a DB2 for z/OS via DRDA over a TCP/IP connection. DB2 should give z/OS the necessary information to have portions of these SQL requests directed to the zIIP. Always check the latest zIIP information from DB2 to determine what's possible.
From DB2 LUW to DB2 z/OS, Pull Configuration
This is the same configuration as the last except SQL Capture now runs on UNIX or Windows and SQL Apply runs on z/OS:
You need IBM InfoSphere Data Replication (IIDR) for DB2 for z/OS** so that you have SQL Appy on z/OS. However, you do not need DB2 Connect for this configuration. DB2 Connect is not needed when z/OS clients access a DB2 LUW server.
From DB2 LUW to DB2 z/OS, Push Configuration
In this configuration, both SQL Capture and SQL Apply run on UNIX or Windows:
You need the following in addition to DB2 z/OS and DB2 LUW:
People tend to run this when (1) they have a low volume of changed data, say only 100's of rows of per minute, and (2) they only want to install and manage replication on a single system. A push configuration is never recommended for high-volume environments because inserts, updates, and deletes are pushed across the network one at a time. They are not grouped or 'blocked' as query results are (i.e., with a pull, a query goes across the network and all eligible changed data is typically returned in blocks of rows via DRDA).
* All comments about DB2 LUW are also applicable to InfoSphere Warehouse since you get SQL Replication at no additional cost when you purchase them.
** This used to say you to need WebSphere or InfoSphere Replication Server on z/OS. It had features that included SQL Replication. You can still use these if you have them. However, Replication Server has been replaced by IIDR.
DavidT 120000JC6D Tags:  replication_server q_replication z/os db2 sql_replication 3,385 Views
People sometimes ask us what they should receive when they buy Replication Server for z/OS. We decided to order a copy of Version 9 and show what we got. We expected documents and/or software for three products:
The box contained documents, CDs, and one 3590 tape. These were shown as 10 items in the packing list. The following three headings show pictures of what we received for each product and describes each item.
DB2 Connect Personal Edition
10. A box with five CD sleeves containing several CDs for DB2 Connect Personal Edition 9.7
DB2 Connect Personal Edition (CPE) can be installed on Windows or Linux and contains Replication Server's user interfaces (the Replication Center and asnclp). You do not have to install this copy of CPE if you already have a DB2 Connect in your shop. The DB2 Connect, most DB2 LUW servers, and the DB2 Administrative Client also contains Replication Server's user interfaces. You can simply point the user interfaces to your DB2 z/OS through your existing DB2 Connect.
Any questions? Post 'em here :)