Blog
DB2utor

Categories

April 22, 2008

Planning for Frozen Objects in DB2 9 for z/OS

The word "fallback" can bring fear and stress to DB2 systems programmers who haven't taken the time to plan the steps needed to return to DB2 Version 8 after migrating the catalog and directory to DB2 V9 compatibility mode. It's rare, but if you experience a severe application or performance error in DB2 V9 and need to fallback to DB2 V8, then follow the step-by-step instructions found in the DB2 V9 for z/OS Installation Guide.

Falling back to DB2 V8 doesn't undo changes made to the catalog during the migration phase to DB2 V9 compatibility mode. The migrated catalog will be used after you fallback. Objects in the catalog that have a dependency on new DB2 V9 features will be frozen. Frozen objects are unavailable and can be identified by looking at the IBMREQD column in the catalog table with a value of "M."

Plans or packages become frozen objects when they use new SQL syntax or new bind options, or have a dependency on a frozen object. Depending on your installation parameters, the package will go through automatic rebind when executed under DB2 V8. If it fails, you may need to change the SQL statement or reference to a frozen object, precompile and explicitly bind the package.

To find a frozen object, run this SQL statement:

SELECT * FROM catalog table
WHERE IBMREQD = ‘M’;

To automatically generate this statement for every catalog table that contains the IBMREQD column, run this SQL statement:

SELECT STRIP(SUBSTR(T.CREATOR,1,8))         
   CONCAT '.' CONCAT SUBSTR(T.NAME,1,18)   
   CONCAT '01'                              
  ,'SELECT * FROM '                        
    CONCAT STRIP(SUBSTR(T.CREATOR,1,8))    
    CONCAT '.' CONCAT SUBSTR(T.NAME,1,18)   
FROM SYSIBM.SYSTABLES T                     
WHERE T.CREATOR = 'SYSIBM'                  
  AND T.TYPE = 'T'                         
  AND EXISTS (SELECT 1                     
              FROM SYSIBM.SYSCOLUMNS C      
              WHERE C.TBCREATOR = 'SYSIBM' 
                AND C.TBCREATOR = T.CREATOR
                AND C.TBNAME = T.NAME      
                AND C.NAME = 'IBMREQD'      
             )                              
UNION ALL                                  
SELECT STRIP(SUBSTR(T.CREATOR,1,8))         
   CONCAT '.' CONCAT SUBSTR(T.NAME,1,18)   
   CONCAT '02'                              
   ,'WHERE IBMREQD =''M'' WITH UR ;'       
FROM SYSIBM.SYSTABLES T                     
WHERE T.CREATOR = 'SYSIBM'                  
  AND T.TYPE = 'T'                         
  AND EXISTS (SELECT 1                     
              FROM SYSIBM.SYSCOLUMNS C      
              WHERE C.TBCREATOR = 'SYSIBM' 
                AND C.TBCREATOR = T.CREATOR
                AND C.TBNAME = T.NAME      
                AND C.NAME = 'IBMREQD'      
             )                              
ORDER BY 1,2                               
WITH UR ;

The output from the above query will look like this. (You'll need to shift everything left 27 characters to remove the object name and sequence number used to sort the result set.  Notice the block ((27 line command.)

((27   SYSIBM.IPLIST                01    SELECT * FROM SYSIBM.IPLIST    
000039 SYSIBM.IPLIST              02    WHERE IBMREQD ='M' WITH UR ;   
000040 SYSIBM.IPNAMES          01    SELECT * FROM SYSIBM.IPNAMES   
000041 SYSIBM.IPNAMES          02    WHERE IBMREQD ='M' WITH UR ;   
000042 SYSIBM.LOCATIONS      01    SELECT * FROM SYSIBM.LOCATIONS 
((0043 SYSIBM.LOCATIONS       02    WHERE IBMREQD ='M' WITH UR ;   

Use the output of this generated SQL as input into SPUFI to get a list of objects in each table that contains a DB2 V9 release dependency. Here's a sample of what you'll see (after shifting left 27 characters):

000001     SELECT * FROM SYSIBM.IPLIST   
000002     WHERE IBMREQD ='M' WITH UR ;   
000003     SELECT * FROM SYSIBM.IPNAMES   
000004     WHERE IBMREQD ='M' WITH UR ;   
000005     SELECT * FROM SYSIBM.LOCATIONS
000006     WHERE IBMREQD ='M' WITH UR ;

Sample output: Since location is a 128-character field that's usually blank, it doesn't look like much. Since you now know that syspackage has objects in it that are DB2 V9 release-dependent, you can go back and select the names of the packages. In my catalog I have 63 packages that are marked as IBMREQD with a value of "M" for DB2 V9 dependency.

    SELECT * FROM SYSIBM.SYSPACKAGE
    WHERE IBMREQD ='M' WITH UR ;   
---------+---------+---------+------
LOCATION                           
---------+---------+---------+------
DSNE610I NUMBER OF ROWS DISPLAYED IS 63

In my catalog I had these tables and object counts:
                                  
Object Count     Table
==========       ===================
63               SYSIBM.SYSPACKAGE
01               SYSIBM.SYSPLAN
26               SYSIBM.SYSTABLES
09               SYSIBM.SYSTABLESPACE
06               SYSIBM.SYSVIEWS
53               SYSIBM.SYSVLTREE
08               SYSIBM.SYSVTREE

This list tells me that I have 63 packages that won't work when I fallback. I also know that I’m taking advantage of DB2 V9 features in 26 tables and nine table spaces. Before I do fallback, I may want to look into the nature of these objects and figure out how critical they are and what options I have to getting the 63 programs up and running under DB2 V8.