Article by Steen Rasmussen
In order to clone a Db2 table, some companies may still be using the good old UNLOAD-LOAD approach or the more efficient method of using image copies from the source Db2 object and OBIDXLAT processing to "recover" the data to the target Db2 object. If you are using either of these methods, you should consider using an even better mechanism that leverages the RC/Merger feature of CA RC/Migrator for Db2 for z/OS.
Watch this 15-minute video to understand how to use CA RC/Migrator (RC/Merger) to do instant Db2 table cloning:
RC/Merger Data Availability and Consistency - Options to consider
There is an increasing interest in RC/Merger and during the past year we have received several questions from customers asking about data availability, data consistency/integrity etc. In the remainder of this article, we will cover the details to eliminate any confusion that might exist around our Broadcom Db2 Data Cloning Tool, otherwise known as RC/M Strategy RC/Merger Move Analysis.
When it comes to the "outages", we don't need to worry about the target (the Db2 subsystem getting the cloned data) since the target objects have to be stopped during the process anyway because RC/Merger is overlaying the data at the page level, which provides the speed advantage as opposed to the row level like UNLOAD-LOAD). So for obvious reasons you can't have concurrent access to the data.
As mentioned, we only need to think about data availability for the source objects, and RC/Merger has four different options to control the Share Level:
Let's go over each of these and explain the differences and issues to consider:
SHRLEVEL NONE
RC/Merger will require exclusive access to the source, but it is up to the user that is cloning the data to make sure this is the case (by doing STA DB(xxx) ACCESS RO or STO DB(xxx)). Otherwise inconsistencies might occur if applications are updating the data. This can be used to get "fuzzy" data so it is recommended to REBUILD INDEX(ALL) and NOT clone index data. Obviously, this is pretty intrusive and very few customers have this luxury unless they are using RC/Merger to consolidate Db2 subsystems into a data sharing group.
SHRLEVEL NS
RC/Merger will make sure the data is available in EXCLUSIVE mode - meaning a STOP will be executed. Again, similar to the previous option this isn't really very useful in most scenarios.
The great thing about this option is that both data and indexes can be cloned and the data is consistent so no need to execute the expensive REBUILD INDEX(ALL).
SHRLEVEL REFERENCE
RC/Merger will issue STA ACCESS(RO) to ensure data consistency and everything mentioned in the previous option applies here.
The previously described options really only come into play when data is cloned from the source VSAM datasets and the customer doesn't have access to using the SNAPSHOT option or the FLASHCOPY option. So let's look at the option that is used by almost every customer whether using Snapshot/Flashcopy or not - as almost no one has the luxury of being able to provide an outage for the production data. One exception could be if you have a "Golden Copy" where the data is cloned from.
SHRLEVEL RN
Also known as (REFERENCE,NOCHECK) where RC/Merger doesn't attempt to enforce access to the source objects. The user has the exclusive responsibility. Of course, this option requires the user to think about the impact if cloning data from the Db2 VSAM pagesets and not using Snapshot/Flashcopy, so let's cover both aspects:
Cloning From the Live Db2 VSAM Pagesets:
Some customers are using this option to do a global STA DB ACCESS(RO) to ensure data is consistent and then they can clone both data and indexes. If you want the applications to have concurrent update access, the data cloned will be "fuzzy" so the recommendation is to ONLY clone the tablespace data and then have RC/Merger generate REBUILD INDEX(ALL) to avoid 00C90101 (inconsistencies between data and index). This has been used a lot in the past and is still used to some degree by customers not having access to snapshots/flashcopy.
Nowadays, more and more customers do have access to generating Snapshots or Flashcopies. The SNAPSHOT feature is already a few years old and allows RC/Merger to generate snapshots of the data as an integral process - then cloning the snapshot'd data and deleting the snapshot datasets upon completion. The user has to specify the HLQs etc. The only downside is there's a small outage during the snapshot process. It is worth mentioning that even before RC/Merger provided this feature, users could take advantage of this method by doing the snapshot outside of RC/Merger at a convenient time - but then it's necessary to manually modify the generated BP-script (doing a CHANGE ALL) pointing to the snapshot datasets instead of the live Db2 VSAM pagesets. This works well but it does require some manual intervention.
Real-life experience from a customer
A large health-care corporation tested IBM COPY SHRLEVEL CHANGE CONSISTENT YES FLASHCOPY YES (a new feature delivered in Db2 Tools r20.0, about 18 months ago) and got pretty excited as the COPY process image copied both tablespace partitions and indexes including the huge NPIs - backing out all INFLIGHT URIDs - making the Image Copies 100% consistent. This COPY process creates special entries in SYSCOPY (, so RC/Merger was enhanced to pick up the latest COPY from SYSCOPY - not touching the live Db2 VSAM pagesets at all, no need to rebuild indexes saving a huge amount of CPU and elapsed time.
Keep in mind that RC/Merger takes care of all the VERSION-ID differences, resetting the RBA's etc. No need to worry about that when using the RC/Merger feature of CA RC/Migrator for Db2 for z/OS.
For additional info, contact us: db2-experts@broadcom.com