PLAN TABLE IN Db2 V11 FORMAT AFTER UPGRADE to Db2 V12
search cancel

PLAN TABLE IN Db2 V11 FORMAT AFTER UPGRADE to Db2 V12

book

Article ID: 138419

calendar_today

Updated On:

Products

SQL-Ease for DB2 for z/OS Plan Analyzer for DB2 for z/OS

Issue/Introduction

PPADB2 automatically generates  plan table in a DB2V12 environment but it creates it as a DB2 V11 format and the following warning message is received . 

The following explain control cards have been overridden with these values:    

EXPLTYPE  = (CURRENT)                                                                                                                                        

-- TTDF.PLAN_TABLE table was auto-created and format is V11.                   

-- TTDF.PLAN_TABLE_HINT_IX index was auto-created.                             

                                                                               

PPE0011W: The explain detected unsupported explain table formats. The          

current target DB2 release mandates that all explain tables use the            

DB2 12 format.  DB2 explain continues to populate tables that use unsupported  

formats, but with a warning SQLCODE +20520, which our processing has           

suppressed.                                                               

          SQL error in PLAN R200PPAM in DBRM PPA@EXPC statement     895        

          SSID: DOD1   DB2 VERSION: V12R1M0                                    

 DSNT404I SQLCODE = 20520, WARNING:  ATTEMPT TO USE DEPRECATED FEATURE         

          TTDF.PLAN_TABLE. REASON CODE: 2                                      

 DSNT418I SQLSTATE   = 01694 SQLSTATE RETURN CODE                              

 DSNT415I SQLERRP    = DSNXOD0Z SQL PROCEDURE DETECTING ERROR                  

 DSNT416I SQLERRD    = 0  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            

 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'                   

          X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC                 

          INFORMATION                       

                                       

Environment

Release : 20.0

Component : Plan Analyzer for DB2 for z/OS

Resolution

After upgrading Db2  did in the CDBAPARM(SETUP00) member dataset needs to get updated to reflect the correct Db2 12 function and catalog release level for SSID DOD1? PPADB2 uses these fields for Db2 release level checking in the products, so it is important these fields correctly reflect the current Db2 release level for the subsystem.

As an example, here is a snapshot of one of our Db2 12 subsystems XXXX where the release level fields SSIDVERF (Db2 function level) and SSIDVERC (Db2 catalog level) are correctly assigned.



SSID (XXXX)

SSIDDESC (DB2 11 GOING TO 12)

SSIDVERF (V12R1M500)

SSIDVERC (V12R1M500)

SSBP4KIX (BP2)

SSBP4KTS (BP1) This is documented in the R20 Database Management Solutions for DB2 for Z/OS 20 under the sub heading to Upgrade to New Db2 releases and Function Levels using the link  below:

UPGRADE TO NEW DB2