PH37364: CM BATCH ALTER TABLESPACE ALTER ADD COLUMN RUN TIME ERROR SQLCODE = -20385

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• CM Batch import and analyze of a change orders the ALTER of a
  tablespace being converted to a PBG before an ALTER ADD COLUMN
  of a table.  As a result, the run job ends with the following
  SQLCODE -20385 error, because the tablespace is put into AREORGP
  status before the table ALTER can be executed:
  
  ADB7955E  DSNT408I SQLCODE = -20385, ERROR:  THE STATEMENT
                     CANNOT BE PROCESSED BECAUSE
                     THERE ARE PENDING DEFINITION CHANGES FOR
                     OBJECT >database<.>tablespace<
                     OF TYPE  TABLESPACE (REASON 2)
  
  The statements are ordered incorrectly because in CMBATCH
  the DISABLE_OPTIMIZE_REORG setting is internally updated to the
  wrong default.
  
  
  Keywords:  msgadb7955E; sqlcode20385n
  

Local fix

• Set CM batch parm DISABLE_OPTIMIZE_REORG ='Y' for ANALYZE.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 Administration Tool for z/OS    *
  *                 and Db2 Object Comparison Tool for z/OS      *
  ****************************************************************
  * PROBLEM DESCRIPTION: An attempt was made to convert a table  *
  *                      space to PBG by using CM batch without  *
  *                      specifying a value for the              *
  *                      DISABLE_OPTIMIZE_REORG setting. Running *
  *                      the resulting WSL results in SQLCODE    *
  *                      -20385.                                 *
  ****************************************************************
  Altering a table and its table space to partition-by-growth
  (PBG) by using CM batch creates a problematic statement ordering
  in the resulting work statement list (WSL), due to the parameter
  DISABLE_OPTIMIZE_REORG. If this parameter is unspecified or set
  to 'N', the WSL will contain ALTER operations for the table
  space before the ALTERs for the table without a REORG operation
  in between the ALTERs. If the WSL is run, the following error
  might occur:
  
  SQLCODE = -20385, ERROR: THE STATEMENT CANNOT BE PROCESSED
  BECAUSE THERE ARE PENDING DEFINITION CHANGES FOR OBJECT
  >database<.>tablespace< OF TYPE TABLESPACE (REASON 2)
  

Problem conclusion

• The 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:

  UI76937

Modules/Macros

• ADB2CMP
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI76937

     UP21/08/28 P F108

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.