Modified on by Isayas Sium
Q Replication Tips for Beginners
If you are new to Q Replication, you may be interested in some tips on making sure your first configuration is successful. This blog is for you. It is a list of tips and includes links to help you successfully configure, deploy and monitor a typical Q Replication scenario.
This blog assumes you've read the post titled A Fast Way to Get Started with Q Replication for DB2 Active-Active Database. For example, this post assumes that you are running replication between two systems as shown in the picture below. However the tips here apply to any Q Replication configuration:
The tips fall into four basic categories:
Use the InfoSphere Data Replication Dashboard!
Validate your WebSphere MQ ('MQ') setup
Validate your DB2 setup (for users new to DB2)
Reset your Q Replication Environment!
Expanding our Picture...
In system 1 and system 2, the DB2 instances, Q Capture/Q Apply pairs and MQ managers communicate with each other as shown in the diagram below:
The DB2 database names, MQ manager names and MQ port numbers in the diagram above are also referenced in the tips sections below (Tips #2 and #3). Q Replication tutorial can be found here
Tip #1: Use the InfoSphere Data Replication Dashboard!
Follow the InfoSphere Data Replication Dashboard Install Procedures to install the InfoSphere Data Replication Dashboard on one of your systems. Installation is quite fast and simple, and footprint (including embedded web server) is small.
Just open a web browser window pointing to the URL defined as part of the installation steps and the Q Replication Dashboard will give you:
easy access to a consolidated view of all your Q Replication configurations
for each configuration, live monitoring graphs display metrics like log latency, Q Capture throughput, etc.
for each configuration, at-a-glance status: drill-down from Q Capture or Q Apply to Q subscription status and access DB2, MQ or Q Replication on-line Information Center topics as needed
Tip #2: Validate your MQ Setup!
The post named A Fast Way to Get Started with WebSphere MQ shows you how to use the ASNCLP CREATE MQ SCRIPT command to create MQ definitions for your active-active configuration. The command returns two MQ scripts, one script for system 1 and one script for system 2. Each script creates an MQ queue manager and its related MQ definitions. This section includes some tips on how to use the create mq script command. It also lists a few tools you will want to use to validate your MQ setup, should you encounter some problems.
Guidelines on how to use the ASNCLP 'CREATE MQ SCRIPT' command:
Do not use 'localhost' value as the mqhost parameter. Use the actual ip address or host name (site1.xx.com for mqserver 1 and site2.yy.com for mqserver 2). 'local' is a meaningless concept in bidirectional environments and creates problems.
If your test environment has a small file system, review the parameters associated with the crtmqm command in each MQ script output file. The '-lp' and '-lf' parameter values may be too large as the MQ log file sizes generated by ASNCLP assume a production environment. If necessary, update the values in each MQ script file before running the script.
Do not update the output MQ script before running it other than the crtmqm command parameters above. For example, each MQ transmit queue definition in a given MQ script output points to the other system's MQ listener port number and should not be changed.
In some cases, you may be asked to reuse an existing MQ environment: an MQ queue manager and possibly MQ administration and restart queues for a Q Capture server. If that is the case, you won't be able to use the ASNCLP script commands as provided and will need to make some updates. This may be error-prone so this section lists various tools and tips to validate and correct your MQ setup for your active-active configuration.
Tools you will want to use to validate (and correct) an MQ configuration for Q Replication:
WebSphere MQ Explorer
- at-a-glance list of all your MQ managers
- MQ queue manager status, channels, queues definition, listener port information etc.
ASNCLP 'Validate MQ Queues' command
- validates that your MQ definitions are correctly set up for a bidirectional scenario
- is described in the post A Fast Way to Get Started with Q Replication for DB2 Active-Active Database
- catches setup error that occurs with the administration queue; for example, the
DB2 ASN.IBMQREP_RECVQUEUES.ADMINQ column value on system 1 (or system 2)
should be the same as the local MQ administration queue name defined on
system 2 (or system 1); it should never match the local administration queue
name in the DB2 ASN.IBMQREP_CAPPARMS.ADMINQ column value on
system 1 (or system 2). The ASNCLP validate function alerts you if that is the case
- validates that the two MQ managers defined for a Q Replication configuration can talk to each other by sending a test message between the two MQ queue managers
- note that the restriction stating that the Q Capture and Q Apply programs have to be stopped before doing the test has been lifted
- for more information on how to use the Replication Center to verify your MQ setup for Q Replication, watch the short video (seven minutes) that is available on ChannelDB2. If you use DB2 V10 and above, the Replication Center is available as a stand-alone DB2 center.
Tip #3: Validate your DB2 Setup!
If you are new to DB2 client/server configuration setup, review the guidelines below:
Update the DB2COMM environment variable on each system if it is not set to 'TCPIP'
Configure DB2's TCPIP communications on system 1 so that ASNCLP or any SQL command issued from a DB2 command window can access the SITE2 database on system 2.
Use a hostname instead of a dynamic IP address when you catalog the tcpip node for the remote DB2 (if you don't use static IP addresses) to prevent loss of connection over time. For example, on system 1, catalog the node for system 2 using site2.yy.com and port 50002.
ASNCLP runs on one system (system 1 in the active-active blog) and creates all the Q Replication definitions from that system. If you decide to run ASNCLP on system 2, you will need to catalog a tcpip node to access the SITE1 database from system 2.
In active-active configurations, the same table definitions must exist on each active site. The active-active blog uses the db2sampl command to create the same tables in SITE1 and SITE2. To ensure that all tables have the same schema, use the same userid to login to both systems.
Tip #4: Know How to Reset your Q Replication Environment!
If tables in the SITE2 database become out of synch with tables in the SITE1 database, you can reset your Q Replication environment by first stopping and then starting the corresponding Q subscriptions with the necessary load methods to populate the target tables:
If you need to reset your Q Replication environment for other reasons (some subscriptions in inconsistent state, messages were sent to a dead-letter queue or hardware failure could be some examples), use the Data Replication Dashboard's Recovery Advisor tab. It helps you troubleshoot and analyze your environment including four recovery scenarios. The remainder of this section highlights its key features.
Note that the information available in the Recovery Advisor is also available in the Troubleshooting section of the Q Replication Information Center.
The following features are available for you to reset your Q Replication environment:
A 'Help me troubleshoot' wizard (Start Analysis button) and Four Recovery Scenarios (pull-down menu options):
The option to save and exit of a given recovery scenario and complete some of the remaining steps later (click on the green button 'Recovery Steps and Scripts' pull-down menu option 'Save and Exit' on the upper right hand side corner - see image above)
The option to restart or delete a saved (recovery scenario) procedure (the table at the bottom of the Recovery Advisor main page lists all saved procedures - if the table is empty, as it is below, there is no procedure work to complete later):
Modified on 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
In the past, IBM always announced a new release of its Replication Server product on UNIX and Windows whenever it announced a new release of DB2 LUW. If the version changed, IBM also updated the version of Replication Server on z/OS. Neither of those changes happened when DB2 LUW v10 was released. What's more, IBM announced in late 2012
that its older data replication products, including Replication Server, would no longer being marketed. So, why's IBM doing this? And what happens to Replication Server's Q and SQL Replication?
Well, over the years, IBM provided its data replication technologies
through a lot of different products. For example, IBM had two major data replication products at the same time for several years - InfoSphere CDC and InfoSphere Replication Server. That was a little confusing,
even to some IBM people. To simplify sales, IBM consolidated all it's replication technologies into a single product called IBM InfoSphere Data Replication
(IIDR). This means that IBM didn't need to continue offering those technologies through the older products (InfoSphere CDC and InfoSphere Replication Server).
- Are the older products still being supported?
- Is IBM still providing the IIDR's Q and SQL Replication at no cost in certain DB2 LUW and InfoSphere Warehouse (ISW)?
- How do you upgrade from your old Replication Server on UNIX or Windows to IIDR?
- How do you upgrade your Replication Server on z/OS to IIDR?
If you have any questions, feel free to post them in the comments section of this blog.
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:
- IBM Withdrawal Announcement 908-016
- IBM Software Announcement 206-224
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:
This product replicated between DB2 databases.
A companion product, DataPropagator NonRelational, replicated data from IMS to DB2.
A little later, IBM's data federation product, DataJoiner, provided the basis for heterogeneous SQL Replication between DB2 and other relational databases.
This was the start of a new, improved implementation of SQL Replication.
The NonRelational product was renamed to IMS DataPropagator.
If anyone knows of anything I missed, feel free to add it to the comments :)
SQL Replication is an easy-to-learn data replication technology that's found in several IBM products. It satisfies many data replication needs and is especially good for the following:
However, there are times when IBM's Q Replication is the better choice. Primarily, Q Replication should be used when you need one or more of the following:
In other words, Q is the way to go for availability and high performance solutions.
Other posts in this thread:
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 have just published a blog entry on the z/OS Platform Evaluation Test (zPET) team's blog regarding the use of IBMQREP_IGNTRAN and insert_bidi_signal=n. For anyone interested, you can find it here: zPET Blog Entry - IGNTRAN
I was having issue when trying to connect to my database (DB2 v10.1.0.3", "s130918", "IP23509", and Fix Pack "3") having authentication as DATA_ENCRYPT using IIDR replication dashboard.
Has anyone come across this issue , is there any work around for this.
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
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?
My shop has IMS DPROP for propagation of IMS data to DB2 z/OS. As it is no longer being marketed and EOS unclear, I am looking at the recommended IBM replacement: Infosphere; specifically Infosphere IMS replication for z/OS and Infosphere Data Replication for DB2 z/OS. Is there any information on the relative performance of Ithese nfosphere Data Replication products versus IMS DPROP ?
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?
Modified on 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!
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.
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 .
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
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
- 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).
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:
- If you are new to Q Replication, you have two choices:
- Upgrade to DB2 Advanced ESE (AESE) or ISW. This the best alternative if all of your replication is between
DB2 LUW and/or ISW servers and your configuration meets requirements. For more information, see the post titled Is Q Replication Free?
- Purchase IIDR*. You will need IIDR if your DB2 LUW replicates with DB2 z/OS, a heterogeneous source or target (e.g., Oracle), or with more sites than allowed by DB2 AESE or ISW.
- If you are an existing HRF customer, you can still buy support ("S&S") for it and activate in DB2 LUW v10. You also have the option of trading up to the full IIDR product. You can do this with a special trade-up feature (part number D0P64LL) that was added to IIDR in its 10.1.3 announcement.
* 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.
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.
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.
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.
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:
- Replicating between DB2 AESE and DB2 z/OS
- Replicating between DB2 AESE and DB2 ESE or Workgroup Edition
- Running replication in a peer-to-peer topology with three DB2 AESE servers
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:
- The no-cost Q Replication in DB2 AESE can only be used to replicate with up to two other DB2 LUW servers if DB2 AESE is v10 or one other DB2 LUW server is the DB2 AESE is 9.7.
- IIDR must be purchased for DB2 editions other than AESE.
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.
Modified on by DellBurner
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:
Note: The products in this article have been superceded in some cases and the license terms of the replacement products might be different.
- 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.)
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:
Software License Agreements Search
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:
InfoSphere Replication Server 9.7
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:
Modified on by DavidT
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 Get for Free?
How Does Upgrading Work?
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:
Which products contain IBM replication technologies?
What happens if my source and target have different levels of software?
What do I need to get for my particular configuration?
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:
In a stand-alone product.
As a feature of selected products.
Included at no additional charge in selected products.
Second, IBM data replication technologies allow a lot of differences between source and target systems:
The source's operating system can be different from the target's (e.g., AIX vs Windows).
The source database can be different from the target database (e.g., DB2 z/OS vs InfoSphere Warehouse).
The source's software can be at a different version and release than that of the target. This includes:
The replication software
Third, IBM data replication technolgies offer a number of ways they can be set up:
Capture can often be run remote from the source when the source and capture run on Linux, UNIX, or Windows.
Apply can run remote from the target.
A single install can access many sources and targets.
The links in this post help you navigate the options.
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:
- Replicate between DB2 LUW or ISW and either DB2 z/OS or Oracle
- Have a primary server more secondary or standby servers for HA or DR than Q Replication is licensed for.
- In other words, you have a 9.7 primary that replicates to more than one secondary/standby or a v10 primary that replicates to more than two, which are common with time-delayed standbys.
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.
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:
- From DB2 z/OS to DB2 LUW - Capture on z/OS, Apply on LUW (a 'pull' configuration)
- From DB2 LUW to DB2 z/OS - Capture on LUW, Apply on z/OS (a 'pull' configuration)
- From DB2 LUW to DB2 z/OS - Capture and Apply on LUW (a 'push' configuration)
There is one other configuration possible. I will not discuss it here because it is very rarely used:
- From DB2 z/OS to DB2 LUW - Capture and Apply on z/OS (a 'push' configuration)
For those of you who don't know what 'push' and 'pull' mean, these are simple concepts:
- Push - Apply runs on the source system and pushes changes across the network to the target database.
- Pull - Apply runs on the target system and pulls changes across the network from the source system.
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:
- IBM's InfoSphere Data Replication** (IIDR) for DB2 for z/OS
Either (a) IIDR** for UNIX and Windows or (b) DB2 Connect
- This contains the SQL Capture program.
- SQL Apply needs client-server connectivity to DB2 z/OS. This is provided by DB2 Connect.
- If you have an installation of DB2 Connect, you can use it. Otherwise, you can use IIDR for UNIX and Windows because it includes DB2 Connect for data replication purposes.
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:
- Either (a) IIDR** for UNIX and Windows or (b) DB2 Connect
- SQL Apply needs client-server connectivity to DB2 z/OS. This is provided by DB2 Connect.
- If you have a installation of DB2 Connect, you can use it. Otherwise, you can use IIDR for UNIX and Windows because it includes DB2 Connect for data replication purposes.
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).
** 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
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:
- InfoSphere Replication Server for z/OS
- WebSphere MQ for z/OS
- DB2 Connect Personal Edition
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.
- The Program Directory for Replication Server.
- The packing list for everything in the box.
- A license information document for Replication Server (text in multiple languages).
- A 3590 tape with all Replication Server software on it. It was wrapped with a document telling how to unload the tape.
5. The program directory for WebSphere MQ.
6. The license program specification for MQ.
7. A CD with a Windows install of MQ documentation.
8. A CD with a Linux install of MQ documentation.
9. A CD with an install of WebSphere MQ V6 for z/OS
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 :)
This post is the answer to one of the FAQs found in License Tips for IBM Data Replication
With the move towards integrated software stacks, you may not be aware of all the software technologies you have in the products you own. IBM InfoSphere Data Replication's Q and SQL Replication are two that people often ask about. They're found in several IBM products and people don't always know they have them. To cut down on confusion, I put together a quick reference of sorts. However, please note that this table is only about which products install the technologies. It is not a statement about what the technologies support. They support more products than those listed here. For example, Q and SQL Replication are used extensively with DB2 z/OS and Information Server's DataStage even though they are not installed by those products.
* SQL Replication is provided in all editions of DB2 LUW and InfoSphere Warehouse at no extra
cost, except DB2 Express-C (the free DB2). For DB2 Express-C, you must purchase support before you have
access to SQL Replication.
** While Q Replication is installed as part of all these products, a license must be acquired before you can use it. How? You can be licensed for Q Replication through IIDR, DB2 Advanced ESE/InfoSphere Warehouse, or InfoSphere Replication Server 9.7. All also include a copy of WebSphere MQ at no additional cost.
*** Only IIDR and Replication Server contains Q and SQL Replication's heterogeneous function (support for Oracle, Sybase ASE, etc.).
In the DB2 LUW Version 8 time frame, IBM had a product portfolio called Information Integrator (II) with several Editions. Three major technologies in these Editions were:
- Event Publishing
You got all three in almost every Edition. However, in the DB2 LUW Version 9 time frame, II was discontinued and these technologies were put into separately-purchasable products:
- WebSphere* Data Event Publisher
- WebSphere* Replication Server
- WebSphere* Federation Server
These changes led to confusion about what you should get if you paid for II's subscription and support (S&S) and wanted replacement products. What you get depends on which operating system platform you ran on:
- z/OS (mainframe)
- LUW (Linux, UNIX, Windows)
The next two sections cover each.
II had five products on z/OS according to the IBM DB2 Tools announcement letter 204-201
- Event Publisher
- Classic Event Publisher
- Classic Replication
- Classic Federation
The replacements were announced in separate letters. For example, II Replication and Event Publisher replacements are in IBM announcement letter 206-224
. The most asked about is II Replication which was replaced by WebSphere Replication Server's feature that contains both Q and SQL Replication
. That's what II Replication's S&S would get you.
II had about six or seven editions on LUW (Advanced, Standard, Replication, Event Publisher, Developer, etc). Most contained all three of event publishing, replication, and federation. According to the annoucement letter (link below), current II cusotmers were entitled to the replacement products called Data Event Publisher, Replication Server, and Federation Server. However, you wouldn't necessarily need all of those. For example, if all you were doing was replicating from DB2 z/OS to an Oracle or Microsoft SQL Server database, you would only need Replication Server since it contains all the federation technology needed for replication purposes. For constrast, note that the Federation Server license does not allow replication through a Federation Server.
* To make things even more fun, these products were rebranded as InfoSphere products in 2009. For example, WebSphere Replication Server is now InfoSphere Replication Server :)