PI87960: OBJECT COMPARE AND CM BATCH GENERATE DROP/CREATE TABLE STATEMENTS WHEN COLUMNS ARE ADDED OR DROPPED

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• This APAR addresses three issues:
  1.  When a user changes columns from NULL to NOT NULL, or
  vice-versa, Compare and CM Batch processing generate
  DROP/CREATE TABLE statements.  With this fix, DROP/CREATE COLUMN
  statements will be generated instead of DROP/CREATE TABLE.
  2. When a user drops a column from a table and an index,
  Compare and CM Batch process the table first, and generate
  DROP/CREATE TABLE statements. With this fix, DROP/CREATE INDEX
  statements will be generated first instead, followed by an
  ALTER TABLE DROP COLUMN statement.
  3.  When a user drops multiple existing columns and appends
  multiple new columns to a table, Compare and CM Batch processing
  generate DROP/CREATE TABLE statements.  With this fix,
  ALTER TABLE DROP COLUMN and ALTER TABLE ADD COLUMN will be
  generated instead.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Administration Tool for z/OS    *
  *                 and DB2 Object Comparison Tool for z/OS who  *
  *                 want to make changes to Table columns using  *
  *                 a DB2 ALTER TABLE statement instead of       *
  *                 drop/creating the Table OR uses the ADB2RE   *
  *                 procedure.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Several problems were addressed in this *
  *                      APAR, all relating to Object Compare    *
  *                      generating a DB2 ALTER TABLE statement  *
  *                      instead of generating a DB2 DROP TABLE  *
  *                      and DB2 CREATE TABLE statement:         *
  *                                                              *
  *                      1.When the column definition is         *
  *                      changed from NULL to NOT NULL,          *
  *                      Admin Tool/Compare drops and recreates  *
  *                      the table, as there is no DB2 ALTER     *
  *                      TABLE statement for changing the NULLs  *
  *                      attribute.                              *
  *                                                              *
  *                      2.DB2 supported ALTER TABLE table DROP  *
  *                      COLUMN col RESTRICT statement to drop a *
  *                      column with certain restrictions. One   *
  *                      restriction is that the column cannot   *
  *                      be dropped if referenced by an index.   *
  *                      When attempting to drop a column that   *
  *                      is referenced by an index, DB2 will     *
  *                      issue an error message:                 *
  *                                                              *
  *                      DSNT408I SQLCODE = -478, ERROR:         *
  *                         ALTER, DROP, OR REVOKE AFFECTING     *
  *                         OBJECT TYPE COLUMN CANNOT BE         *
  *                         PROCESSED BECAUSE OBJECT index OF    *
  *                         TYPE INDEX IS DEPENDENT ON IT        *
  *                                                              *
  *                      Currently Admin Tool/Compare does not   *
  *                      generate ALTER TABLE DROP COLUMN. The   *
  *                      table is dropped and recreated.         *
  *                                                              *
  *                      3.When Table columns are rearranged,    *
  *                      Admin Tool/Compare drops and recreates  *
  *                      the table instead of using DB2 ALTER    *
  *                      TABLE DROP COLUMN and ADD COLUMN        *
  *                      statements to achieve the same result.  *
  *                                                              *
  *                      OR ADB2RE abends with Protection        *
  *                      exception                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A new option on panel ADB2PCO called "Unload altered
  tables" was implemented to give users the option to
  generate ALTER TABLE statements instead of DROP/CREATE
  TABLE statements. The default for this option is 'No'.
  
  When this option "Unload altered tables" is 'Yes', an
  UNLOAD record will be generated, in addition to doing
  the following (corresponding to each of the three problems
  outlined above):
  
  1. When the column definition is changed from NULL to NOT
  NULL, Object Compare will generate ALTER TABLE DROP COLUMN
  when the last few columns of the table are changed from NULL
  to NOT NULL or vice versa. For example:
  
  The table has the same columns in the same order on the source
  and target. The definition of the last column is changing from
  WITH DEFAULT NULL (NULL and default) to NOT NULL WITH DEFAULT
  (NOT NULL and default).
  
  E.g.
  COL1 VARCHAR(12) FOR SBCS DATA WITH DEFAULT NULL,
  is changed to
  COL1 VARCHAR(12) NOT NULL FOR SBCS DATA DEFAULT 'VALUE'
  
  There is no DB2 ALTER TABLE statement which supports changing
  NULL/NOT NULL to NOT NULL/NULL. Instead of generating
  DROP/CREATE TABLE, the column is dropped and appended:
  
  ALTER TABLE schema.tablename
  DROP COLUMN COL1 RESTRICT;
  ALTER TABLE schema.tablename
  ADD COLUMN COL1 VARCHAR(12) NOT NULL FOR SBCS DATA
  DEFAULT 'VALUE'
  
  NOTE: In this release, VARCHAR is the only supported data type.
  
  2. When column to be dropped is reference by an index,
  Admin Tool/Compare will first generate the following DDL
  statement to remove the dependency between column and index:
  
    DROP INDEX inx1;
  
  It will then generate the following statements:
  
    CREATE INDEX inx.. (without col1);
    ALTER TABLE tb1 DROP COLUMN col1 RESTRICT;
  
  This will drop the column, pending the uniqueness of a
  ROWID column.
  
  3.When Table columns are rearranged, Admin Tool/Compare will
  generate ALTER TABLE DROP COLUMN and ALTER TABLE ADD COLUMN
  statements to rearrange, drop, or add
  columns when possible instead of using DROP/CREATE TABLE
  statements. DB2 Restrictions for using ALTER TABLE DROP/ADD
  COLUMN still apply. If these restrictions are encountered,
  Admin Tool/Compare will continue to use the DROP/CREATE TABLE
  statements, as before.
  
  Also fixed in this APAR/PTF is a fix to PROCEDURE ADB2RE
  that receives Protection exception and the caller receives
  SQLCODE=-430 / SQLCODEN430.
  

Problem conclusion

Temporary fix

Comments

• Problem has been resolved.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI58621 UI58622

Modules/Macros

•    ADBALT   ADBCCM   ADBCDCH  ADBCIMU  ADBCIMV
  ADBCRCC  ADBCRCP  ADBCSRV  ADBEALT  ADBEMTAR ADBHPCO  ADBSALT
  ADBSETUP ADBSPCO  ADBTEP2  ADB2CME  ADB2CMP  ADB2CMT  ADB2CMU
  ADB2C11A ADB2PCO  ADB2RE   ADB2REE  ADB2REZ  ADB2WVL  ADB2000
  ADB7000
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI58621

     UP18/09/26 P F809

• RC10 PSY UI58622

     UP18/09/26 P F809

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.