Db2 13 Readiness Report

Posted By: Emil Kotrc

Before you migrate to any new Db2 release such as Db2 13 you usually do a migration readiness planning. Part of this planning is running the pre-migrations job that IBM ships with the prior version (DSNTIJPE in Db2 12) as well as with the latest version (DSNTIJPM in Db2 13). Normally you run these checks on the prior version - in this case on Db2 12 - to make sure the migration to a new release runs smoothly.

Pre-migration job

The pre-migration job (DSNTIJPM) contains 21 reports, which are basically SQL queries to Db2 catalog. There are specific actions if you hit any of these. The summary follows:

Report

Description

1

Previous-release sample database

2

Simple table spaces

3

Explain tables that are not in the current-release format

4

User defined indexes on the Db2 catalog that do not reside in the Db2 catalog space

5

Package copies that are not supported by Db2 13

6

Packages that are autobind candidates in Db2 13

7

Plans that are autobind candidates in Db2 13

8

Packages to be rebound prior to migrate to release 13 or V12R1M510 activation

9

Problem with catalog table space version numbers

10

Inconsistent version numbers in Db2 catalog

11

Db2 system page discrepancies for SYSTABLES

12

Orphaned rows in SYSCOPY and SYSOBDS

13

Orphaned rows in SYSTABSTATS

14

Orphaned rows in SYSCOLAUTH

15

Extraneous text in SYSIBM.SYSTRIGGERS.TEXT

16

Unicode columns in EBCDIC tables before V12

17

Indexes on Unicode columns in EBCDIC tables

18

Obsolete RLSTs

19

Native SQL procedures and compiled SQL scalar functions created in V9

20

Tables that have an OBID of 1

21

Foreign key constraints that have a Db2 catalog table as a parent


I would particularly point out report 8 – a SQL that Db2 runs at the time of activation of V12R1M510, which is an entry point to Db2 13. You cannot go to Db2 13 without activating V12R1M510. The SQL that is being executed is:

SELECT *

FROM SYSIBM.SYSPACKAGE

WHERE LASTUSED >= DATE(DAYS(CURRENT DATE) - 548)

AND RELBOUND NOT IN ('P','Q',’R’)

AND VALID <> ‘N’

AND OPERATIVE <> ‘N’;

 

This basically shows packages that were bound before Db2 11, are valid, and have been used within last 18 months. If the query returns any rows, you need to rebind these packages first. We wrote a blog about this with Julia.

The only thing you need to be careful here is the setting of the DISABLE_EDMRTS zParm, which may turn off the collection of LASTUSED column statistics in SYSPACKAGE catalog table. Note, that this is the column used in the prior query.

So far it seems that once you executed the pre-migration queries, activated the V12R1M510 function level, and you completed all other pre-migration tasks, you are ready to go to Db2 13. Are you? Well there is one more thing.

One more thing

The one more thing is Db2 12 fallback SPE. In other words that is the PTF you must apply to your Db2 12, even on a standalone system, that guarantees that you may fallback to 12 during the 13 migration. Thanks to the catalog level changes in V13, this fallback PTF is really small, but you still need to have it installed. How do you know? Well, SMP/E query may help, but is not always possible as you may be running from a deployment library. So, perhaps DIAGNOSE MEPL? That is a bit tricky. Or a new great addition to -DISPLAY GROUP DETAIL command that shows whether you are ready to migrate - PH50072. It shows the eligibility to migrate as well as other conditions. This idea came around an AHA idea to simply make sure you have the fallback PTF installed, but it evolved into a full readiness report of the -DIS GROUP DETAIL.

Let's see some examples.

First example shows a standalone system that is not ready for migration, because the function level is not M510 (see the reason):

******************************** TOP OF DATA  *******************************

DSN7100I  !DSNA DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V12R1M500)
CURRENT FUNCTION LEVEL(V12R1M500)
HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M500)
HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)
PROTOCOL LEVEL(2)
 GROUP ATTACH NAME(....)
---------------------------------------------------------------------
DB2  SUB  DB2 SYSTEM  IRLM
MEMBER ID  SYS  CMDPREF  STATUS  LVL  NAME  SUBSYS  IRLMPROC 
-------- --- ---- -------- -------- ------ -------- ---- --------
........ 0 DSNA !DSNA ACTIVE 121510 SYS1 ISNA DSNAIRLM
--------------------------------------------------------------------- 
MIGRATION READINESS REPORT          
--------------------------            
DB2 CODE SPE MIGRATION          
MEMBER LEVEL APR ELIGIBLE          
-------- --------- ------- ---          
........   V12R1M510 PH37108 YES          
MIGRATION READINESS STATUS: SUBSYSTEM IS NOT READY FOR DB2 13  
REASON: HIGHEST ACTIVATED FUNCTION LEVEL NOT V12R1M510
---------------------------------------------------------------------
SPT01 INLINE LENGTH:        32138
*** END DISPLAY OF GROUP(........)
DSN9022I  !DSNA DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION 
******************************** BOTTOM OF DATA  *******************************
 

However, it shows that the migration is eligible, because the fallback APAR PH37108 is already applied.

