PI84820: CM GENERATED WSL TO ADD COLUMN TO INDEX MAY RESULT IN SQLCODE20181 IF INDEX INVOLVED IN REFERENTIAL CONSTRAI

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• If a primary index is to be ALTERed to add a column, Change
  Management generates  a WSL with the ALTER
  INDEX ADD INCLUDE COLUMN statement.  However, CM does not take
  account of the possibility that the index may also share
  columns with a foreigh key existing on the same table, in which
  case the primary and foreign keys must be dropped first.
  Executing the CM-generated WSL without these DROP statements
  will result in SQLCODE -20181 COLUMN CANNOT BE ADDED TO INDEX
  index-name.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Administration Tool for         *
  *                 z/OS who use the Change Management           *
  *                 function.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: ALTER INDEX to add an INCLUDE           *
  *                      column fails with SQLCODE -20181 /      *
  *                      SQLCODEN20181 if the index is           *
  *                      associated with primary key.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When altering index to add an INCLUDE column, the
  ALTER will fail if the index is associated with primary key,
  resulting in SQLCODE -20181.
  
  Problem is fixed by providing the table definition so that
  compare is aware of the primary key and will be able to
  generate the right set of statements as below:
  
  ALTER TABLE...DROP PRIMARY KEY
  DROP INDEX....
  CREATE INDEX...
  ALTER TABLE ..ADD PRIMARY KEY..
  

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:

  UI51343 UI51344

Modules/Macros

•    ADBCVOC  ADB0     H0IHB20J H0IHC10
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI51343

     UP17/10/25 P F710

• RC10 PSY UI51344

     UP17/10/25 P F710

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.