PM66561: ALT SQLCODEN750 WHEN TABLE HAS TRIGGER DEFINED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS' ALT function created
  an ALTER statement which fails with a SQLCODE -750 / SQLCODEN750
  when attempting to change the data type for a column used by a
  TRIGGER:
  
  ALTER TABLE >schema<.>table_name<
    ALTER >column_name< SET DATA TYPE VARCHAR(400) ;
  after DB2 APAR PM40141 has been applied.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool         *
  *                 for z/OS who use the 'Alter Table ALT'       *
  *                 (ALT) function to extend the column length   *
  *                 and there are one or more triggers           *
  *                 referencing the column on DB2 9 in any       *
  *                 mode.                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the ALT function to extend   *
  *                      the column length on a column that is   *
  *                      referenced by one or more existing      *
  *                      triggers on DB2 9 in any mode, the      *
  *                      APPLY job failed with RC=8 / return     *
  *                      code 8 and an error message:            *
  *                                                              *
  *                      ADB5185E                                *
  *                        The run is ending due to errors.      *
  *                                                              *
  *                      and SQLCODEN750 / SQLCODE = -750:       *
  *                                                              *
  *                      SQLCODE = -750,                         *
  *                      ERROR:  THE SOURCE TABLE                *
  *                      table-name CANNOT BE RENAMED            *
  *                      OR ALTERED BECAUSE IT IS                *
  *                      REFERENCED IN EXISTING VIEW,            *
  *                      MATERIALIZED QUERY TABLE,               *
  *                      OR TRIGGER DEFINITIONS,                 *
  *                      IS A CLONE TABLE, OR HAS A              *
  *                      CLONE TABLE DEFINED FOR IT.             *
  *                      DSNT418I SQLSTATE   = 42986             *
  *                      SQLSTATE RETURN CODE                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using the ALT function to  alter a column that extends
  the column length, and the column is referenced by one or
  more existing  triggers on DB2 9 in any mode, the APPLY job
  failed with RC=8 / return code 8 and the SQLCODEN750 / SQLCODE
  = -750.
  
  Note:
  According to the DB2 SQL Reference for DB2 9, altering
  a column referenced by a trigger is allowed but in fact
  it is not true. A DB2 APAR PM40141 was opened/closed for DB2
  R810 and DB2 9 to ensure that any column involved in an
  ALTER COLUMN was not referenced in a trigger.
  

Problem conclusion

• Problem has been resolved.
  
  Code has been amended to follow the guidance in the DB2 SQL
  Reference for z/OS for Version 9.1 (SC18-9854-13) and DB2 SQL
  Reference for z/OS for Version 10 (SC19-2983-07).
  

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

•    ADB2CMF  ADB2CMP  ADB2CMR  ADB2CMS  ADB2CMT
  ADB7000
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK90403

     UP12/12/19 P F212

• RA20 PSY UK90404

     UP12/12/20 P F212

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.