Same example, but with a datasharing group, showing that all members are running with a maintenance including the fallback SPE.

******************************** TOP OF DATA  *******************************

DSN7100I  !DSN1 DSN7GCMD
*** BEGIN DISPLAY OF GROUP(DSNGP   ) CATALOG LEVEL(V12R1M500)
CURRENT FUNCTION LEVEL(V12R1M500)
HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M500)
HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)
PROTOCOL LEVEL(2)
GROUP ATTACH NAME(DSNG)
---------------------------------------------------------------------
DB2  SUB  DB2 SYSTEM  IRLM
MEMBER ID  SYS  CMDPREF  STATUS  LVL  NAME  SUBSYS  IRLMPROC 
-------- --- ---- -------- -------- ------ -------- ---- --------
DSN1 1 DSN1 !DSN1 ACTIVE 121510 SYS1 ISN1 DSN1IRLM
DSN2 2 DSN2 !DSN2 ACTIVE 121510 SYS2 ISN2 DSN2IRLM
DSN3 3 DSN3 !DSN3 ACTIVE 121510 SYS3 ISN3 DSN3IRLM
--------------------------------------------------------------------- 
MIGRATION READINESS REPORT          
--------------------------            
DB2 CODE SPE MIGRATION          
MEMBER LEVEL APR ELIGIBLE          
-------- --------- ------- ---          
DSN1 V12R1M510 PH37108 YES          
DSN2 V12R1M510 PH37108 YES          
DSN3 V12R1M510 PH37108 YES          
MIGRATION READINESS STATUS: GROUP IS NOT READY FOR DB2 13
REASON: HIGHEST ACTIVATED FUNCTION LEVEL NOT V12R1M510
---------------------------------------------------------------------
DISPLAY SUBGROUP ATTACH INFORMATION FOR GROUP ATTACH DSNG
---------------------------------------------------------------------
---------------------------------------------------------------------
SCA STRUCTURE SIZE: 17408 KB, STATUS= AC, SCA IN USE= 67%
LOCK1 STRUCTURE SIZE: 46080 KB
NUMBER LOCK ENTRIES: 8388608
NUMBER LIST ENTRIES: 86269, LIST ENTRIES IN USE: 314
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(DSNG    )
DSN9022I  !DSN1 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
************************** BOTTOM OF DATA  *************************

On the other hand, the next example shows a subsystem that is really ready to go - function level is M510 and fallback PTF is installed:

DSN7100I   !DSN1  DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V12R1M509) 
CURRENT FUNCTION LEVEL(V12R1M510)
HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M510)
HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M510)
PROTOCOL LEVEL(2)
GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2  SUB  DB2 SYSTEM  IRLM
MEMBER ID  SYS  CMDPREF  STATUS  LVL  NAME  SUBSYS  IRLMPROC 
-------- --- ---- -------- -------- ------ -------- ---- --------
........ 0 DSN1 !DSN1 ACTIVE 121510 SYS1 ISN1 DSN1IRLM
--------------------------------------------------------------------
MIGRATION READINESS REPORT          
--------------------------            
DB2 CODE SPE MIGRATION          
MEMBER LEVEL APR ELIGIBLE          
-------- --------- ------- ---          
........ V12R1M510 PH37108 YES          
MIGRATION READINESS STATUS: SUBSYSTEM IS READY FOR DB2 13
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138          
*** END DISPLAY OF GROUP(........) 
DSN9022I  !DSN1 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION

 

Now, you may want to ask. What if I run this report on a Db2 13 system? In that case, there is no future release yet and the report just reports that

 

******************************** TOP OF DATA  *******************************
DSN7100I  !DSN1  DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V13R1M501)  
CURRENT FUNCTION LEVEL(V13R1M501)
HIGHEST ACTIVATED FUNCTION LEVEL(V13R1M501)
HIGHEST POSSIBLE FUNCTION LEVEL(V13R1M503)
PROTOCOL LEVEL(2)
GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2  SUB  DB2 SYSTEM  IRLM
MEMBER ID  SYS  CMDPREF  STATUS  LVL  NAME  SUBSYS  IRLMPROC 
-------- --- ---- -------- -------- ------ -------- ---- --------
........ 0 DSN1 !DSN1 ACTIVE 131503 SYS1 ISN1 DSN1IRLM
--------------------------------------------------------------------
MIGRATION READINESS REPORT          
--------------------------            
DB2 CODE SPE MIGRATION          
MEMBER LEVEL APR ELIGIBLE          
-------- --------- ------- ---          
........ V13R1M503 NO          
MIGRATION READINESS STATUS: SUBSYSTEM IS NOT READY FOR DB2 NEXT
REASON: NO NEXT RELEASE OF DB2 IS CURRENTLY AVAILABLE 
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138          
*** END DISPLAY OF GROUP(........) 
DSN9022I  !DSN1 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
************************** BOTTOM OF DATA  *************************

Conclusion

Migration to a new Db2 release is always a challenging task. Hopefully, the new Db2 13 migration readiness report will make the planning easier.


Author: Emil Kotrc

Created: 2024-02-02 Fri 09:19