PI99526: CM BATCH COMPARE ALTER TABLESPACE ALTER INDEX ADVISORY REORG PENDING SQLCODE =-20385

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A CM Batch or Compare run job which contains an ALTER on an
  index also contains an ALTER on a tablespace which places the
  latter in ADVISORY REORG PENDING status (SQLCODE 610 /
  SQLNCODE610).  The job, however, does not REORG the tablespace
  before executing the ALTER on the index.   As a result, the job
  may end with SQLCODE -20385 / SQLCODEN20385:
  
   DSNT408I SQLCODE = -20385, ERROR:  THE STATEMENT CANNOT BE
  PROCESSED BECAUSE THERE ARE PENDING DEFINITION CHANGES FOR
  OBJECT >indexname< OF TYPE INDEX (REASON 9)
  DSNT418I SQLSTATE   = 57007 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXIAIX SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 255  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'000000FF'  X'00000000'  X'00000000'
  X'FFFFFFFF'   X'00000000'  X'00000000' SQL DIAGNOSTIC
  INFORMATION
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS.                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: REORG INDEX cannot resolve the index's  *
  *                      pending state because the tablespace    *
  *                      is in pending state.                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Sample WSL of this APAR is:
  
   ALTER TABLESPACE >tsname< DSSIZE 2G;
   ALTER INDEX >indexname< BUFFERPOOL BP8K2;
   REORG INDEX >indexname<;
  
   ALTER TABLESPACE put the tablespace in pending state.
   ALTER INDEX BUFFERPOOL put index in pending state.
   REORG INDEX cannot resolve index's pending state because
   tablespace is in pending state.
  
  The correct WSL sequence is:
  
   ALTER TABLESPACE >tsname< DSSIZE 2G;
   REORG TABLESPACE >tsname<;
   ALTER INDEX >indexname< BUFFERPOOL BP8K2;
   REORG INDEX >indexname<;
  
  The reported issue may also cause SQLCODE = -644 / SQLCODEN644
  if index attribute PIECESIZE is altered to 8G.
  

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

•    ADBGAJOB ADB2CME  ADB2CMF  ADB2CMO  ADB2CMP
  ADB2CMR  ADB2CM2  ADB2CM3
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI58621

     UP18/09/26 P F809

• RC10 PSY UI58622

     UP18/09/26 P F809

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.