PI69551: ABEND04E RC00E7005 AT DSNXGRDS DSNXRITV M114 WHEN USING AFTER TRIGGER WITH TRANSITION VARIABLE RANGE LIST AND MULTI ROW INSERT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E7005 at dsnxgrds dsnxrtiv m114 when using after
  trigger with transition variable range list and multi row insert
  
  Additional symptoms and search keywords
  ABEND04E RC00E7005 SQLMULTIROW SQLINSERT SQLRANGELIST SQLTRIGGER
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of triggers.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00E70005 can occur at     *
  *                      location DSNXRITV M114 when an AFTER    *
  *                      INSERT trigger is executed after a      *
  *                      multiple-row insert is performed if     *
  *                      the triggered SQL statement uses a      *
  *                      transition variable in a range list     *
  *                      predicate.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00E70005 can occur at location DSNXRITV M114 when
  an AFTER INSERT trigger is executed after a multiple-row insert
  is performed if the triggered SQL statement uses a transition
  variable in a range list predicate.
  
  
  The following example helps to illustrate the problem.
  
  1. Create a Table T1 with an index and insert a single row.
  
     CREATE TABLE T1 (C1 INTEGER, C2 VARCHAR(10));
  
     CREATE INDEX T1_IX1 ON T1(C1,C2);
  
     Note: The index is created on columns C1 and C2 so that
     a range list can be used on these two columns.
  
     INSERT INTO T1 VALUES(1,'TEST1');
  
  
  2. Create an AFTER INSERT Trigger using a range list on
     columns (C1,C2).
  
     --#SET TERMINATOR #
     CREATE TRIGGER TRIG_AI
      AFTER INSERT ON T1
      REFERENCING NEW AS N
      FOR EACH ROW MODE DB2SQL
  
       BEGIN ATOMIC
        UPDATE T1 SET C1 = 2
         WHERE C1 = N.C1
          AND (C2 = N.C2 OR C2 IS NULL);
       END#
  
     --#SET TERMINATOR ;
  
  
  3. Execute a multiple-row insert (MRI) for Table T1 which fires
     the trigger TRIG_AI. For example, via a JDBC application
     that contains the following insert statement,
  
     String sqlstr = "INSERT INTO T1 VALUES (1, ?)";
     PreparedStatement pstmt = conn.prepareStatement(sqlstr);
  
     pstmt.setNull(1,java.sql.Types.VARCHAR);
     pstmt.addBatch();
  
     pstmt.setString(1, "TEST2");
     pstmt.addBatch();
  
     pstmt.executeBatch();
  
  where 'conn' is a database connection.
  
  The PreparedStatement method addBatch is used to create a batch
  of parameters so that a single statement can be executed
  multiple times using a different set of parameters for each
  execution.
  
  
  When the MRI statement fires the AFTER INSERT Trigger, TRIG_AI,
  the transition variable N.C2 becomes NULL and then a non-null
  value during the inserts. It is used inside the range list
  predicate on columns (C1,C2). Because DB2 does not handle the
  internal structure for N.C2 properly, it leads to the abend
  mentioned above.
  
  
  Please note that performing the MRI statement shown in Step (3)
  against a DB2 10 bound trigger package with the same DDL
  statements as those in Step (1) and Step (2) could also result
  in the same abend.
  

Problem conclusion

• DB2 code has been modified to correctly process the
  aforementioned SQL statement to prevent the reported abend.
  
  Please note, APAR PI70615 provides the solution for DB2 12.
  
  
  Additional Keywords: SQLTRIGGER SQLNULL SQLMULTIROW SQLINSERT
                       SQLRANGELIST
  

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:

  UI42549

Modules/Macros

• DSNXRITV
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI42549

     UP16/11/29 P F611

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.