A fix is available
APAR status
Closed as program error.
Error description
with PI71903 applied, and with with DDF_COMPATIBILITY = DISABLE_IMPCAST_JV when executing SQL that has non-matching hostvars type with columns, loop may occur in DSNXRIHD UI53261 offset14E88 offset14F46
Local fix
change application: hostvar to match column type
Problem summary
**************************************************************** * USERS AFFECTED: All Db2 v11 for z/OS with PI71903/UI46243 * * applied or v12 for z/OS users with * * PI76402/UI47107 applied and running * * distributed SQL with special register * * APPLICATION COMPATIBILITY set to "V10R1" and * * zparm DDF_COMPATIBILITY set to * * DISABLE_IMPCAST_JV. * **************************************************************** * PROBLEM DESCRIPTION: A small loop can occur in module * * DSNXRIHD (between OFFSET14740 and * * OFFSET14F00 for Db2 v11) when running a * * distributed query that contains an * * INSERT statement with host variable * * input after the application of Db2 v11 * * for z/OS apar/ptf PI71903/UI46243 or * * Db2 v12 for z/OS apar/ptf * * PI76402/UI47107 with special register * * APPLICATION COMPATIBILITY set to * * "V10R1" and zparm DDF_COMPATIBILITY set * * to DISABLE_IMPCAST_JV. * **************************************************************** * RECOMMENDATION: * **************************************************************** A tight or small loop can occur in module DSNXRIHD when running a distributed query that contains an INSERT statement with host variable input after the application of Db2 v11 for z/OS apar/ptf PI71903/UI46243 or Db2 v12 for z/OS apar/ptf PI76402/UI47107 with special register APPLICATION COMPATIBILITY set to "V10R1" and zparm DDF_COMPATIBILITY set to DISABLE_IMPCAST_JV. In Db2 v11, the loop can occur between OFFSET14740 and OFFSET14F00 (these taken from the current maintenance level). So, these offsets can vary. The following example helps to present a failing or looping case. Step 1. Bring up Db2 with the zparm DDF_COMPATIBILITY set to DISABLE_IMPCAST_JV. Step 2. In a distributed JAVA application, use SPECIAL REGISTER CURRENT APPLICATION COMPATIBILITY set to "V10R1". Step 3. Submit the following JAVA script to be executed. String sql = "CREATE TABLE T1(C1 CHAR(2))"; PreparedStatement stmt = con.prepareStatement(sql); stmt.executeUpdate(); sql = "INSERT INTO T1 VALUES(?)"; stmt = con.prepareStatement(sql); stmt.setNull(1, 4); <<= set null for integer host variable Step 4. Check the result of the execution. This SQL will insert a nullable integer into a character column C1 (which is defined as nullable) of Table T1. This example represents the reported case using an input with a host variable. The last element in the input VALUES clause references a nullable column (C1) for the loop to occur. The input can contain multiple elements or host variables as long as the last one references a nullable column. In this case, a nullable integer is to be inserted into a nullable character column. An internal flag is set once an implicit cast (integer->char) error is detected which leads to the loop. Once the loop has been detected, the Db2 DDF thread can be canceled with the following command sequence, -CANCEL THREAD(token) -CANCEL DDF THREAD(token) FORCE Furthermore, until the problem has been corrected, it is advised not to run with the APPLICATION COMPATIBILITY zparm DDF_COMPATIBILITY. Another possible or better workaround is to set deferPrepares property for Data Source or Connection as false. For example: ds.setDeferPrepares(false) Possible values are: true Statement preparation on the data source does not occur until the PreparedStatement.execute method is executed. This value is the default. false Statement preparation on the data source occurs when the Connection.prepareStatement method is executed. Deferring prepare operations can reduce network delays. However, if you defer prepare operations, you need to ensure that input data types match table column types. In our example above, please note. ==> If ds.setDeferPrepares(false) the loop does not occur. ==> If ds.setDeferPrepares(true) the loop does occur. The expected result is shown as follows. SELECT * FROM T1; +----+ | C1 | +----+ 1_| ? | +----+ Please note: A similar loop can occur in module DSNXRIHS when Db2 is the server under similar circumstances. All of the following conditions must exist for the loop to occur per the reported case (for Db2 v11). 1. Apar PI71903 (ptf UI46243) must be applied. 2. The zparm DDF_COMPATIBILITY is set to DISABLE_IMPCAST_JV. 3. The SQL to be run is dynamic, it is run from a distributed connection. 4. The dynamic SQL contains special register, SET CURRENT APPLICATION COMPATIBILITY = 'V10R1'; 5. The dynamic SQL contains an INSERT statement. However, other statements may also be affected. Only Java applications using IBM Data Server driver are affected. 6. The Java script contains method ds.setDeferPrepares(true) by default. 7. The INSERT statement accepts host variable input. 8. The VALUES clause contains at least one host variable, the last one must be null and is associated with a NULLABLE column.
Problem conclusion
The code in Db2 has been modified to prevent the loop from occurring when using the special register CURRENT APPLICATION COMPATIBILITY, "V10R1" with the DDF_COMPATIBILITY = DISABLE_IMPCAST_JV zparm setting to disable "implicit cast" for distributed SQL INSERT statements that contain a VALUES clause with the last element being nullable. Additional Keywords: SQLLOOP SQLINSERT LOOPDSNXRIHD LOOPDSNXRIHS SQLNULL
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PI99258
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
YesPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-06-18
Closed date
2018-07-16
Last modified date
2018-08-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI57285 UI57287
Modules/Macros
DSNXRIHD DSNXRIHS
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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:
02 August 2018