PI68734: ALTERING A COLUMN WHICH IS PART OF AN EXTENDED INDEX AND PART OF A FOREIGN KEY MAY RESULT IN SQLCODE = -20180

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR addresses two issues:
  1.  Altering a column which is part of an extended index and
  part of a foreign key may result in:
  DSNT408I SQLCODE = -20180, ERROR:  COLUMN columname IN TABLE
  creatorid.tablename CAN NOT BE ALTERED AS SPECIFIED
  DSNT418I SQLSTATE   = 428FR SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXIALC SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 61 0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION        page 16
  DSNT416I SQLERRD    = X'0000003D'  X'00000000'  X'00000000'
  X'FFFFFFFF'
  X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
  2.  For Version 12 Release 1 only (for Version 11 Release 2,
  please seee see APAR PI75630):  Doing a Compare and using
  Change Management option and specifying Save Compare Results
  YES gets:
     Unexpected sqlcode : -803 in Insert data into
          ADBCMPREPOBJLINES
  

Local fix

• Drop extended index before executing alter.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Object Comparison Tool          *
  *                 for z/OS who compare tables which have       *
  *                 a column that is involved in both an         *
  *                 extended index and a referential             *
  *                 integrity constraint.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: Apply job generated by DB2 Object       *
  *                      Compare fails with SQLCODE=-20180       *
  *                      SQLCODEN20180.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 Object Comparison Tool for z/OS compares two tables
  and generates an APPLY job which contains a table with a
  column that is involved in both an extended index and
  a referential integrity constraint. The generated ALTER
  COLUMN statement is generated before the DROP INDEX
  statement, which causes the job to fail with SQLCODE=-20180:
  
  DSNT408I SQLCODE = -20180, ERROR: COLUMN >column<
   IN TABLE >table< CAN NOT BE ALTERED AS SPECIFIED
  DSNT418I SQLSTATE = 428FR
  SQLSTATE RETURN CODE
  

Problem conclusion

• Problem has been resolved.
  This APAR also includes the forward fit of APAR PI75630
  for DB2 Administration Tool for z/OS, Version 12,
  Release 1 only.
  

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:

  UI47485 010PC2Ÿ 010PC2Ÿ UI47486 010PC2Ÿ 010PC2Ÿ 010PC2Ÿ

Modules/Macros

•    ADB2CME  ADB2CMF  ADB2CMO  ADB2CMP  ADB2CMR
  ADB2CMS  ADB2CMT  ADB2CMU  ADB2CM1  ADB2CM2  ADB2CM3
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI47485

     UP17/05/25 P F705

• RC10 PSY UI47486

     UP17/05/25 P F705

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.