This month I wish to stroll along the dangerous and difficult road called Db2 Release Migration. We all love it as it means a new release with all new go-faster features and whizzbang things to play with but we also all hate it as it means we have things to do before we can actually migrate and get to this new promised land.
With Db2 for z/OS vNext coming up soon we are now back in the Driving Seat!
The list of deprecated items is pretty long and now about 80% of them have been finally deleted. If you have, or use, any of them you will *not* be joining the rest of us on the other side of the valley where the grass is definitely much greener!
The list of killed off items is:
- Simple tablespaces
- Segmented tablespaces
- Classic partitioned tablespaces (With or without Index based partitioning)
- Basic (Six byte) format
- BRF (Basic Row Format so not yet at RRF – Reordered Row Format)
- Hash Access
- Synonyms
- VTAM/SNA
Haakon Roberts shared this data at the IDUG EMEA 2024 in Valencia and it was the first time we had all seen an inkling of what would come. The real surprise, at least for me, was the requirement to remove VTAM/SNA support and move over to TCP/IP. The rest we had all known about for years but killing off VTAM/SNA was brand new!
CATMAINT to the Rescue?
Nope! None of these features will be automagically fixed by running a CATMAINT. It is all manual work and up to us, the DBAs fighting at the front, to fix “When we have some spare time…”
An ALTER a Day keeps the Dr at Bay
The basic “cure” for most of these problem children is simply an ALTER and a REORG, but it *never* is that easy, is it? To fix segmented or simple tablespaces with just one table within them it is an ALTER to MAXPARTITIONS 1 which will kick the tablespace into the world of UTS PBG. Follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
More than one?
If you have multi-table tablespaces then you must be at Db2 12 FL508 or higher and create a new set of tablespaces that match *exactly* the current ones for BUFFERPOOL, CCSID and LOGGED attributes. Then you use the ALTER … MOVE TABLE syntax for each table. An actioning REORG followed by a full RUNSTATS on each new tablespace afterwards with SHRLEVEL REFERENCE is then required to get the RTS statistics inline. Now do a REBIND of all invalidated packages and you are done.
Time for the Classics?
If you have any really, really old index-based partitioning tables, you must do two ALTERs within one commit scope. The first flipping the Partitioning Index to NOT CLUSTER and then back to CLUSTER. Now you are at table-based partitioning so read on!
Table-based partitioning
At this point it is an ALTER to SEGSIZE 64 which will kick these babies into the fun world of UTS PBR.
For both Classic cases, follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
Six Byte RBA/LRSN, anyone?
If any of your tablespaces or indexspaces have not got 10 Byte RBA/LRSN then you must REORG them to action this. One little problem here is if the space is an XML space (Type = ‘P’) then you must first check its base table’s tablespace to see if that is a UTS space. If so then all is good, otherwise you have a non-versioning XML space which will require this four-step fix:
1. DSN1COPY to generate image copy for XML data
2. Drop XML column from base table
3. Recreate the XML column in the base table
4. LOAD REPLACE to load the XML data from the image copy generated by step 1.
Invalidated Packages?
Yup! All of these ALTERs and the actioning REORGs with their inline/afterwards RUNSTATS will happily invalidate any and all packages that refer to the objects… This is a major pain as your access paths can then very easily go south! This is time for our tool BindImpactExpert (BIX) to ride to the rescue. As long as you are running with EXPLAIN(YES) – and I sincerely hope you are!!! – BIX can be used to highlight any and all changed access paths enabling you to be proactive with corrective measures.
Definitely evil DEFINE NO
DEFINE NO objects are great as SQL can SELECT from them really fast! They take up next to no disk space and require no REORG or COPY processing as there is no VSAM dataset. The problem with these little devils is that REORG is actually *not* permitted on them! One exception to this rule is if the TS/TP was created with BRF, as then you can REORG with the option ROWFORMAT RRF and this REORG will just flip a bit or two in the catalog/directory and nothing else.
And???
That means that all the ALTERs you might have done are “hanging in the wind”. IBM state that an INSERT/LOAD will materialize the VSAM cluster(s) but who wants to do that? The only way forward is to extract the DDL that created them, DROP them and then reCREATE them all as DEFINE NO again. As when recreated they will be valid for vNext, of course then a REBIND of all invalidated packages is also required and you are done.
Devil in the Details
For BRF partitions the fixing REORG will fail if any table has a validation procedure (VALPROC table column) or edit procedure (EDPROC tabel column) defined. If this is the case then the procedures must first be dropped before the REORG and afterwards added back.
Did they make a HASH of it?
HASH access came in with a big fanfare, we finally had an access path like IMS HDAM, but it died a death very quickly and the fix here is ALTER with DROP ORGANIZATION. Naturally, this drops the hash index and so you must then create a new index for SQL use and – guess what else you must do? Of course, a REORG with inline statistics and a REBIND of all invalidated packages and you are done.
Simply SYNONYMs
I wrote a Blog about all these beasts.
Yes, over ten years ago! The actual fix for every synonym is straightforward:
SET CURRENT SQLID = 'Synonym schema' ;
DROP SYNONYM 'Synonym name' ;
COMMIT ;
CREATE ALIAS 'Synonym schema'.'Synonym name'
FOR 'Table creator'.'Table name' ;
COMMIT ;
The problems here are that the statement SET CURRENT SQLID requires SYSADM Authorization, so this is not something I would farm out to the next student doing work experience!!! Plus, guess what you have to do afterwards??? A REBIND of all invalidated packages and possibly a chain of dependent invalidated objects as well and you are done.
TCP/IP is the Future!
It came as a surprise but there are indeed shops out there who are still using VTAM/SNA for inter-Db2 communication. The problem here is that when it was released in 1974, we all trusted each other! If you were connected to DB2A and just hopped over to DB2B it happily trusted you as you “were already on a DB2A so you must be good guy!” Sadly, those halcyon days are long gone. These days we have “Zero trust” as the norm – a real shame but it is what it is!
Reason for Change?
TCP/IP has enhanced security (AT-TLS and JSON Web Tokens), it can use 64-bit communication buffers and is zIIP eligible. You will save CPU and be much more secure, so going to this is really no question – However, this is a project all on its own – not something for a Friday afternoon at 4 o’clock!
Big Switch?
To switch off VTAM/SNA you should do several things. First clear out any and all old entries in the CDB tables LULIST, LUMODES, MODESELECT and all but the empty LUNAME entry from LUNAMES. Then you should do a BSDS update with DSNJU003 setting the IPNAME for that subsystem to be the real host name. Just doing that switches off VTAM/SNA for that member. If doing all this then also go to SECPORT usage at the same time as then your Db2s are truly secure! Your auditors will love you.
IBM Details
Are here.
Let’s get it sorted!
Yes, even all the sort workspaces must be cleaned-up and moved to PBG spaces. You really need a lot of 32k, a few 4k, and you need to know exactly how many FOR SORT and how many FOR DGTT (The new syntax in Db2 13 FL508) you require and need. No more guessing at which workload is going into which work tablespace.
- FOR SORT specifies that the table space is used for processing other than declared global temporary table (DGTT) work, such as sort, joins, created global temporary tables, query parallelism, trigger transition tables, and so forth.
- FOR DGTT specifies that the table space is used for DGTT work and processes that use internal temporary tables, such as scrollable cursors and INSTEAD OF triggers.
Remember that here no ALTER helps – you must DROP and CREATE the work spaces again… But hey! At least no REORG, RUNSTATS and REBINDs are required!
<PHEW> That’s a Ton of Stuff to do…
Wouldn’t it be cool if there was a nice small bit of freeware out there that listed out all the stuff you have to do to actually get ready for Db2 vNext! In fact, you cannot even get to Db2 13 FL511 as that will stop you with any of the above-mentioned items!
What Luck – SEGUS to the Rescue!
Yes indeed, we have a bit of freeware called MigrationReadiness HealthCheck for Db2 z/OS (MRHC) that runs through your Db2 subsystems and shows you all the KPIs you have as well as highlighting all the Migration Blockers, as I call them. There is also a pay-ware version that then actually creates all of the ALTERs, REORGS, RUNSTATS and REBINDs for you as well.
How does it look?
It looks cool, of course! Here are a few example screen grabs of how it looks in my little Db2 13 data-sharing test system and what it offers:
Db2 MigrationReadiness HealthCheck V2.1 for SD1 V13R1M509
started at 2026-05-28-11.08.24
Lines with *** are deprecated features
Lines with MMM are migration blockers
Lines with XXX are definition errors
Number of DATABASES : 225
# of empty DATABASES : 48
# of implicit DATABASES : 110
# of empty implicit DBs : 46
Number of TABLESPACES : 4995
of which HASH organized : 0
of which PARTITION CLASSIC : 0
# Partitions : 0
of which SEGMENTED : 27 MMM
of which SIMPLE : 3 MMM
of which LOB : 99
of which UTS PBG : 4833
# Partitions : 4834
of which UTS PBR (Absolute): 1
# Partitions : 6
of which UTS PBR (Relative): 10
# Partitions : 2215
of which XML : 22
Number of TSs as LARGE : 0
Number of empty tablespaces : 7
Number of multi-table TSs : 12 MMM
# of tables within these : 49
.
.
Number of table partitions : 7211
of which DEFINE NO : 2918
of which 6 byte RBA <11 NFM: 0
of which 6 byte RBA Basic : 0
of which ten byte RBA : 4293
Number of TP in BRF : 17 MMM
.
.
LULIST entries found : 0
LUMODES entries found : 1 MMM
LUNAMES entries found : 2 MMM
MODESELECT entries found : 1 MMM
.
.
Total number of REORGs 21
REORGing 176 Cylinders
also requiring 1409 REBINDs
Naturally, most of this is the Db2 Catalog and Directory. At the end it outputs a small list of KPIs with the Total number of REORGs required, the total numbers of Cylinders of space all the table and indexspaces take and how many REBINDs should then be done afterwards. This gives the experienced DBA a very good idea of how long and how potentially dangerous this whole thing could be!
Just the Facts, Ma’am!
Under DD card ALTERCAT in the pay-ware version are all the required actions, note that you must be at Db2 13 FL509 or above to get the CONVERTUTS REORG syntax:
-- REORG TABLESPACE DSNDB01.SCT02 SHRLEVEL CHANGE
-- CONVERTUTS
-- REORG TABLESPACE DSNDB01.SYSUTILX SHRLEVEL CHANGE
-- CONVERTUTS
-- REORG TABLESPACE DSNDB06.SYSALTER SHRLEVEL CHANGE
-- CONVERTUTS
-- REORG TABLESPACE DSNDB06.SYSCONTX SHRLEVEL CHANGE
-- CONVERTUTS
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC.(2026-01-21-06.55.23.099777)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2019-03-22-09.20.33.799998)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2022-08-01-06.08.43.796175)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVCRC.(2018-08-21-07.21.16.927191)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVDRC.(2022-08-01-06.21.52.371116)) APREUSE(WARN)
Notice that it always generates APREUSE(WARN) to try and keep the old access path.
Also, here are any of the nasty DEFINE NO REORG problems:
-- DEFINE NO TABLESPACE TS5941.TSTSDNQ REORG NOT POSSIBLEand the SQL for CDB Clean-up:
-- DELETE FROM SYSIBM.LUMODES
-- ;
-- COMMIT ;
-- DELETE FROM SYSIBM.LUNAMES
-- WHERE NOT LUNAME = ' '
-- ;
-- COMMIT ;
-- DELETE FROM SYSIBM.MODESELECT
-- ;
-- COMMIT ;
Under DD card MIGRAREP are all the Migration Blockers listed out in detail:
Simple DB: DSNDB01 TS: SCT02
Segmented DB: DSNDB01 TS: SYSUTILX
Segmented DB: DSNDB06 TS: SYSALTER
Segmented DB: DSNDB06 TS: SYSCONTX
Segmented DB: DSNDB06 TS: SYSDDF
Segmented DB: DSNDB06 TS: SYSEBCDC
Simple DB: DSNDB06 TS: SYSGPAUT
Segmented DB: DSNDB06 TS: SYSGRTNS
Segmented DB: DSNDB06 TS: SYSHIST
Segmented DB: DSNDB06 TS: SYSJAVA
Segmented DB: DSNDB06 TS: SYSROLES
Segmented DB: DSNDB06 TS: SYSSEQ
Segmented DB: DSNDB06 TS: SYSSEQ2
Segmented DB: DSNDB06 TS: SYSSTATS
Segmented DB: DSNDB06 TS: SYSTARG
Segmented DB: DSNDB06 TS: SYSTSASC
Segmented DB: DSNDB06 TS: SYSTSUNI
Segmented DB: DSNDB06 TS: SYSTSXTM
Segmented DB: DSNDB06 TS: SYSTSXTS
Simple DB: DSNDB06 TS: SYSUSER
Segmented DB: DSNDB06 TS: SYSXML
Segmented DB: TS5941 TS: TSTSDNQ
Segmented DB: WRKSD10 TS: DSN32K00
Segmented DB: WRKSD10 TS: DSN32K01
Segmented DB: WRKSD10 TS: DSN4K00
Segmented DB: WRKSD10 TS: DSN4K01
Segmented DB: WRKSD11 TS: DSN32K00
Segmented DB: WRKSD11 TS: DSN32K01
Segmented DB: WRKSD11 TS: DSN4K00
Segmented DB: WRKSD11 TS: DSN4K01
BRF tablespace DB: DSNDB01 TS: SCT02
BRF tablespace DB: DSNDB01 TS: SYSUTILX
BRF tablespace DB: DSNDB06 TS: SYSALTER
BRF tablespace DB: DSNDB06 TS: SYSCONTX
BRF tablespace DB: DSNDB06 TS: SYSDDF
BRF tablespace DB: DSNDB06 TS: SYSEBCDC
BRF tablespace DB: DSNDB06 TS: SYSGPAUT
BRF tablespace DB: DSNDB06 TS: SYSGRTNS
BRF tablespace DB: DSNDB06 TS: SYSHIST
BRF tablespace DB: DSNDB06 TS: SYSJAVA
BRF tablespace DB: DSNDB06 TS: SYSROLES
BRF tablespace DB: DSNDB06 TS: SYSSEQ
BRF tablespace DB: DSNDB06 TS: SYSSEQ2
BRF tablespace DB: DSNDB06 TS: SYSSTATS
BRF tablespace DB: DSNDB06 TS: SYSTARG
BRF tablespace DB: DSNDB06 TS: SYSUSER
BRF tablespace DB: DSNDB06 TS: SYSXML
CDB table LUMODES has row - LUNAME DKLTEST MODENAME MODTEST.
CDB table LUNAMES has row - LUNAME DKLTEST SYSMODENAME MODTEST.
CDB table LUNAMES has row - LUNAME ROYTEST SYSMODENAME -none-.
CDB table MODESELECT has row - LUNAME DKLTEST AUTHID DKLTEST_AUTH_ID PLANNAME DKLTEST.Note the WRKxxxx tablespaces…
Start to plan today!
Using our MigrationReadiness HealthCheck for Db2 z/OS freeware you can start to divvy up and plan all of the work that will be coming down the vNext road towards you. Start now and you still have over two years to get it all done and actioned. Start in two years and you will never make it – The choice is yours!
TTFN,
Roy Boxwell
