DB2 - L

Expand all | Collapse all

Clone Table RTS Stats Question.

  • 1.  Clone Table RTS Stats Question.

    Posted Nov 15, 2020 08:19 PM

    A Clone Table lives in same UTS as the original table.   It can have two entries in SYSTABLESPACESTATS being instance 1 and instance 2, yet   SYSTABLESPACE and SYSTABLEPART have one row each (SYSTABLESPACE has INSTANCE=1 only). SYSTABLES has 2 rows, one with Type C.

    So how to tell which RTS Instance is the Clone? I could not see anything in the DB2 Catalog to specify it. 

    Initially Clone is likely to be INSTANCE = 2, but after Exchange of data, it is Instance 1, and original table is Instance 2.

    So the RTS supposedly allows space to be monitored on each of 2 tables separately, but perhaps not really, and we just have to add them together?

    Are there other situations where 2 instances will both be valid, unrelated to Clones?

    Michael Hannan
    DB2 SQL and Performance Specialist

  • 2.  RE: Clone Table RTS Stats Question.

    Posted Nov 16, 2020 03:16 AM

    When you create the CLONE you actually get brand new VSAM datasets:


    The INSTANCE 2 in the above list were all created by the


    I simply join the INSTANCE in SYSTABLESPACE to the INSTANCE in the RTS and you are done! As this is the "active" version of the data and obviously, the "other" instance is the "clone" version.

    Roy Boxwell

    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

  • 3.  RE: Clone Table RTS Stats Question.

    Posted Nov 16, 2020 05:01 AM

    Thanks Roy,

    I did not check SYSTABLESPACE again after my Exchange Tables to see that indeed its instance number had switched from 1 to 2.

    I expected joins to match things rather than deduce by absence or inference  that the other instance must be the Clone.

    Rather amusing. 

    I must celebrate. This is maybe the first time, at least in near memory, I ever got a quick and definitive answer to a question that was baffling me.   I am clearly not an optimist. Yet I was expecting an answer of something moderately obvious, but just was eluding me.

    Michael Hannan
    DB2 SQL and Performance Specialist

  • 4.  RE: Clone Table RTS Stats Question.

    Posted Nov 16, 2020 05:46 AM
    SYSTABLESPAXESTATS does have a column named INSTANCE so this will reflect the activity on base/clone.


  • 5.  RE: Clone Table RTS Stats Question.

    Posted Nov 16, 2020 12:17 PM

    Having worked with CLONE tables for sometime, and got burnt by this one. My ONE BIG recommendation - Isolate the table into its own database to avoid issues when performing the exchange as it needs a DBD lock to complete. 

    This doesn't 100% guarantee the exchange, as any claimers/readers on the tablespace will also cause issues


    JackCampbellSaxon Consutling,Inc.

  • 6.  RE: Clone Table RTS Stats Question.

    Posted Nov 19, 2020 06:00 PM

    Thanks Jack,

    DBD locks are always an annoying thing to me, except for obviously DDL has to update it. I still want to know why certain R.I. operations can lock the DBD, even if is a share lock. Makes it harder to create Indexes (even when Not Unique Defer Yes), without problems, and could interfere with an EXCHANGE Clone too. If I understood I might recommend against using R.I. more strongly.

    I was just adjusting queries on Catalog to Cater for Clone table RTS Stats.  It is quite evident that Exchange is DDL rather than DML to me, so updating the DBD is fairly obvious, and many DDL operations could be risky for contention in operational systems.  So a Daily EXCHANGE to switch between 2 tables might not be a great idea, unless have a quiet time. 

    I always say that SQL gives us a lot of rope to hang ourselves, if we don't use it wisely. We therefore need to understand how it works, somewhat. Roger Miller used to say "Don't ask us how it works, tell us your problem, and we will tell you how to fix it." Ha ha, a joke I guess. Sorry, we don't need to make serious mistakes at design time.  There are many SQL features that need to be used sparingly if at all.

    For switching between two tables I quite often see application SQL that has to UNION the two tables and use a switch subquery predicate  to decide which of them will actually be queried to find rows.   I had thought of the Clone table with EXCHANGE as an option but not necessarily a good one, as you say.

    Queries that UNION are not necessarily as much of an access path problem as they used to be. Optimizer team in V12 has given us some enhancements. We have predicate push down to each leg, ORDER BY push down to each leg with any FETCH FIRST n ROWS ONLY, with final Sort doing a merge of the sort results.   FETCH FIRST n ROWS should be used heavily in online or any application that will not Fetch the entire result set.  Enhancements were need to handle Temporal and Archive tables better.

    Michael Hannan
    DB2 SQL and Performance Specialist

  • 7.  RE: Clone Table RTS Stats Question.

    Posted Nov 23, 2020 12:43 PM

    We worked with IBM to get an RFE which eventually became LOAD (Shrlevel=Reference), then de-cloned all the tables and switched to the new format of LOAD - switch works the same as REORG.

    No need for UNION of 2 tables in SQL , LOAD completes or fails on shadow copy, and removed the (other) clone table issues we were encountering.


    JackCampbellSaxon Consutling,Inc.

  • 8.  RE: Clone Table RTS Stats Question.

    Posted Nov 27, 2020 02:11 AM

    Nice Jack. I had not really studied this newer LOAD option into a shadow Copy. Looks good!

    Bottom line is Clones maybe something that rarely needs to be used.
    I also don't like to see Scrollable Cursors used at all in OLTP, but tolerate them for low volume work.
    There are plenty of DB2 functions that need to be used very sparingly (if at all), but not many will be wise enough to know the difference.

    Notification of the dangers and down sides is valuable.

    Michael Hannan
    DB2 SQL and Performance Specialist