PI60659: SQLCODE151 DSNXOST FOR INSTEAD OF TRIGGER ON VIEW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE=-151
  SQLSTATE=42808
  CSECT NAME=DSNXOST
  RDS CODE=-400
  
  Keywords:
  SQLCODE151 SQLTRIGGER SQLVIEW SQLINSTEADOFTRIGGER
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 users of INSTEAD OF triggers.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected or incorrect SQLCODE         *
  *                      may be issued for a UPDATE/INSERT/      *
  *                      DELETE/MERGE statement containing       *
  *                      multiple references of the same         *
  *                      view, on which INSTEAD OF trigger       *
  *                      is defined with the exact matching      *
  *                      action. In addition, one or more of     *
  *                      the following conditions must be        *
  *                      met:                                    *
  *                      1. The query contains explicit period   *
  *                      specification or period clause.         *
  *                      2. The query is a static query,and any  *
  *                      of the bind options SYSTIMESENSITIVE,   *
  *                      BUSTIMESENSITIVE, or ARCHIVESENSITIVE   *
  *                      is set to YES (YES is the default for   *
  *                      all the three options).                 *
  *                      3. The query is a dynamic query.        *
  *                      Bind option SYSTIMESENSITIVE is         *
  *                      set to YES, and the CURRENT TEMPORAL    *
  *                      SYSTEM_TIME special register            *
  *                      contains non-null value.                *
  *                      4. The query is a dynamic query.        *
  *                      Bind option BUSTIMESENSITIVE is         *
  *                      set to YES,  and the CURRENT TEMPORAL   *
  *                      BUSINESS_TIME special register contain  *
  *                      non-null value.                         *
  *                      5. The query is a dynamic query.        *
  *                      Bind option ARCHIVESENSITIVE is set     *
  *                      to YES, and the SYSIBMADM.GET_ARCHIVE   *
  *                      global variable contains 'Y' value.     *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For Example,
   CREATE TABLE T1 (C1 INT,C2 INT);
  
   CREATE VIEW V1 (VC1,VC2) AS SELECT C1+1, C2 FROM T1;
  
   CREATE TRIGGER TRIG1 INSTEAD OF UPDATE ON V1
    REFERENCING OLD AS OLD1 NEW AS NEW1
    FOR EACH ROW MODE DB2SQL
    UPDATE T1 SET
      C1 = NEW1.VC1;
  
   SET CURRENT TEMPORAL BUSINESS_TIME='2008-01-06-00.00.00.000';
  
   UPDATE V1 SET VC1 = 2;
  
  SQLCODE -151  may be issued incorrectly for the UPDATE
  statement above from DSNXOST at location -400.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement.
  
  Additional Keywords: SQLTEMPORAL SQLTRIGGER SQLINSERT SQLDELETE
                       SQLMERGE SQLIWS SQLINSTEADOFTRIGGER
                       SQLCODE151 SQLVIEW
  

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:

  UI39038

Modules/Macros

• DSNXOTDA DSNXOTL  DSNXOVC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI39038

     UP16/07/21 P F607

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.