PI76636: SQLCODE = -20182 ERROR ON ATTEMPTED RECREATE OF PARTITIONING INDEX AFTER IT IS DROPPED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• If DB2 Object Comparison Tool indicates that a partitioning
  index needs to be dropped and recreated, the following error may
  result when the attempt is made to recreate the index:
  
   SQLCODE = -20182, ERROR:  PARTITIONING CLAUSE ENDING AT ON
  CREATE. STATEMENT FOR schema.indexname IS NOT VALID
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Object Comparison Tool          *
  *                 for z/OS who change the name of a            *
  *                 partitioning index without using the         *
  *                 mask RENAME function.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: Since DB2 changes index-based           *
  *                      partitioning to table-based             *
  *                      partitioning when dropping a            *
  *                      partitioning index, creating the new    *
  *                      partitioning index fails                *
  *                      with error SQLCODE=-20182 /             *
  *                      SQLCODEN20182.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Object Compare generates a DROP INDEX statement for
  a partitioning index from the target, and a CREATE INDEX
  statement for a partitioning index from the source. Since DB2
  changes index-based partitioning to table-based partitioning
  when dropping a partitioning index, creating the new
  partitioning index fails with the following SQL error:
  
   DSNT408I SQLCODE = -20182, ERROR:  PARTITIONING
   CLAUSE ENDING AT ON CREATE STATEMENT FOR >object<
   IS NOT VALID
   DSNT418I SQLSTATE   = 530A2
   SQLSTATE RETURN CODE
  

Problem conclusion

• If partitioning index is dropped without dropping the base table
  the new index will be created without partition information
  (non-partitioning index). If the base table is dropped for a
  different reason, both objects will be created according to
  their source definition (index-based partitioning will be
  preserved).
  
  Using the mask RENAME function to change an index name will
  change the name of a partitioning index without changing the
  table or index definition.
  

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

•    ADB2CMO
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI48498

     UP17/07/07 P F707

• RC10 PSY UI48499

     UP17/07/07 P F707

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.