PI18823: MESSAGE SQLCODEN20181 ERROR: COLUMN CANNOT BE ADDED TO INDEX FOR ALTER INDEX ADD INCLUDE COLUMN APPLY JOB

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS
  fails with a message:
  ADB5044I *** INPUT STATEMENT:
     ALTER INDEX >schema<.>index_name<
       ADD INCLUDE COLUMN (>col<) ;
  sqlerror on ALTER      command, EXECUTE    function
   DSNT408I SQLCODE = -20181, ERROR:  COLUMN CANNOT BE ADDED TO
  INDEX >schema<.>index_name<
  
   DSNT418I SQLSTATE   = 428FS SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNXIAIX SQL PROCEDURE DETECTING ERROR
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS and DB2 Object Comparison Tool for      *
  *                 z/OS who make changes to add included        *
  *                 columns to an index.                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 Administration Tool for z/OS        *
  *                      and DB2 Object Comparison Tool          *
  *                      for z/OS generated an ALTER INDEX       *
  *                      <ix_schema>.<ix_name> ADD               *
  *                      INCLUDE COLUMN (<colname>)              *
  *                      statement in the APPLY job which        *
  *                      fails with a SQLCODEN20181 /            *
  *                      SQLCODE=-20181 error when the           *
  *                      index is an index on a foreign key.     *
  *                                                              *
  *                      Example of the error when running       *
  *                      the APPLY job:                          *
  *                                                              *
  *                      ADB5044I *** INPUT STATEMENT:           *
  *                         ALTER INDEX                          *
  *                      >schema<.>index_name<                   *
  *                         ADD INCLUDE COLUMN (>col<) ;         *
  *                      sqlerror on ALTER command,              *
  *                      EXECUTE   function                      *
  *                      DSNT408I SQLCODE = -20181,              *
  *                      ERROR:  COLUMN CANNOT BE                *
  *                      ADDED TO INDEX                          *
  *                      >schema<.>index_name<.                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 Administration Tool for z/OS and DB2 Object Comparison
  Tool for z/OS generated an ALTER INDEX <ix_schema>.<ix_name>
  ADD INCLUDE COLUMN (<colname>) statement in the APPLY job
  which fails with a SQLCODEN20181 / SQLCODE=-20181 error when
  the index is an index on a foreign 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:

Modules/Macros

•    ADB2CMF  ADB2CMR
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA20 PSY UI20537

     UP14/08/20 P F408

• RB10 PSY UI20538

     UP14/08/20 P F408

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.