PI10779: SQLCODEN669 THE OBJECT CANNOT BE EXPLICITLY DROPPED ON DROP INDE X DUE TO OUT OF SEQUENCE ALTER TABLE DROP UNIQUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS' Change Management feature
  created out of sequence DDL which results in a SQLCODE -669 /
  SQLCODEN669 error:
     DROP INDEX >schema<.>index<  ;
  sqlerror on DROP       command, EXECUTE    function
   DSNT408I SQLCODE = -669, ERROR:  THE OBJECT CANNOT BE
  EXPLICITLY DROPPED.
            REASON 0002
   DSNT418I SQLSTATE   = 42917 SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNXIDIX SQL PROCEDURE DETECTING ERROR
   DSNT416I SQLERRD    = 501  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
   DSNT416I SQLERRD    = X'000001F5'  X'00000000'  X'00000000'
  X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  as the ALTER TABLE DROP UNIQUE appears after the DROP INDEX
  statement.
  

Local fix

• Move the ALTER TABLE DROP UNIQUE statement infront of the
  DROP INDEX statement.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Object Comparison Tool for  *
  *                 z/OS who compare tables created on           *
  *                 partitioned table spaces.                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: When comparing tables created on        *
  *                      partitioned table spaces where there    *
  *                      is a unique constraint and a unique     *
  *                      index for the constraint defined on     *
  *                      the target table, but the unique        *
  *                      constraint is not defined on the        *
  *                      source table, and the unique rule       *
  *                      of the index is changed from            *
  *                      UNIQUE to NOT UNIQUE, a                 *
  *                      SQLCODE=-669 / SQLCODEN669              *
  *                      is observed:                            *
  *                                                              *
  *                      DSNT408I SQLCODE = -669,                *
  *                      ERROR: THE OBJECT CANNOT                *
  *                      BE EXPLICITLY DROPPED.                  *
  *                        REASON 0002                           *
  *                      DSNT418I SQLSTATE   = 42917             *
  *                        SQLSTATE RETURN CODE                  *
  *                                                              *
  *                      after running the change.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The unique constraint needs to be removed and the unique index
  needs to be dropped and recreated after the compare. The ALTER
  TABLE DROP UNIQUE statement to remove the unique constraint
  and the DROP INDEX statement to drop the index were generated
  in the CHANGE file, but the sequence of these two statements
  was wrong and leads to the SQLCODE=-669 / SQLCODEN669, which
  should be updated to issue ALTER TABLE DROP UNIQUE statement
  before the DROP INDEX statement.
  

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:

  UI18317 UI18318

Modules/Macros

•    ADB2CMP  ADB2CMR
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA20 PSY UI18317

     UP14/05/28 P F405

• RB10 PSY UI18318

     UP14/05/28 P F405

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.