This month I’m reviewing CLONE Table usage. It is one of several, what I call „esoteric“, Db2 abilities/functions that I will be running through over the coming months, plus some blogs that are either badly misunderstood, not used by anyone, or just very odd.
Attack of the Clones
Clones arrived in a blaze of glory way back in DB2 9 (remember that capital B?) and then promptly disappeared. I have had nothing to do with them – ever – and I only received one question about their usage. Until now…
What Changed?
Well, what happened, is that I was asked if our RealTime DBAExpert utility generating software worked with CLONE tables, and I had to do some quick checking in my head about *what* these things were!
How Do They Work?
So what is a CLONE Table? It is basically a duplicate table that lives in the „same“ tablespace but with a different INSTANCE. This is the first place where people make mistakes. You read a lot about renaming the VSAM LDS. That *never* happens with CLONEs. The „trick“ that IBM uses is the INSTANCE, but I am getting ahead of my self here!
In the Beginning…
Create a Database, Tablespace and a Table with a couple of indexes:
CREATE DATABASE "TESTDB"
BUFFERPOOL BP0
INDEXBP BP0
STOGROUP SYSDEFLT
;
COMMIT ;
CREATE TABLESPACE "TESTTS" IN "TESTDB"
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
LOG YES
DEFINE YES
DSSIZE 1 G
MAXPARTITIONS 1
BUFFERPOOL BP0
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS NO
MAXROWS 255
SEGSIZE 32
;
COMMIT ;
CREATE TABLE BOXWELL.TEST_BASE
(COL1 CHAR(12) NOT NULL
,COL2 INTEGER NOT NULL
,COL3 INTEGER NOT NULL)
IN TESTDB.TESTTS
;
COMMIT ;
CREATE UNIQUE INDEX BOXWELL.TEST_BASE_IX1 ON BOXWELL.TEST_BASE
(COL1, COL2, COL3)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
BUFFERPOOL BP0
CLOSE YES
COPY NO
;
COMMIT ;
CREATE INDEX BOXWELL.TEST_BASE_IX2 ON BOXWELL.TEST_BASE
(COL2, COL3)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
BUFFERPOOL BP0
CLOSE YES
COPY NO
;
COMMIT ;
Insert some data:
INSERT INTO BOXWELL.TEST_BASE VALUES ('A', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('B', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('C', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('D', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('E', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('F', 2 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('G', 2 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('H', 2 , 3);
INSERT INTO BOXWELL.TEST_BASE VALUES ('I', 2 , 3);
INSERT INTO BOXWELL.TEST_BASE VALUES ('J', 2 , 3);
COMMIT ;What Says RTS?
First, make sure the real-time statistics (RTS) have all been externalized:
-ACCESS DATABASE(TESTDB) SPACENAM(*) MODE(STATS)
Then run a little SQL:
SELECT *
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'TESTDB'
;
SELECT *
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE DBNAME = 'TESTDB'
;
You should see one row from SYSTABLESPACESTATS with 10 TOTALROWS and 10 REORGINSERTS etc. and two rows from SYSINDEXSPACESTATS with 10 TOTALENTRIES and 10 REORGINSERTS etc. Now we have what I call the „base“ table.
Use ISPF as well…
In ISPF 3.4 you should see datasets like this:
DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0001.A001
The Fun Begins …
Now we create a CLONE. To do this, you do *not* CREATE – that would be way too easy – a CLONE Table. You actually issue an ALTER statement like this:
ALTER TABLE BOXWELL.TEST_BASE ADD CLONE RINGO.AARDVARK ; COMMIT ;
Now do that RTS select and the ISPF 3.4 again … As if by magic you will now see double the rows in the RTS … Check out the INSTANCE column:
------+---------+---------+ PSID PARTITION INSTANCE ------+---------+---------+ 2 1 1 2 1 2
Aha! We now have two sets of RTS Counters – This is a good thing! ISPF also looks different:
DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0002.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0002.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0001.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0002.A001
Notice all the INSTANCE values here?
Finally the Boss Guy – SYSTABLESPACE. Here is where the access is controlled using, yet again, INSTANCE and its good friend CLONE:
SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'TESTDB'
;
--+---------+--
INSTANCE CLONE
--+---------+--
1 YThis is showing you all the information you need. The current base table is still the original table and this tablespace is in a „clone relationship“ – slightly better than „it’s complicated“ but close!
Test Select
Run this to see what you get back:
SELECT COUNT(*) FROM BOXWELL.TEST_BASE ; SELECT COUNT(*) FROM RINGO.AARDVARK ;
You should get ten from the first count and zero from the second.
So What Is the Point?
Now we, finally, get to the raison d’être of CLONEs. The idea is that using table name ringo.aardvark you can INSERT data, perhaps very slowly over a period of days, into the CLONE TABLE and the application is not aware of and cannot be affected by it. Once the INSERT processing is completed you may then do the EXCHANGE DATA command to instantaneously swap the tables around. OK, it must actually just do a one byte update of the INSTANCE column in the SYSTABLESPACE, but I digress…
Here’s How it Looks
EXCHANGE DATA BETWEEN TABLE BOXWELL.TEST_BASE
AND RINGO.AARDVARK
;
COMMIT ;Now do those COUNT(*) SQLs again:
SELECT COUNT(*) FROM BOXWELL.TEST_BASE ---------+---------+---------+-------- 0 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*) FROM RINGO.AARDVARK ---------+---------+---------+-------- 10 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Whoopee! You can see that the table name has not changed but all the data has! This is pretty cool!
Downsides …
Never a day without pain, my mother used to say, and CLONEs come with a bunch of pain points!
Pain Point Number One
Reduced utility support. You can only run MODIFY RECOVERY, COPY, REORG (without inline statistics!) and QUIESCE against these. Why? Because there is only one set of catalog statistics for them. A RUNSTATS would destroy all of the data for *both* objects and the current object access paths might all go south; further, you absolutely *must* add the keyword CLONE to the utility control cards. You *cannot* rely on LISTDEF to do this for you and it is documented:
This utility processes clone data only if the CLONE keyword is specified. The use of CLONED YES on the LISTDEF statement is not sufficient.
but people still miss this and then *think* they are working with their clones but they are not! This can get very embarrassing…
Pain Point Number Two
You must remember to RUNSTATS the „new“ base after the EXCHANGE has been done. The RTS is always in step, the Catalog is probably way out of line…
When You Are Quite Finished
Once a CLONE table is no longer required you can easily drop it but naturally not with a DROP but with another ALTER statement:
ALTER TABLE BOXWELL.TEST_BASE
DROP CLONE
;
COMMIT ;Pain Point Number Three
The problem here is not that bad, but, depending on when you do the DROP CLONE, your „base“ could be the instance two! You, and your vendors, must make sure your Db2 and non-Db2 utilities are happy with this state of affairs!
RealTime DBAExpert?
Yep, we are in the clear! Our software does indeed support these esoteric beasts.
Over To You!
Do you use CLONEs? If so, why? Any war stories or is everything hunky dory?
As usual I would love to hear from you!
TTFN,
Roy Boxwell
Updates
I got quite a few updates about clones:
DDL Disaster
One of the major issues that I missed was another pain point: DDL Changes. These are really nasty as you must throw away your clone before you can do the ALTER and then recreate the CLONE relationship.
Commands
I also did not mention that various commands also need the CLONE keyword to be applied to CLONE spaces. For example -START DATABASE(xxx) SPCENAM(yyy) CLONE