PK56392: ADD FUNCTION FOR ALTER TABLE DROP DEFAULT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Add function for  ALTER TABLE DROP DEFAULT
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V9 users who use ALTER TABLE.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: This apar introduces a new function     *
  *                      which enhances ALTER TABLE to drop      *
  *                      default values from user tables.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  ALTER TABLE ALTER COLUMN will be enhanced to support dropping
  default values. This includes system and user-defined default
  values, but excludes DB2-generated default values. Existing rows
  will retain their current value. Any UPDATE with DEFAULT and
  INSERT subsequent to the DROP DEFAULT will be affected.
  
  ================
  V9 SQL Reference
  ================
  This APAR/PTF changes the text of the ALTER TABLE section.
  of the V9 DB2 SQL Reference.
  
  V9 SQL Reference, under ALTER TABLE, column-alteration
  
  --------------------------------------
  ALTER COLUMN column-alteration section
  --------------------------------------
  DROP DEFAULT
  Drops the current default for the column. For not nullable
  columns, the specified column must have a default value and for
  nullable columns, the specified column cannot have a null
  default value (SQLSTATE 42837, SQLCODE -190). For nullable
  columns, the new default value is the null value.
  
  The table must not be referenced by a view (SQLCODE -650,
  SQLSTATE 56090) and cannot be defined with DATA CAPTURE CHANGES
  (SQLCODE -148, SQLSTATE 42809).
  
  To remove the default value for a column that was defined with
  an ALTER TABLE statement that included an ADD COLUMN clause,
  do the following:
  -Run the REORG utility on the table space that contains the
   table
  -Issue the ALTER TABLE statement that specifies DROP DEFAULT
  If the REORG is not processed, an error is returned for the
  ALTER TABLE statement (SQLCODE -650, SQLSTATE 56090).
  
  ================
  DB2 Codes manual
  ================
  This APAR/PTF changes the text of a few SQL codes.  The change
  will be documented in the DB2 Codes manual.
  
  ------------
  SQLCODE -650
  ------------
  The following reason-codes will be modified for SQLCODE -650:
  
  21: ALTER TABLE SET DEFAULT and DROP DEFAULT are not allowed for
      tables in advisory REORG-pending (AREO) state.
  22: ALTER TABLE SET DEFAULT and DROP DEFAULT are not allowed for
      tables referenced by a materialized query table or view.
  
  ------------
  SQLCODE -350
  ------------
  The following reason will be modified for SQLCODE -350:
  
  - An XML column cannot be specifed with the SET DEFAULT or DROP
    DEFAULT  clause.
  
  ------------
  SQLCODE -148
  ------------
  The following reason-codes will be modified for SQLCODE -148:
  
  01 The ALTER statement specified an ALTER COLUMN clause that
     attempted to drop the default value, alter the default value,
     or change the length of the column. The <DELETE length of the
     DELETE> column cannot be altered because the column has a
     field procedure or is involved in a referential constraint.
  02 The ALTER statement specified an ALTER COLUMN clause that
     attempted to drop the default value, alter the default value,
     or change the length of the column. The l<DELETE length of
     the  DELETE> column cannot be altered because source-name is
     a table that has an edit procedure or valid procedure, is
     defined with data capture changes, or is a global temporary
     table.
  
  --------------
  SQLCODE -20240
  --------------
  The following reason-code will be modified for SQLCODE -20240:
  
  4. A security label column DEFAULT cannot be altered .
  
  ------------
  SQLCODE -190
  ------------
  The following reasons will be modified for SQLCODE -190:
  - If a SET DEFAULT or DROP DEFAULT clause is specified, the
    existing column does not have a defined default value, or
    the column has NOT NULL as the null attribute.
  - If a SET DEFAULT or DROP DEFAULT clause is specified,
    the existing column is defined as an identity column,
    rowid, or row change timestamp column.
  
  
  Additional Keywords
  -------------------
  SQLCODE148
  SQLCODE350
  SQLCODE650
  SQLCODE190
  SQLCODE20240
  SQLALTER
  

Problem conclusion

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:

  UK42715

Modules/Macros

• DSNDHNOD DSNGDART DSNHAPLY DSNHBNF  DSNHPNAM
  DSNHPTAB DSNHSCOM DSNHSM6C DSNHSYMH DSNXIALC DSNXISB5
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK42715

     UP09/01/06 P F901

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.