PH24104: ALTER COLUMN NOT NULL TO WITH DEFAULT NULL UNLOAD ALTERED TABLE YES DATA LOSS;NEW OPTION RECREATE FOR NULLS CHANGE

Obtain the fix for this APAR.

APAR status

• Closed as unreproducible in next release.

Error description

• Altering a column of a table from 'NOT NULL' to
  'WITH DEFAULT NULL', with UNLOAD ALTERED TABLES =
  'YES', generates 'ALTER TABLE DROP COLUMN', REORG, and an 'ALTER
  TABLE ADD COLUMN'.  Since the data is not saved before the drop
  of the column, all data of the affected column may get lost.
  
  This APAR adds a new option, RECREATE FOR NULLS CHANGE, to
  accompany UNLOAD ALTERED TABLES = 'YES'.  With this option set
  to 'YES', UNLOAD, DROP, CREATE and LOAD statements will be
  produced.  With the option set to 'NO', Admin Tool will generate
  the same ALTER statements that it does now.
  
  Note:  By default, the value of the new RECREATE FOR
  NULLS CHANGE and  RECREATE_FOR_NULLS_CHANGE (CMBATCH)
  option is YES (use UNLOAD, DROP, CREATE, and LOAD operations).
  If you want to override this value to NO for a particular CM
  batch job, add RECREATE_NULLS_CHANGE = NO to the GOCCM.PARMS DD
  statement in your JCL.  If you want to change your installation
  default value for this parameter to NO, add
  RECREATE_NULLS_CHANGE = NO to the PROFPARM data set.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the Db2 Administration Tool for     *
  *                 z/OS and Object Comparison Tool for z/OS     *
  *                 who alter the NULLS attribute of a           *
  *                 column.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Executing a work statement list (WSL)   *
  *                      from Object Compare or CM Batch might   *
  *                      result in data loss when altering       *
  *                      the NULLS attribute of a                *
  *                      column (the NULLS value                 *
  *                      in the SYSCOLUMNS catalog               *
  *                      table).                                 *
  ****************************************************************
  When altering a column's NULLS attribute with the option "Unload
  Altered Tables" set to YES, the resulting WSL contains the
  following sequence of operations:
  
  ALTER TABLE DROP COL col1
  REORG
  ALTER TABLE ADD COL col1
  
  Running this sequence of operations results in data loss for
  the column, because the data is not saved before the
  DROP COL operation.
  

Problem conclusion

Temporary fix

Comments

• By default, the value of the new "Recreate for NULLS Change" and
  RECREATE_FOR_NULLS_CHANGE (CMBATCH) option is YES (use UNLOAD,
  DROP, CREATE, and LOAD operations). If you want to override this
  value to NO for a particular CM batch job, add
  RECREATE_NULLS_CHANGE = NO to the GOCCM.PARMS DD statement in
  your JCL . If you want to change your installation default value
  for this parameter to NO, add RECREATE_NULLS_CHANGE = NO to the
  PROFPARM data set.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI71178

Modules/Macros

• ADB2000  ADB2CM1  ADB2CM2  ADB2CM3  ADB2CME  ADB2CMF  ADB2CMO
  ADB2CMP  ADB2CMR  ADB2CMS  ADB2CMT  ADB2CMU  ADB2PCO  ADB7000
  ADBCCM   ADBEALT  ADBEMTAR ADBHPCO  ADBSALT  ADBSPCO
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI76402

     UP21/07/23 P F107

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.