We all know, and love, REORG Mapping tables, right? Well, this month I want to run through the ins and outs of defining and using these little beasts!
In the Beginning
In the beginning, well DB2 V5 actually, was a requirement for a list of Row Ids (RIDs) to be stored so that the SHRLEVEL CHANGE REORG could map between an original RID and the new “shadow” RID. It was created with normal DDL:
CREATE TABLESPACE table-space-name SEGSIZE integer;
CREATE TABLE table-name1
(TYPE CHAR(1) NOT NULL,
SOURCE_RID CHAR(5) NOT NULL,
TARGET_XRID CHAR(9) NOT NULL,
LRSN CHAR(6) NOT NULL)
IN database-name.table-space-name
;
CREATE TYPE 2 UNIQUE INDEX index-name1 ON table-name1
(SOURCE_RID ASC,
TYPE,
TARGET_XRID,
LRSN)
;
Notice that the TARGET_XRID is nine characters even though a RID is, at least way back then, actually five bytes!
Tablespace or Index space size?
What was interesting from the start, was that the size of the tablespace was irrelevant – it was *only* the index that was ever used. So I saw a ton of tablespaces with 7200, 720 allocations, all of which were completely pointless. I always ALTERed these back to 48, 48. Actually, I normally just DROPped and CREATEd them again!
It is also interesting to see how many shops actually do COPY, RUNSTATS, and even REORGs of these tablespaces… Completely pointless of course as they are not used by normal SQL, never used for RECOVERY, and a waste of time for all utilities. If possible, EXCLUDE them from all utilities!
Static or Dynamic?
So shops started heading off in two directions: Some created 1000’s of mapping tables, all with Job name as schema or table name, so that they could just use generator software and always “know” that the mapping table would be there. The other group decided to add a DROP and CREATE step at the beginning of the REORG job, and a DROP at the end. This was known as “dynamic” mapping tables.
Static meant less traffic to the catalog, but 1000’s of tables loafing around doing nothing, Dynamic meant DB2 Catalog traffic but only a few tables existing and only for the lifetime of the REORG. Basically, it was down to every shop to work out their best way to handle these tables.
Changes through the releases
In DB2 V7 it was even recommended to name the table the same as the Utility ID and as these, by definition, are unique, you could never hit the “duplicate name” problem.
In DB2 V8 it was noted that REORG always empties the table after completion.
In DB2 V9 you could now also use a PBG for the tablespace. This is actually required if you have more than two billion rows in the tablespace to be REORGed.
CREATE TABLESPACE table-space-name MAXPARTITIONS integer;
instead of the segmented syntax.
All new ball game
In Db2 11 it all changed! The ability was given to use a MAPPINGDATABASE or even nothing at all! This latter option seemed great, at first glance, but then it became apparent that it caused Db2 catalog contentions… not good for massively parallel REORG jobs!
The MAPPINGDATABASE xxxxx value overides the ZPARM REORG_MAPPING_DATABASE in cases where you would like to use another “location” for your implicit tables. From the docu:
REORG MAPPING DB field (REORG_MAPPING_DATABASE subsystem parameter)
The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
Acceptable values: database-name
Default: blank
Update: option 31 on panel DSNTIPB
DSNZPxxx: DSN6SPRM.REORG_MAPPING_DATABASE
Online changeable: Yes
Data sharing scope: Member
database-name
The name of the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table. The name must a character string with maximum length of 8 bytes.
blank
An implicitly defined database is used.
When processing a REORG TABLESPACE SHRLEVEL CHANGE request, the REORG utility has the option to create its own mapping table and mapping index, instead of relying on user's input. Specifying this subsystem parameter with a valid database name directs REORG to allocate the mapping table in the database that is specified. By default, REORG uses an implicitly defined database for the mapping table allocation.
So nearly all shops have <blank> – which led to the creation of hundreds of implicit databases…
Top tip: Create a “master” Database that is *just* for REORG SHRLEVEL CHANGE and change the ZPARM to use it. Then you have a really good high level way to exclude utilities from bothering with these tables and you stop using up your DBIDs!
Also in Db2 11, the mapping table got updated as the LRSN increased to ten bytes…
<optional>CREATE TABLESPACE table-space-name SEGSIZE integer;
or
<optional>CREATE TABLESPACE table-space-name MAXPARTITIONS integer;
CREATE TABLE table-name1
(TYPE CHAR(1) NOT NULL,
SOURCE_RID CHAR(5) NOT NULL,
TARGET_XRID CHAR(9) NOT NULL,
LRSN CHAR(10) NOT NULL)
<optional>IN database-name.table-space-name
;
CREATE UNIQUE INDEX index-name1 ON table-name1
(SOURCE_RID ASC,
TYPE,
TARGET_XRID,
LRSN);
Actually, IBM changed the names of the columns as well (Though the column names are never really used…) so the CREATE really looks like:
<optional>CREATE TABLESPACE table-space-name SEGSIZE integer;
or
<optional>CREATE TABLESPACE table-space-name MAXPARTITIONS integer;
CREATE TABLE table-name1
(TYPE CHAR(1) NOT NULL,
ORID CHAR(5) NOT NULL,
NRID CHAR(9) NOT NULL,
LRSN CHAR(10) NOT NULL)
<optional>IN database-name.table-space-name
;
CREATE UNIQUE INDEX index-name1 ON table-name1
(ORID ASC,
TYPE,
NRID,
LRSN)
;
If this format was not available in NFM, then REORG created a table to use on its own using either an implicit database, or the database from the ZPARM, if specified. This uses up another DBID of course.
All change!
Then along came Db2 12 and they changed the mapping table again…
<optional>CREATE TABLESPACE table-space-name SEGSIZE integer;
or
<optional>CREATE TABLESPACE table-space-name MAXPARTITIONS integer;
CREATE TABLE table-name1
(TYPE CHAR(1) NOT NULL,
ORID CHAR(7) NOT NULL,
NRID CHAR(11) NOT NULL,
LRSN CHAR(10) NOT NULL)
<optional>IN database-name.table-space-name
;
CREATE UNIQUE INDEX index-name1 ON table-name1
(ORID ASC,
TYPE,
NRID,
LRSN)
;
This time, it was the RIDs expanding by two bytes each that forced the change. Just like in Db2 11 NFM, if this format was not available in Db2 12 FL500 and above, then REORG creates a table to use on its own using either an implicit database or the database from the ZPARM if specified.
Enough history…
So now we know how the table looks and how it is used and defined. The question is: Which of the various methods is the best?
Method 1: Define 1000’s of static mapping tables.
Method 2: Create each table in each utility job using the utility id as the mapping table name.
Method 3: Create a “master” Database and use it in the MAPPINGDATABASE utility parameter or ZPARM, thus allowing Db2 to create all tablespaces, tables, and indexes in it.
Method 4: Do nothing, have no MAPPINGxxx utility parameter and let Db2 “do the magic.”
Method 1: Is a bit mad, as whenever IBM development change the definition, you have 1000’s of changes to do and they do clutter up the Db2 catalog. However if you are paranoid about contention and you are doing lots, and I really mean *lots*, of parallel REORGS then this method is probably the best way to avoid DBD contention.
Method 2: Is not bad, as you only create as many objects as you have jobs and they are always dropped at the end, so it reduces clutter but it does increase the risk of Db2 Catalog contention.
Method 3: My favorite! (Unless doing *lots* of parallel REORGS!) You have one DB with one DBID and you can easily exclude it from other utilities and cleanup, and recreate is a simple DROP and CREATE. From my point of view, this is a winner! There is still Db2 Catalog contention of course as the TS, TB and IX must all be created before all being thrown away at the end of the REORG.
Method 4: Is the worst of the all IMHO. You will run through your DBIDs quicker than you can blink, and you increase contention on the Db2 Catalog as it must also create your implicit database all the time. This is for test only! However, it it does have the advantage of a new DBID every time and so very low chance of DBD contention if you have lots of parallel running REORGS.
Results from my tests
Using static caused no trouble anywhere and DBIDs did not get wasted.
Using dynamic, including creation of the database, allowed DBID reuse.
Using dynamic without the database also allowed DBID reuse as the DROP TABLESPACE also dropped the implicitly created database.
Using dynamic without the database & tablespace caused a Widow database to be left after dropping of the TABLE, (remember the current rules on dropping implicitly created databases? If you drop the Table then the Tablespace gets dropped but not the Database!) This is pretty bad of course, as not only are your DBIDs going up they are being left “used” which is not good!
Using MAPPINGDATABASE with an existing database works, as I mentioned, great and is my favorite! (Remember – Only if not doing massively parallel REORGS)
What are your thoughts on mapping table solutions?
I would love to hear from you!
TTFN
Roy Boxwell
Update: Michael Harper contacted me with some comments and I summarize them below:
- Definitely use a default mapping database – do not leave it blank as this will reduce contention issues
- If not using shared ZPARMs also use different default mapping databases as again this will reduce contention issues
Update: One of my readers pointed out that when running large numbers of parallel REORGS (Over 100!) you can hit DBD contention issues if you use one DB for the mapping Tables. In this scenario you either require a dynamic table being created for each job in its own database or use the implicit databases (DSNnnnnn) and make sure you DROP any widow style DBs.
Another Update: One of my readers mentioned that you can still get contention when you or Db2 create mapping tables. This is naturally true for creating anything within Db2. If you want a 100% guarantee of no contention then you *must* create all mapping tables in advance which would be Method 1 in my Blog.