§Using ‘Standard’ replication achieves much higher throughput performance than using ‘Consolidation’ or ‘Summarization’
Standard replication can do optimizations such as arraying, commit grouping, etc that can not be performed when using the other replication methods
Note some optimizations will also be disabled if using Adaptive apply or Conflict Detection & Resolution
§Be aware when you are parking tables/subscriptions
An inactive (not currently replicating) subscription that contains tables with a replication method of Mirror will continue to accumulate change data in the staging store from the current point back to the point where mirroring was stopped. For this reason, you should delete subscriptions or remove tables that are no longer required, or change the replication method of all tables in the subscription to Refresh to prevent the accumulation of change data in the staging store on your source system.
The same is true with a parked (idle) table. You need to insure that the replication method is set to Refresh
Modified on by GlenSakuth
Number of CDC Subscriptions Required
A Subscription is a logical container that describes the replication configuration for tables from a source to a target datastore. Once the subscription is created, you create table mappings within the subscription for the group of tables you wish to replicate
An important part of planning an InfoSphere CDC implementation is to choose the appropriate number of subscriptions to meet your requirements
Rule of Thumb:
Starting with the minimum number of subscriptions and only increasing due to valid reasons, is the optimal approach
This will ensure efficient use of resources as well as require a lower level of maintenance
It may require an iterative process before you have a good balance
The number of subscriptions will impact the resource utilization of the server (more CPU and RAM are needed) and performance of InfoSphere CDC
Note that tables with referential integrity or ones where the data must be synchronized at all times must reside in the same subscription since different subscriptions may be at different points in the log
The following are valid reasons to increase the number of subscriptions:
Requirement to replicate one source table to multiple targets
You need to increase the number of applies once it has been determined that it is the apply that is affecting the performance and you want further parallelism
Management of replication for groups of tables, in cases where some tables only require mirroring with a scheduled end time, while others require continuous or they are active at different times of the day
You have too many tables in a single subscription which is affecting start-up performance
You have multiple independent business applications that you need to mirror, but want to be able to deal with maintenance independently
In 2011, IBM released three new data replication products:
One question that comes up is whether the two IMS replication products are compatible with either the new Data Replication product or the existing InfoSphere CDC products. The answer is yes - the IMS products are compatible with both new and existing products that contain the CDC technology. IMore specifically, they can provide IMS changed data to any data replication solution that you can build with IBM's CDC technology. For example, you can create unidirectional (one-way) subscriptions that feed IMS changed data to any database that can be targeted by CDC:
Two notes about this picture:
- IBM recommends you use the CDC technology in IIDR if you do not own InfoSphere CDC.
- The target DB2 can be DB2 for z/OS, DB2 LUW, or DB2 for System i.
You could also feed IMS changed data into other business software such as ETL, IBM's DataStage, and ESBs:
In other words, the new IMS data replication products extend the reach of IBM's CDC technology by adding IMS as a source for log-based capture of changed data. If you have technical questions, see the Classic CDC section of the Information Center
The following rules apply with respect to what Versions of Management Console (MC), Access Server, and CDC agents (engines) will inter-operate.
These rules apply to any CDC 6.x or higher release.
1) The MC and AS must be at the exact same release level
2) The CDC source and target agents (engines) can be at different release levels
3) The MC version must be >= the most recent CDC source or target agent (engine)
With the announcement of InfoSphere Data Replication 10.1.2
, IBM added a product called InfoSphere Data Replication for Database Migration. This new product is a tool to help you with hardware and database upgrades. It is intended for short-term use. For example, if you're upgrading to a totally new hardware platform, the new Data Replication product keeps two copies of your database in sync - one copy on your old hardware and the other on your new hardware. This gives you the time you need - a few weeks or several months - to migrate and test applications before you turn off the old hardware. A similar scenario is possible if you're just upgrading databases or if you're upgrading hardware and databases simultaneously.
The first release of the new product is available for three different combinations of source and target databases (three different from and to combinations):
- Oracle to Oracle
- Oracle to DB2 for Linux, UNIX, and Windows (LUW)
- DB2 LUW to DB2 LUW
It provides only the data replication function needed for database migration. Specifically:
- Unidirectional (one-way) replication
- If you need multi-way replication, you need to buy the full Data Replication product.
- One source and target database pair.
- In other words, a single copy of the product can only be replicating between two databases at any given time.
- However, after you finish migrating a given database, you can move the product to another system and migrate another database.
- Data transformations when the source is an Oracle database and the target is DB2 LUW.
- If you need transformations for other source and target combinations, you need to buy the full Data Replication product.
- Replication of add and remove table partitions when both source and target are Oracle databases.
- If you need other DDL replication, you need to buy the full Data Replication product.
Of course, like the full Data Replication product, the new product contains all IBM Data Replication technologies - CDC, Q Replication, and SQL Replication. However, for those of you familiar with the older products- InfoSphere CDC and InfoSphere Replication Server - there is no database migration edition of those two products.
Note that there are two licensing differences for this Data Replication product when compared to many other products. First, this one is licensed by target server install instead of a PVU (processor value unit) count. That means for each target install you license, you can install at a single source for no additional charge. Second, IBM does not offer a non-production version. Therefore, you buy the same product for both production and non-production uses. This isn't bad since this database migration product is significantly cheaper than the full Data Replication product. To verify these licensing points and others, always see the the license file on ibm.com
as the official word in how licensing works.
If you're looking for an excellent way to replicate changed data from a wide range of databases into a Netezza appliance, you can do so through InfoSphere Data Replication
. The latest release provides an Apply program that is both native to Netezza and optimized for Netezza targets. This Apply is built from Data Replication's CDC technology and is also compatible with the CDC technology found in InfoSphere Change Data Capture and InfoSphere Classic Change Data Capture for z/OS
. This means you can replicate data to Netezza from source databases ranging from Oracle, DB2, and others on UNIX or Windows to DB2* and IMS on the mainframe. Ordering information can be found in the Data Replication announcement letter on ibm.com
* Data Replication's CDC Apply program cannot be used to feed changed data to the IBM DB2 Analytics Accelerator (IDAA).
I've added three new videos to my channel. They walk through configuring, operating and monitoring data replication using the CDC Management Console. This is basically the same thing you'd get if you came by the InfoSphere demo room at Information On Demand (now Insight) and agreed to let me show you a quick demo of CDC.
Here's the link to my channel "James talks about Data Replication":
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!
Now that IBM has packaged its major data replication technologies into a single product, InfoSphere Data Replication
, a lot of people are asking what they can take advantage of that they couldn't with the older products (InfoSphere CDC and InfoSphere Replication Server). Other than the obvious point of having access to multiple technologies, you can now use IBM's table compare utility, asntdiff
, with CDC. asntdiff is a general-purpose utility that compares the data from two queries. IBM provides it through several product - Replication Server, the IBM Data Server Client, and all editions of DB2 and InfoSphere Warehouse.*
Long-time CDC users may ask what's happening to CDC's differential refresh and why they would want to use asntdiff instead of differential refresh. First understand that differential refresh is alive and well and it's not going anywhere :) asntdiff is just an option available to you.
To understand when you might want to use asntdiff, understand the basics of how it works.
- asntdiff accepts two queries as input and compares the result sets.
- You can use almost any query you can write against source and target tables.
So, the first reason to consider asntdiff is times when differential refresh's restrictions could be overcome by writing queries to get the result sets you need. For example, asntdiff may be an alternative if one of the following differential refresh restrictions applies to your replication configuration:
- Differential refresh is only available for tables that use Standard replication.
- Derived columns in the source table are not supported.
- Target columns are ignored if they are mapped to derived expressions, constants, or journal control fields.
- Key columns of the target table must be mapped directly to columns in the source table.
Next, asntdiff is independent of data replication and can be started from a command line. Among other things, this means:
- It can made part of a z/OS batch job and scheduled.
- It can be used while a CDC subscription is running
One major point to be aware of with asntdiff is how it works with heterogeneous data. For example, when you want to compare data being replicated from Oracle to DB2. asntdiff was originally written for DB2 databases. As a result, it requires IBM data federation technology to query databases such as Oracle. The good news is that InfoSphere Data Replication provides data federation for use with data replication configurations.
If you're not familiar with asntdiff and want to give it a try, see the ChannelDB2.com blog post titled Compare the Rows of Two Tables
. If you have questions, feel free to post them in the CDC message board here on developerWorks.
* Yes, technically, you could already use asntdiff with CDC on UNIX or Window since it comes in so many IBM products on UNIX and Windows. However, if you wanted to use it on z/OS, you could only get it through Replication Server. It's now in InfoSphere Data Replication as well.
Modified on by MikeJory
IIDR 188.8.131.52 has been released with new functionality in addition to resolving some APARs.
IIDR 184.108.40.206-4 can be found on Fix Central.
* No IIDR downtime support for Microsoft SQL Server non-structural online index rebuild operations
Index and table reorganizations done to improve query performance no longer impact replication. Replication will continue with no outage and with no user actions required.
* Log based change capture for Oracle is now supported on Microsoft Windows
* No IIDR downtime support for Oracle Automatic Storage Management rebalancing activities
Previously in some situations rebalancing of ASM could could require replication be restarted
* An unplanned failure of Oracle to a DataGuard standby database no longer requires a resynchronization
Previously if an Oracle database being used as a source of changes failed over to an Oracle DataGuard standby, you would have been required to refresh the target data before restarting replication
* IIDR can now capture changes directly from ASM on Exadata
Previously replication required the ASM logs to be multiplexed to a non-ASM destination which typically could only be accomodated off the Exadata appliance
* IIDR can now target Cloudant
* IIDR can now use webHDFS to deliver change data to HDFS
This allows IIDR to be installed remotely from the cluster and enables targeting HDFS deployments that are secured via Kerberos
JR53019 POTENTIAL FOR DUPLICATE TRANSACTION REPLAY ON THE TARGET DUE TO INCORRECT BOOKMARKING ON IMMEDIATELY PREVIOUS SHUTDOWN.
JR53294 CDC TRIES TO READ AN UNCOMPLETED ORACLE LOG AND GETS ERROR IN LOG SHIPPING USING DATAGUARD SCENARIO.
JR53370 CDC DATA LOSS / CONSISTENCY UPOEN CDC RESTART AFTER ABORT
JR52825 CDC FOR DATASTAGE IN DIRECTCONNECT MODE NOT HONOURING THE DELAY PARAMETER FROM BATCH THRESHOLD SUBS PROPERTY
JR52973 DMSHUTDOWN COMMAND WITH -A OPTION DOESN'T TERMINATE SUBSCRIPTIONS
JR52797 ADDRESSING AN IDENTIFIED BUFFER OVERRUN CONDITION IN THE ORACLE SOLARIS JVM
JR53411 SETTING BLOB/CLOB TRUNCATION IN DATASTAGE PROPERTIES FOR A SUBSCRIPTION, REVERSES THE LIMITS
JR52923 TIMESTAMP IS NOT PROPERLY REPLICATING
JR53413 HAVING ROW SIZES GREATER THAN 341333 BYTES THROWS JAVA.NIO.BUFFEROVERFLOWEXCEPTION
JR52946 INSTANCE USING UPPER CASE ORACLE SID FOR STANDBY DATABASE CANNOT BE CREATED
JR53340 IIDR FOR ORACLE 12C FAILS TO START REPLICATION WHEN AN INSCOPE TABLE WAS ALTERED
Modified on by GlenSakuth
I have had many requests to share best practices when using IBM InfoSphere Change Data Capture (from this point forward in the blog referred to as CDC). I will try to add new tips and techniques on a regular basis.
Along with many of the best practices posts, I will include items denoted by "Rule of Thumb". These are general guidelines that will help in your planning. I will endeavor to provide reasons or context for the guidance. The Rules of Thumb should not be treated as hard limits, rather as useful guidance. If your needs fall significantly outside the guidance, it certainly does not mean that it can not be done. Rather, it would be best to engage with an InfoSphere CDC subject matter expert, and potentially you may want to consider IBM Services for assistance.
Steady State Operations
Modified on by DavidT
In April 2013, IBM announced Version 10.5 of DB2 for Linux, UNIX, and Windows. The same letter announced that DB2 AESE and DB2 AWSE would provide limited use of IBM InfoSphere Data Replication's (IIDR's) Change Data Capture (CDC) technology at no additional cost. However, the "limited use" statement sometimes leaves people with a question or two. The goal of this post is to answer those questions.
First, what CDC function are you entitled to use in the DB2 Advanced Editions? The license is the always final word, but, in simple terms, you can only use the bundled CDC to build disaster recovery solutions where a primary DB2 instance* has up-to-two backup instances. For example, the following replication topology is allowed by the DB2 Advanced Edition licenses:
Furthermore, the disaster recovery use case limits your entitled use of CDC function in the following ways:
You can only use unidirectional (one-way) replication.
You can set up replication from the primary DB2 to the backup(s) but you cannot set up replication from the backup(s) to the primary. This fits with the definition of a pure disaster recovery solution since it provides for fail-over but not switchback. If you need CDC for both fail-over and switchback, you need to license the full IIDR product.
You cannot transform the data as it's replicated. Again, this fits with the definition of disaster recovery and you can license the full IIDR to be entitled to transformations as you replicate.
The question is - when do you need to buy CDC now? If you want to do anything more than what's described in this post, you'll need to buy IIDR for your DB2 Advanced Editions. The two most common replication configurations that require this are ones where you do either of the following:
Replicate between DB2 LUW and either DB2 z/OS or Oracle.
Set up an HA or Active-Active solution with IIDR's CDC technology.
If you need to understand more about these examples, we'll have pictures and add a few more examples in a future post that talks about when you need to buy CDC.
Of course, the last question is - can I still build DB2 DR, HA, and Active-Active solutions using the Q Replication built into the DB2 Advanced Editions? Yes, absolutely. The addition of CDC to DB2 does not change this.
* Multiple DB2 instances can be created from a single DB2 install. Each instance can use the bundled CDC to replicate up to the entitled number of backup instances.
For those who have been using the CDC Developer Works Community, you will notice a change to the look & feel of the entry page. This new menu driven mechanism will provide extra flexibility as significant new content gets added to this community. I have tested it out on various browsers, so if you experience an issue, please send me an email at email@example.com and I will investigate.
Note I will also be cleaning up some old blog entries where questions were asked. Questions should continue to be asked on the forum versus the blog.
Hope you continue to get benefit from this community.
The new IIDR 11.3.3 Release is available today!
Here is a link to a page that describes the new functionality available: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W8d78486eafb9_4a06_a482_7e7962f5ac59/page/IIDR%20CDC%20New%20Features%20By%20Release?section=IIDR%2011.3.3
There are a large number of usability enhancements, new platform/DB support such as SQL Server 2014, along with technology previews/beta available for many new key technologies such as a Cloudant apply, WebHDFS support for Hadoop, and many others.
In response to: Best Practice - Target Considerations
Regarding Oracle triggers on the target tables, would these fire
during a standard refresh? We want them to fire off during normal
continuous replication from DB2 to Oracle..
Most are triggers on insert so I am concerned during the refresh
which we do whenever we change the table structure to match the