PI99258: LOOP IN DSNXRIHD UI53261 OFFSET14E88 OFFSET14F46 FOR SQL WITH IMPLICIT TYPE INCOMPATIBILITY BETWEEN 18/07/09 PTF PECHANGE

Obtain the fix for this APAR.

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 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

• RB10 PSY UI57287

     UP18/07/31 P F807 «

• RC10 PSY UI57285

     UP18/07/26 P F807 «

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.