Hi all! Welcome to the end-of-year goody that we traditionally hand out. This year is a relaunch of the Migration HealthCheck that we first did over two years ago. I’ve also provided some news about Db2 13 UTS PBR RPN spaces that might be of interest to you!
DB2 12 UTS PBR RPN
You might well know that this was my single favorite feature of Db2 12. What I did not really appreciate, until now, was the fact that getting these very big partitions can come with a major price!
WHAT IS THE PROBLEM?
If you have a Data Sharing system (Who does not these days?) and you happen to use LOCKSIZE ROW on your UTS PBR RPN then you should take a good look at your performance monitor data. If you see a high number of P-Locks, and the number of false contentions is greater than your IRLM–SUSPENDS, then BINGO!
THE FIX IS?
Documented in the red book „Db2 13 Performance Topics“, Chapter 5 Data Sharing, 5.1 „Partition-by-range table space relative page numbering enhancements“ is the information above, and a lot more. The crux of the matter is a new Hash Algorithm, and to get to it you must simply REORG any UTS PBR RPN spaces that were created prior to Db2 13 FL500.
HERE IS SOME SQL
Here is some SQL to list out any and all of your UTS PBR RPN table partitions that were created prior to Db2 13 FL500 and have not yet been REORGed or LOAD REPLACED.
First check that you are actually *at* Db2 13 R1 FL500!
SELECT LU.EFFECTIVE_TIME
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
WITH UR
FOR FETCH ONLY
;
This simply returns the time when the FL500 was „activated“ in your Db2 13 system. If it returns no rows then you cannot do anything…
THE USUAL SUSPECTS…
Then we get the Partitions of interest:
SELECT SUBSTR(TP.DBNAME , 1 , 8) AS DBNAME
,SUBSTR(TP.TSNAME , 1 , 8) AS TSNAME
,TP.PARTITION
,TP.CREATEDTS
,TP.REORG_LR_TS
FROM SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSTABLEPART TP
WHERE 1 = 1
AND TS.DBNAME = TP.DBNAME
AND TS.NAME = TP.TSNAME
AND TS.TYPE = 'R' -- UTS PBR ONLY
AND TS.LOCKRULE = 'R' -- ROW LEVEL LOCKING ONLY
AND TP.PAGENUM = 'R' -- UTS PBR RPN ONLY
AND TP.CREATEDTS < (SELECT LU.EFFECTIVE_TIME
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
) -- CREATED BEFORE FL500 ACTIVATED
AND TP.REORG_LR_TS < (SELECT LU.EFFECTIVE_TIME
FROM SYSIBM.SYSLEVELUPDATES LU
WHERE 1 = 1
AND LU.FUNCTION_LVL = 'V13R1M500'
AND LU.OPERATION_TYPE = 'F'
) -- LAST REORG/LOAD BEFORE FL500 ACTIVATED
ORDER BY 1 , 2 , 3
WITH UR
FOR FETCH ONLY
;
This query uses the effective timestamp, created timestamp and the last reorg load replace timestamp to filter out all the partitions that do not need to be REORGed or LOAD REPLACEd.
Please also remember you only need to do all this when you have ROW LEVEL locking in data sharing with high CPU p-locks. The red book shows some very impressive CPU savings!
BACK TO THE HOLIDAYS!
Over the last two years we have added and enhanced our Migration HealthCheck a lot. Improvements include testing what happens with DEFINE NO spaces when they were created years and releases ago but would now be externalized. All of this means the output has changed, of course.
TELL ME MORE!
Here’s a list of all the deprecated (and semi-deprecated) items that should be checked and verified at your site:
- Use of SYNONYMS
- Use of HASH objects
- Use of segmented spaces
- Use of classic partitioned objects (not using table based partitioning)
- Use of simple spaces
- Use of six-byte RBA
- Use of BRF
- Use of LARGE objects (This is semi-deprecated)
- SQL EXTERNAL Procedures
- UNICODE (VARBIN Columns)
- Old RLF table defs
- Old PLAN_TABLE defs
- Old bound packages in use in the last 548 days
- Direct bound DBRMs (Yes they can still exist!)
ANYTHING ELSE?
Well yes! You could also check how many empty implicit databases and how many empty tablespaces you have. While you are scanning your subsystem, it could also be cool to list out all the Db2 subsystem KPIs. What about seeing how many tables you actually have in multi-table tablespaces that, at some point, must also be migrated off into a UTS PBG or UTS PBR tablespace?
WE DO IT ALL!
Our little program does all of this for you. It runs through your Db2 Catalog in the blink of an eye and reports all of the data mentioned above.
WHAT DOES IT COST?
Nothing – It is our freeware for 2022/2023 and you only have to be registered on our website to request it along with a password to run it.
HOW DOES IT LOOK?
Here is an example output from one of my test systems here in Düsseldorf:
Db2 Migration HealthCheck V2.3 for SC1 V12R1M510 started at
2022-12-14-10.56.00
Lines with *** are deprecated features
Number of DATABASES : 594
# of empty DATABASES : 237
# of implicit DATABASES : 385
# of empty implicit DATABASES: 207
Number of TABLESPACES : 4861
of which HASH organized : 0
of which PARTITIONED CLASSIC : 2 ***
# Partitions : 32 ***
of which SEGMENTED : 294 ***
of which SIMPLE : 0
of which LOB : 67
of which UTS PBG : 4467
# Partitions : 4477
of which UTS PBR (Absolute) : 5
# Partitions : 801
of which UTS PBR (Relative) : 6
# Partitions : 756
of which XML : 20
Number of tablespaces as LARGE : 8 ***
Number of empty tablespaces : 28
Number of multi-table TSs : 55
# of tables within these : 239
Number of incomplete TS : 1 XXX
Number of INSERT ALG 0 TS : 4861
Number of INSERT ALG 1 TS : 0
Number of INSERT ALG 2 TS : 0
Number of tables : 10293
of which ACCELERATOR ONLY : 0
of which ALIASes : 5307
of which ARCHIVEs : 1
of which AUXs : 60
of which CLONEs : 0
of which GTTs : 136
of which HISTORYs : 1
of which MQTs : 1
of which TABLEs : 4765
of which VIEWs : 2
of which XMLs : 20
Number of tables with Audit : 101
Number of tables with Data Cap : 0
Number of tables incomplete : 1 XXX
Number of tables with control : 0
Number of RLF DSNRLMT__ tables : 0
of which columns deprecated : 0
Number of RLF DSNRLST__ tables : 1
of which columns deprecated : 0
Number of PLAN_TABLES : 68
of which deprecated : 3 ***
Number of SYNONYMs : 1 ***
Number of UNICODE V11 Columns : 0
Number of PROCEDURES : 110
of which SQL EXTERNAL : 0
of which EXTERNAL : 108
of which NATIVE SQL : 2
Number of FUNCTIONS : 87
of which EXTERNAL TABLE : 38
of which EXTERNAL SCALAR : 42
of which SOURCED AGGREGATE : 0
of which SOURCED SCALAR : 0
of which SQL TABLE : 1
of which SQL SCALAR : 6
of which SYSTEM-GENERATED : 0
Number of Indexes : 23243
of which HASH : 0
of which type 2 : 23210
# of partitioned IXs : 6
# Partitions : 160
of which DPSI : 18
# Partitions : 164
of which PI : 15
# Partitions : 1138
Number of indexes COPY YES : 38
Number of indexes COMPRESS YES : 0
Number of table partitions : 6606
of which DEFINE NO : 2848
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 3759
Number of TP in BRF : 0
Number of TP with COMPRESS Y : 498
Number of TP with COMPRESS F : 0
Number of TP with COMPRESS H : 0
Number of TP with TRACKMOD YES : 2968
Number of index partitions : 24666
of which DEFINE NO : 20140
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 4527
Number of STOGROUPS : 10
Number of non-SMS VOLUMES : 0
Number of PLANs : 54
of which DBRMs direct : 0
# of SQL statements : 0
Number of PACKAGES (total) : 5788
of which VALID = A : 42
of which VALID = H : 0
of which VALID = N : 44
of which VALID = Y : 5702
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 5788
Old RELBOUND executed packages : 0
Number of PACKAGES (distinct) : 480
Number of Original PACKAGES : 0
Number of Previous PACKAGES : 0
Number of Phased-out PACKAGES : 0
Total number of PACKCOPY : 0
of which VALID = A : 0
of which VALID = H : 0
of which VALID = N : 0
of which VALID = Y : 0
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 0
Number of SQL statements : 441833
Db2 Migration HealthCheck V2.3 for SC10 V12R1M510 ended at
2022-12-14-10.56.03
Db2 Migration HealthCheck ended with RC: 0
Any line with *** at the end means that you have something to do at some point in the future. The names of all the found objects are written to DD card DEPRECAT so you can then start building a „to do“ list. I would start now to slowly „fix“ all of these before it is 03:00 in the morning, someone is migrating to Db2 14 FL 608 and it all goes horribly wrong…
Any line with XXX means that you have an incomplete definition for a tablespace and/or a table. These should be fixed as well, either by completing the definition or dropping the unfinished object(s).
WHAT’S WRONG WITH LARGE?
This is not actually deprecated but any tablespaces marked as LARGE tend to also not have a valid DSSIZE in them. This is fine if you have built a CASE construct to derive the value from the tablespace definition. But what you should do, is an ALTER and a REORG to „move“ the LARGE to a „proper“ tablespace. IBM and 3rd Party Software vendors hate having to remember that ancient tablespaces are still out there!
ALL ON MY OWN?
Naturally not! For example, after all the ALTERs have been done, a lot of the spaces are simply in Advisory REORG pending status and you could use our RealtimeDBAExpert (RTDX) software to automatically generate the required REORGs to action the changes.
SYNONYMS??
Well, you can do them all yourself by reading one of my older newsletters – just remember to watch out for the GRANTs afterwards.
HOW MANY DEPRECATED OBJECTS DO YOU HAVE?
I would love to get screenshots of the output at your sites which I would then all sum up and publish as an addendum to this newsletter. Just so that people can see how many Parrots we all have pining for the fjords!
TTFN, Happy Holidays!
Roy Boxwell