db2 11 RBA/LRSN Migration 6 to 10 bytes: How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s?
Are you thinking days, months, years??
This month, I want to discuss the pretty big changes that appeared in db2 11, as these have been made even more important with db2 12 coming around the corner.
End-of-Time
In db2 11, the so-called end-of-time or end-of-log problem finally got addressed. The old “it will never run out” six byte RBA (2 to the power of 48 or 256TB) was extended on the left with four bytes of zeroes up to 10 bytes, and the “valid until 17th September 2042 and incremented every 16 microseconds” six byte LRSN was also extended on the left with one byte, and on the right with three bytes. This means the RBA can address 2 to the power of 80 or one Yotta Byte and the LRSN goes up to about the year 36,000. More importantly it goes down to nearly the picosecond!
2042! I will be retired by then…
Well, hold your horses! The end-of-time problem can occur way earlier than that, due to an idiosyncrasy of upgrading to datasharing from non-datasharing. To do this, db2 must transform your current 6 Byte RBA into a 6 byte LRSN. Naturally an LRSN is a date/time and the RBA just a byte address, so db2 basically rounds the RBA up to be an LRSN and adds a so-called “DELTA” value to the BSDS. This delta value can surprise you, badly! One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018… Whoops!
This delta value can surprise you, badly!
One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018…
Check your delta!
Just run a DSNJU004 on your BSDS and check for the STCK TO LRSN DELTA line:
DSNJCNVT CONVERSION PROGRAM HAS RUN DDNAME=SYSUT1 LOG MAP OF BSDS DATA SET COPY 1, DSN=SB10.BSDS01 LTIME INDICATES LOCAL TIME, ALL OTHER TIMES ARE GMT. DATA SHARING MODE IS ON SYSTEM TIMESTAMP - DATE=2017.079 LTIME=19:29:46.01 UTILITY TIMESTAMP - DATE=2016.071 LTIME=18:19:43.66 VSAM CATALOG NAME=SB10 HIGHEST RBA WRITTEN 000000000000FCD54000 2017.079 20:29:46.0 HIGHEST RBA OFFLOADED 000000000000FBF0AFFF RBA WHEN CONVERTED TO V4 00000000000000000000 MAX RBA FOR TORBA 00000000000000000000 MIN RBA FOR TORBA 00000000000000000000 STCK TO LRSN DELTA 00000000000000000000
Here in one of our baby datasharing systems there is no delta, so I can retire!
Roadworks ahead!
I like to think of the RBA/LRSN like a three lane German highway (so six lanes in total) that is getting widened to five lanes (so ten in total) You just *know* that the throughput will go up and the traffic jams will go down!
Will it really help?
Well, the out-of-the-box benefits are threefold:
- No LRSN “spin” – In datasharing a member must wait or “spin”, for some styles of inserts/updates, until it gets a unique LRSN. Now, with faster and faster machines, CPU is being wasted doing nothing but spinning its heels! The IBM Labs state that the percentage overhead ranges between 0% – 6%, and that heavy batch can be much more – even as much as 20%+
- All of the conversion from and to is then gone. Externally always 10, internally a mix. The puffing up and the shrinking down also takes CPU cycles
- Converting Logs and tables “may yield a few percent” performance improvement – This again from the IBM Labs.
Road map required!
How to get there? Well first, in NFM, migrate the BSDS from each member one at a time and when all members are done, analyze your workload and pick the biggest usage of UPDATE/DELETE. REORG these objects at the TP/TS level. This gives the biggest improvement earliest.
Rolling on the REORGs
Then trickle through the REORGS on *all* user objects. Here *all* means *all* ! Well actually not all … clone tables cannot be migrated to 10 bytes so you must drop the clone table, REORG the base tablespace and then recreate the clone table.
Remember here to make sure your ZPARMS (OBJECT_CREATE_FORMAT EXTENDED and UTILITY_OBJECT_CONVERSION NOBASIC) are correct and that the Reordered Row Format (RRF) is enabled, since Basic Row Format (BRF) is deprecated!
Forward thinking!
Other things to plan, and think about, at the same time are:
Migrate all INDEX based Partitioned Objects to TABLE based Partitioned Objects and then migrate these to be partitioned by Range PBR (UTS). First an ALTER INDEX xxx.yyy NOT CLUSTER to make it table based instead of index based. Then an ALTER TABLESPACE xx.yy SEGSIZE nn to make it into a PBR (UTS)
Migrate any single table simple/segmented objects to be Partitioned by Growth PBG (UTS). Just an ALTER TABLESPACE xx.yy MAXPARTITIONS 1 is enough
Note that these simple ALTERS cause PACKAGE Invalidation and so must be timed correctly.
Why all the fuss?
Why do this? Well, remember that the UTS, as the underlying tablespace, is the *only* space where db2 is adding new functionality and performance. Think about inline LOBs, especially with COMPRESS YES, FTB in db2 12, HASH method, Fast Insert etc. It is clearly the aim of IBM to go to a purely UTS world at some point.
Relative Page Numbering – Should I wait?
In db2 12, the Relative Page Numbering (RPN) system enables varying sizes of DSSIZE, also at the Index Level. This is seriously good news, *but* it requires a TS level REORG to get there, and you must be in db2 12. So if you have *vast* history style partitioned tables, you would wait until you are in db2 12 before you actually do the first big REORG, including going to 10 byte RBA/LRSN if space and time are a big worry.
Are you alone?
No, of course not! Our product RealTimeDatabaseExpert (RTDX) can do all this for you. We also guarantee that no object will be left “hanging”, due to using our BatchControl technology. We also use look-ahead features with time windows to check that we are *not* going to encroach into productive timeframes. If you also have our WorkLoadExpert (WLX) software installed, we can even pick the right moment to fire off the REORGs that you need when you can actually run them! This permanent trickle of reorgs means that within a few years you are done and ready to take advantage of that ten-lane Autobahn!
This newsletter is interrupted by our marketing department who are calling out
“By the way, this tailored solution for this special requirement is also available for rent!”
See our RTDX Flyer and RealTime DBAExpert page .
Now back to our usual programming…
How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s? Are you thinking days, months, years?? I would love to know! To help you in this phase here are some little SQLs that will give you an idea of how far you have to go…based on the assumption that anything REORGed in the last six months will probably get REORGed again.
SELECT COUNT(*) AS INDEXPARTS_TOTAL
FROM SYSIBM.SYSINDEXSPACESTATS;
SELECT COUNT(*) AS INDEXPARTS
, SUM(COALESCE(NACTIVE, 0)) AS NACTIVE
, SUM(COALESCE(REORGINSERTS, 0)) AS REORGINSERTS
, SUM(COALESCE(REORGDELETES, 0)) AS REORGDELETES
, SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE (REORGLASTTIME IS NULL
OR (REORGLASTTIME IS NOT NULL
AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
OR
(REORGLASTTIME IS NOT NULL
AND (LOADRLASTTIME IS NULL
OR (LOADRLASTTIME IS NOT NULL
AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))
;
SELECT COUNT(*) AS TABLEPARTS_TOTAL
FROM SYSIBM.SYSTABLESPACESTATS
;
SELECT COUNT(*) AS TABLEPARTS
, SUM(COALESCE(NACTIVE, 0)) AS NACTIVE
, SUM(COALESCE(REORGINSERTS, 0)) AS REORGINSERTS
, SUM(COALESCE(REORGUPDATES, 0)) AS REORGUPDATES
, SUM(COALESCE(REORGDELETES, 0)) AS REORGDELETES
, SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS
WHERE (REORGLASTTIME IS NULL
OR (REORGLASTTIME IS NOT NULL
AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
OR (REORGLASTTIME IS NOT NULL
AND (LOADRLASTTIME IS NULL
OR (LOADRLASTTIME IS NOT NULL
AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))
;
Naturally if you are *in* db2 11 you can easily extend these queries to give you proper feedback like:
SELECT COUNT(*) AS INDEXPARTS
, SUM(CASE WHEN B.RBA_FORMAT = 'E'
THEN 1 ELSE 0 END) AS INDEXPARTS_EXTENDED
, SUM(CASE WHEN B.RBA_FORMAT = 'U'
THEN 1 ELSE 0 END) AS INDEXPARTS_DEFINE_NO
, SUM(CASE WHEN B.RBA_FORMAT = 'B'
THEN 1 ELSE 0 END) AS INDEXPARTS_BASIC
, SUM(CASE WHEN B.RBA_FORMAT = ' '
THEN 1 ELSE 0 END) AS INDEXPARTS_MIGRATED
FROM SYSIBM.SYSINDEXSPACESTATS A
,SYSIBM.SYSINDEXPART B
WHERE A.CREATOR = B.IXCREATOR
AND A.NAME = B.IXNAME
AND A.PARTITION = B.PARTITION
;
SELECT B.RBA_FORMAT AS INDEXPART_FORMAT
, COUNT(*) AS INDEXPARTS
, SUM(1E00 * COALESCE(A.NACTIVE, 0)) AS NACTIVE
, SUM(1E00 * COALESCE(A.REORGINSERTS, 0)) AS REORGINSERTS
, SUM(1E00 * COALESCE(A.REORGDELETES, 0)) AS REORGDELETES
, SUM(1E00 * COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS A
,SYSIBM.SYSINDEXPART B
WHERE A.CREATOR = B.IXCREATOR
AND A.NAME = B.IXNAME
AND A.PARTITION = B.PARTITION
AND B.RBA_FORMAT IN ('B' , ' ')
AND ((A.REORGLASTTIME IS NULL
OR (A.REORGLASTTIME IS NOT NULL
AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
OR
(A.REORGLASTTIME IS NOT NULL
AND (A.LOADRLASTTIME IS NULL
OR (A.LOADRLASTTIME IS NOT NULL
AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))
GROUP BY B.RBA_FORMAT
;
SELECT COUNT(*) AS TABLEPARTS
, SUM(CASE WHEN B.RBA_FORMAT = 'E'
THEN 1 ELSE 0 END) AS TABLEPARTS_EXTENDED
, SUM(CASE WHEN B.RBA_FORMAT = 'U'
THEN 1 ELSE 0 END) AS TABLEPARTS_DEFINE_NO
, SUM(CASE WHEN B.RBA_FORMAT = 'B'
THEN 1 ELSE 0 END) AS TABLEPARTS_BASIC
, SUM(CASE WHEN B.RBA_FORMAT = ' '
THEN 1 ELSE 0 END) AS TABLEPARTS_MIGRATED
FROM SYSIBM.SYSTABLESPACESTATS A
,SYSIBM.SYSTABLEPART B
WHERE A.DBNAME = B.DBNAME
AND A.NAME = B.TSNAME
AND A.PARTITION = B.PARTITION
;
SELECT B.RBA_FORMAT AS TABLEPART_FORMAT
, COUNT(*) AS TABLEPARTS
, SUM(COALESCE(A.NACTIVE, 0)) AS NACTIVE
, SUM(COALESCE(A.REORGINSERTS, 0)) AS REORGINSERTS
, SUM(COALESCE(A.REORGUPDATES, 0)) AS REORGUPDATES
, SUM(COALESCE(A.REORGDELETES, 0)) AS REORGDELETES
, SUM(COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS A
,SYSIBM.SYSTABLEPART B
WHERE A.DBNAME = B.DBNAME
AND A.NAME = B.TSNAME
AND A.PARTITION = B.PARTITION
AND B.RBA_FORMAT IN ('B' , ' ')
AND ((A.REORGLASTTIME IS NULL
OR (A.REORGLASTTIME IS NOT NULL
AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
OR
(A.REORGLASTTIME IS NOT NULL
AND (A.LOADRLASTTIME IS NULL
OR (A.LOADRLASTTIME IS NOT NULL
AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))
GROUP BY B.RBA_FORMAT
;Here the RBA_FORMAT value “E” is extended (10 Bytes), “U” is DEFINE NO but if you have the ZPARM OBJECT_CREATE_FORMAT set to EXTENDED (which you should!) then when an insert happens it will get created as extended. The problem children are “B” and Blank.
One late bit of info that came from a reader that some of you might find interesting, or perhaps worrying, is this:
There is a potentially time-consuming pre-requisite to the migration and that is the changes made to InfoSphere Information Replication 10.2.1 which expand two columns COMMITSEQ and INTENTSEQ from 10 to 16 bytes to accommodate 10-bytes RBA/LRSN. This modification has a big impact on application programs that make use of these two columns, especially COBOL programs that must be changed and recompiled and regression tested; as well as all SORT statement on the data files extract that include these columns, etc.
As usual any comments or criticisms are greatly appreciated
TTFN, Roy Boxwell