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?
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.
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.
2601 Iron Gate Drive, #101Wilmington, NC 28412Phone: (910) 660-8649Fax: (910) 523-5504