Start of change

Upgrade DB2 tables

You upgrade DB2 tables when you upgrade your CICS® system or change the template in the HDB, and you want to retain data in the new table format.

Before you begin

If the table that is to be upgraded contains performance data, CICS PA will use the template specified in the HDB to create the new version of the table. Therefore you need to ensure that the current template name in the HDB definition is correct and that this template contains all of the fields that are required in the new table.

If the table that is to be upgraded contains statistics data, CICS PA needs to know which version of CICS TS or CICS TG the existing data in the table was based on. You can specify these "old" VRMs on the Export HDB Data Set panel. CICS PA will upgrade the data in the table to the current version of CICS TS or CICS TG, which it obtains from the CICS versions (VRM) fields on the Historical Database Menu.

About this task

New releases of CICS might include the addition or deletion of CICS TS and CICS TG Performance (CMF) and Statistics fields. As you migrate to a new release of CICS TS or CICS TG, you might want to redefine the DB2® tables that are loaded by CICS PA to make them compatible with the new CICS version. You might also upgrade a DB2 table if you have modified the custom HDB template that was used to export the data to DB2 and you want the table to reflect the new template definition.

This topic describes how to upgrade a DB2 table using the Export HDBs dialog. Alternatively you can use the T line action in the HDB Maintenance dialog.

Only perform this task if you want to migrate the data in the old version of the table to the new format. If you don't want to retain the old data, drop the table and then redefine it using the Export HDBs or HDB Maintenance function.

If you want to keep an existing table containing obsolete fields, instead of upgrading the table, create a new table using a template that is based on the new release.

For details of the Upgrade settings fields, see "Data Set Allocation Settings" in Define a Performance HDB. The DB2 settings fields are described in DB2 settings.

Attention: Upgrading DB2 tables might result in the deletion of data in columns that are obsolete or incompatible in the new release. To see if a table contains fields that might be affected, refer to the following information.

Procedure

  1. From the Export HDBs dialog, first select an HDB that you want to upgrade.
  2. Select a container data set from the list.
  3. Statistics HDB only: Enter line action S to select the reports that you want to upgrade.
  4. From the Export HDB Data Set dialog, select option 3.
  5. Enter the Upgrade settings. These are used to specify the attributes of the data set into which existing data will be unloaded.

    If you are upgrading statistics tables, you must provide the "old" VRM. This is the CICS TS or CICS TG release that was used to define the DB2 table for this HDB data set.

  6. Press Enter. An attention message notes that upgrading the tables might result in the deletion of data in columns that are obsolete or incompatible in the new release.
  7. Press Enter again to review the generated JCL.
    Figure 1. Edit JCL for HDB Export: Upgrade DB2 table
    EDIT       SXY.SPFTEMP1.CNTL                               Columns 00001 00072 
    Command ===> ________________________________________________ Scroll ===> CSR_ 
    ****** ***************************** Top of Data ******************************
    //CICSPA  JOB ,NOTIFY=&SYSUID                                           
    //* CICSPA V5R3 HDB - Ease DB2 Table Migration                          
    //* Delete Old UNLOAD and SYSPUNCH Dataset                              
    //DELETE   EXEC PGM=IDCAMS                                              
    //SYSPRINT DD  SYSOUT=*                                                 
    //SYSIN    DD  *                                                        
     DELETE CPA530.EXAMPLE.XXLST.REC                                        
     DELETE CPA530.EXAMPLE.XXLST.PUN                                        
     SET MAXCC=0                                                            
    /*                                                                      
    //* This step UNLOAD the DB2 table and creates dataset for RELOAD       
    //UNLOAD   EXEC PGM=DSNUTILB,REGION=0M,PARM='DB2P'                      
    //STEPLIB  DD DISP=SHR,DSN=DB2.PROD.SDSNLOAD                            
    //         DD DISP=SHR,DSN=DB2.PROD.SDSNEXIT                            
    //SYSPRINT DD  SYSOUT=*                                                 
    //UTPRINT  DD  SYSOUT=*                                                 
    //SYSUDUMP DD  SYSOUT=*                                                 
    //SYSPUNCH DD DSN=CPA530.EXAMPLE.XXLST.PUN,                             
    //            SPACE=(TRK,(5,5)),                                        
    //            DISP=(NEW,CATLG,CATLG)                                    
    //SYSREC   DD DSN=CPA530.EXAMPLE.XXLST.REC,                             
    //            SPACE=(CYL,(1,1)),                                        
    //            DISP=(NEW,CATLG,CATLG)                                    
    //SYSIN    DD *                                                         
     UNLOAD TABLESPACE CPAX.NJB                                             
       FROM TABLE NJB.CPA_CMFPLST                                           
    /*                                                                      
    //S1      EXEC PGM=IRXJCL,                                              
    //             PARM='CPADB2UP',COND=(8,LE)                              
    //SYSEXEC  DD  DISP=SHR,DSN=CPA530.DEVT.SCPAEXEC                        
    //SYSPRINT  DD SYSOUT=*                                                 
    //SYSTSPRT  DD SYSOUT=*                                                 
    //SYSPUNCH  DD DISP=SHR,DSN=CPA530.EXAMPLE.XXLST.PUN                    
    /*                                                                      
    //* Drop the old table then create the new one                          
    //RUNTIAD EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(8,LE)                     
    //STEPLIB  DD DISP=SHR,DSN=DB2.PROD.SDSNLOAD                            
    //         DD DISP=SHR,DSN=DB2.PROD.SDSNEXIT                            
    //SYSTSPRT DD SYSOUT=*                                                  
    //SYSTSIN  DD *                                                         
     DSN SYSTEM(DB2P)                                                       
     RUN PROGRAM(DSNTIAD) -                                                 
         LIB('DB2.PROD.RUNLIB.LOAD') PLAN(DSNTIAD)                          
    /*                                                                      
    //SYSPRINT DD SYSOUT=*                                                  
    //SYSUDUMP DD SYSOUT=*                                                  
    //SYSIN    DD *                                                         
       DROP TABLE NJB.CPA_CMFPLST;                                          
       COMMIT;                                                              
                                                                            
       CREATE TABLE NJB.CPA_CMFPLST (                                       
          START                TIMESTAMP,                                   
    ⋮                                                           
    (table and index definition)                                            
    ⋮                                                           
    /*                                                                      
    //* Reload to new table                                                 
    //RELOAD   EXEC PGM=DSNUTILB,REGION=0M,                                 
    //         PARM='DB2P',COND=(8,LE)                                      
    //STEPLIB  DD DISP=SHR,DSN=DB2.PROD.SDSNLOAD                            
    //         DD DISP=SHR,DSN=DB2.PROD.SDSNEXIT                            
    //SYSPRINT DD  SYSOUT=*                                                 
    //UTPRINT  DD  SYSOUT=*                                                 
    //SYSUDUMP DD  SYSOUT=*                                                 
    //* Dataset created by UNLOAD and contains table records                
    //SYSREC   DD  DISP=SHR,DSN=CPA530.EXAMPLE.XXLST.REC                     
    //SYSUT1   DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                  
    //SORTOUT  DD  UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                  
    //* Dataset created by UNLOAD and contains LOAD command input           
    //SYSIN    DD  DISP=SHR,DSN=CPA530.EXAMPLE.XXLST.PUN                     
  8. Submit the JCL to upgrade the DB2 table. Review the job output in SDSF to verify that the table was created successfully.
End of change