This new feature in DB2 V9 might sound like an old monster movie (Invasion of the Clone Tables!!!), but it is actually a nifty new capability for managing DB2 data availability. Cloning is basically a method of entirely refreshing all of the rows of a table while maintaining availability to the table.
OK, so how does it work? Basically, you will create a table with the exact same attributes as a table that already exists at the current server, except that it is empty of data. It is created using the ALTER TABLE SQL statement with the ADD CLONE parameter. This clone table is created in the same table space as the existing table. After creating the clone table you can do whatever you want to do with it. LOAD it, INSERT rows to it, etc.
The clone table will be structurally identical to the base table in every way: same number of columns, column names, data types, as well as have the same indexes, before triggers, LOB objects and so on. All of this is done auto-magically by DB2 when it the clone table is created. Authorizations and views on the base table, however, are not duplicated, or cloned.
So, the clone is created and it can be manipulated without impacting the base table. When the clone is ready to become the base table it is exchanged with the base table. This is accomplished using the new EXCHANGE SQL statement. After running an EXCHANGE the clone becomes the real table and the previously “real” table becomes the clone - - and you can repeat the process.
The clone and the base table are kept in different underlying VSAM linear data sets. We all should know that the data sets used by DB2 are named using the following format:
cat.DSNDBD.DBNAME.TSNAME.I0001.A001. Well, the clone uses the same name except for the next to last component. It will be I0002. When the exchange is made, DB2 flips the I0001 with the I0002.
Of course, there are rules and details you will need to know to properly deploy cloning in your organizations, but that is all documented in the manuals. The point of these blog entries is more to make you aware of new functionality and give you the basic flavor for how it works and what it is intended for…
With that in mind, this is intended to allow you to quickly replace one version of DB2 table data with another version, without impacting availability while the new version is built.
News, views, and items of interest on IBM's Db2 database management system and mainframes.
You have mentioned that Clones can be created on LOB objects too. Can you please let me know how this is accomplished. I did try to clone a LOB table and it fails with SQL Error -148 (THE SOURCE TABLE OR TABLESPACE source-name CANNOT BE ALTERED)
ReplyDeleteIn your article you mentioned we made a Clone table. But what is the benefits? Why we make clone table? We can do our all work in base table also.What is the difference between base table and clone table?
ReplyDeletedigital signature
Clone tables are specifically for situations where the data in a table is periodically completely refreshed. With a clone table, the current table can continue to remain online and available while the clone table is populated with the new refreshed data. When the clone is ready, a quick switch swaps the clone with the current with minimal lack of availability. If you do not have a need like this, then clones are not useful for you.
ReplyDelete