Skip to Content

Adding columns to the Db2 PLAN_TABLE table by importing DML

Use the following procedure to import DML to upgrade Db2 PLAN_TABLE tables with columns that were added by a later version of Db2.

Warning

Important

You can also perform this task by using the CM/PILOT DML_STRUCTURE_CHG script. For more information, see Changing-data-structures-by-using-DML.

Before you begin

Create a temporary table, as shown in the following figure that contains the new columns that were added by subsequent versions of Db2 for the PLAN_TABLE table, using Change Manager or SPUFI.

CREATE DATABASE DEMPLAN;

CREATE TABLESPACE TS1 IN DEMPLAN;

CREATE TABLE DEM.TESTPLAN
  (WHEN_OPTIMIZE     CHAR(1) NOT NULL,              
   QBLOCK_TYPE       CHAR(6) NOT NULL,              
   BIND_TIME         TIMESTAMP NOT NULL,            
   OPTHINT           CHAR(8) NOT NULL WITH DEFAULT,
   HINT_USED         CHAR(8) NOT NULL WITH DEFAULT,
   PRIMARY_ACCESS    CHAR(1) NOT NULL WITH DEFAULT)
IN DEMPLAN.TS1
;

To create and import the DML statement

  1. In an ISPF editor, type the DML statement to match the one shown in the following figure and provide the information that is unique to your site.For information about DML syntax, see CM-PILOT-Data-Manipulation-Language-DML.

    Success

    Tip

    You can copy the DML statement from member ACMDMLU6 in the HLQ.BMCCNTL data set.

         -- EXAMPLE DML TO ADD PLAN TABLE COLUMNS
    LIKE   COLUMNS
    JOIN SYSIBM.SYSCOLUMNS DEM
      SET  NAME     = DEM.NAME,
           COLNO    = 200 + DEM.COLNO,
           COLTYPE  = DEM.COLTYPE,
           LENGTH   = DEM.LENGTH,
           NULLS    = DEM.NULLS,
           DEFAULT  = DEM.DEFAULT
    WHERE
           SYSIBM.SYSCOLUMNS.TBNAME    =  'PLAN_TABLE'

         --EXCLUDE THE SYSTEM PLAN TABLE FOR DB2
      AND  SYSIBM.SYSCOLUMNS.TBCREATOR <> 'SYSIBM'
      AND  SYSIBM.SYSCOLUMNS.COLNO     =  1

         --CHANGE TO WHATEVER YOU CALLED THE TEMP TABLE ABOVE
      AND  DEM.TBNAME                  =  'PLAN_TABLE'

         --CHANGE TO WHATEVER YOU USED TO CREATE TEMP TABLE
      AND  DEM.TBCREATOR               =  'DEM'

         --FIND PLAN TABLES THAT DO NOT CONTAIN NEW COLUMNS
      AND  NOT EXISTS
           (SELECT * FROM SYSIBM.SYSCOLUMNS X
            WHERE
                  X.TBNAME    = SYSIBM.SYSCOLUMNS.TBNAME
            AND   X.TBCREATOR = SYSIBM.SYSCOLUMNS.TBCREATOR
            AND   X.NAME      = DEM.NAME)
    ;

  2. Import the DML file.Now that the DML file has been imported, you can analyze a work ID and execute a worklist.

    Warning

    Important

    The worklist for this example should contain the following types of commands:

    • Data unload commands for Db2 PLAN_TABLE tables
    • Drop commands for the Db2 PLAN_TABLE tables
    • Alter table commands for the Db2 PLAN_TABLE tables that add the new NOT NULL WITH DEFAULT columns
    • Create table commands for the Db2 PLAN_TABLE tables that add the new NOT NULL columns, as well as the three new NOT NULL WITH DEFAULT columns
    • Load commands to load the data that was previously unloaded
    • Statistics and image copy commands
    • Rebind commands


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

ALTER and BMC AMI Change Manager for Db2 13.1