PH00552: GOC - GENERATE ALTER TABLE STATEMENTS INSTAED OF DROP/CREATE IN COMPARE.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• GOC - Generate ALTER TABLE statements instaed of DROP/CREATE in
  COMPARE.
  1. Customer is dropping multiple existing columns and appending
  multiple new columns.
  2. Customer is dropping column from table and index. OC
  processes the table first and decides to drop and recreate the
  table.
  

Local fix

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.                     *
  ****************************************************************
  * 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.  *
  ****************************************************************
  * 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 the following:
  
  1. 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).
  
  Eg.
   ROLE_ORIGIN VARCHAR(12) FOR SBCS DATA WITH DEFAULT NULL,
   is changed to:
  
   ROLE_ORIGIN VARCHAR(12) NOT NULL FOR SBCS DATA DEFAULT
   'RESAUTH'
  
  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 >sqlid<.T003ACCESS_ROLE
   DROP COLUMN ROLE_ORIGIN RESTRICT;
   ALTER TABLE >sqlid<.T003ACCESS_ROLE
   ADD COLUMN ROLE_ORIGIN VARCHAR(12) NOT NULL FOR SBCS DATA
   DEFAULT 'RESAUTH'
  
  In this release, VARCHAR is the only supported data type.
  
  2. Object compare will handle this case by generating the
  following DDL statements:
  
     DROP INDEX inx1;
  
  to remove the dependency between column and index. Then
  
     CREATE INDEX inx.. (without col1);
     ALTER TABLE tb1 DROP COLUMN col1 RESTRICT;
  
  Drop the column. Pending the uniqueness of a ROWID column.
  
  3. Object 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, Object Compare will continue to use the DROP /
  CREATE TABLE statements, as before.
  

Problem conclusion

• Problem has been resolved.
  

Temporary fix

Comments

APAR Information

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

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

  UI58619 UI58620

Modules/Macros

•    GOCCMP   GOCGCMP
  

Fix information

• Fixed component name

  DB2 OBJECT COMP

• Fixed component ID

  5697G6400

Applicable component levels

• RB20 PSY UI58619

     UP18/09/26 P F809

• RC10 PSY UI58620

     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.