A fix is available
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 number
PI69551
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-09-21
Closed date
2016-11-11
Last modified date
2016-12-01
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 December 2016