This year’s end-of-year goody giveaway is a revamped and updated version of our 2020 Happy Holiday Present. This time focused on the migration blockers that will stop you getting to vNext!
What are the Problems?
The number one problem with Db2 system migrations, is the deprecated features that are still firmly nailed to their perches but are 100% dead. They will not cause a problem today, or tomorrow, but at some point they will start to smell … and I mean smell really bad!
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)
IBM, well actually Haakon Roberts at the IDUG EMEA in 2024, announced a list of deprecated features or functionality that will block migration to Db2 vNext:
This list includes nearly all of the first list and added SNA/VTAM usage.
Anything Else?
Well, yes! You could also check how many empty implicit databases you have and how many empty tablespaces you have while you are checking your subsystem out. While you are scanning, it could also be cool to list out all the Db2 subsystem KPIs, and 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 new 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 plus the five deprecated ZPARMs that you should also verify these days!
What does it cost?
Nothing – It is our licensed freeware for 2025/2026 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 Readiness HealthCheck V1.0 for SD1 V13R1M507 started at 2025-08-27-10.05.32
Lines with *** are deprecated features
Lines with MMM are migration blockers
Lines with XXX are definition errors
Number of DATABASES : 122
# of empty DATABASES : 17
# of implicit DATABASES : 65
# of empty implicit DATABASES: 15
Number of TABLESPACES : 2216
of which HASH organized : 0
of which PARTITIONED CLASSIC : 0
# Partitions : 0
of which SEGMENTED : 19 MMM
of which SIMPLE : 3 MMM
of which LOB : 63
of which UTS PBG : 2115
# Partitions : 2115
of which UTS PBR (Absolute) : 0
# Partitions : 0
of which UTS PBR (Relative) : 4
# Partitions : 24
of which XML : 12
Number of tablespaces as LARGE : 0
Number of empty tablespaces : 6
Number of multi-table TSs : 12
# of tables within these : 49
Number of incomplete TS : 7 XXX
Number of INSERT ALG 0 TS : 2206
Number of INSERT ALG 1 TS : 10
Number of INSERT ALG 2 TS : 0
Number of tables : 4520
of which ACCELERATOR ONLY : 0
of which ALIASes : 2164
of which ARCHIVEs : 0
of which AUXs : 63
of which CLONEs : 0
of which GTTs : 100
of which HISTORYs : 1
of which MQTs : 1
of which TABLEs : 2170
of which VIEWs : 9
of which XMLs : 12
Number of tables with Audit : 1
Number of tables with Data Cap : 2
Number of tables incomplete : 1 XXX
Number of tables with control : 1
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 : 34
of which deprecated : 27 ***
Number of SYNONYMs : 0
Number of UNICODE V11 Columns : 0
Number of PROCEDURES : 116
of which SQL EXTERNAL : 0
of which EXTERNAL : 109
of which NATIVE SQL : 7
Number of FUNCTIONS : 87
of which EXTERNAL TABLE : 39
of which EXTERNAL SCALAR : 42
of which SOURCED AGGREGATE : 0
of which SOURCED SCALAR : 0
of which SQL TABLE : 0
of which SQL SCALAR : 2
of which SYSTEM-GENERATED : 4
Number of Indexes : 2594
of which HASH : 0
of which type 2 : 2594
# of partitioned IXs : 0
# Partitions : 0
of which DPSI : 0
# Partitions : 0
of which PI : 0
# Partitions : 0
Number of indexes COPY YES : 7
Number of indexes COMPRESS YES : 0
Number of table partitions : 2236
of which DEFINE NO : 1024
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 1212
Number of TP in BRF : 17 MMM
Number of TP with COMPRESS Y : 43
Number of TP with COMPRESS F : 0
Number of TP with COMPRESS H : 0
Number of TP with TRACKMOD YES : 2234
Number of index partitions : 2594
of which DEFINE NO : 1324
of which six byte RBA <11 NFM: 0
of which six byte RBA Basic : 0
of which ten byte RBA : 1270
Number of STOGROUPS : 2
Number of non-SMS VOLUMES : 0
Number of PLANs : 39
of which DBRMs direct : 0
# of SQL statements : 0
Number of PACKAGES (total) : 2697
of which VALID = A : 12
of which VALID = H : 0
of which VALID = N : 6
of which VALID = Y : 2679
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 2697
of which OPERATIVE = R : 0
Old RELBOUND executed packages : 0
Number of PACKAGES (distinct) : 482
Number of Original PACKAGES : 278
Number of Previous PACKAGES : 278
Number of Phased-out PACKAGES : 271
Total number of PACKCOPY : 827
of which VALID = A : 30
of which VALID = H : 0
of which VALID = N : 0
of which VALID = Y : 797
of which VALID = S : 0
of which OPERATIVE = N : 0
of which OPERATIVE = Y : 827
of which OPERATIVE = R : 0
Number of SQL statements : 109143
LULIST entries found : 1 MMM
LUMODES entries found : 1 MMM
LUNAMES entries found : 7 MMM
MODESELECT entries found : 1 MMM
ZPARM CHECK_FASTREPLICATION set to REQUIRED is at correct value REQUIRED and Ok.
ZPARM CMTSTAT set to INACTIVE is at correct value INACTIVE and Ok.
ZPARM DISALLOW_SEL_INTO_UNION set to YES is at correct value YES and Ok.
ZPARM MATERIALIZE_NODET_SQLTUDF set to NO is not at correct value YES. ***
ZPARM PREVENT_NEW_IXCTRL_PART set to YES is at correct value YES and Ok.
DDF command prefix -SD10 the IPNAME is not set to "-NONE" and Ok.
Db2 Migration Readiness HealthCheck V1.0 for SD1 V13R1M507 ended at 2025-08-27-10.05.34
Migration to vNext is not possible
Db2 Migration Readiness HealthCheck ended with RC: 4
Note that any MMM will be flagged as Return Code 4 with the message that „Migration to vNext is not possible“
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…
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 – 2016-01 Simply Synonyms in DB2 z/OS – (again) just remember to watch out for the GRANTs afterwards.
That’s a Huge Amount of Work!
Well, there is also a licenced version that creates all the ALTERs, REORGs, RUNSTATS and REBINDs for you – Costs a bit of money, but makes the entire project much easier to handle!
How many Blockers 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
Roy Boxwell