IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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

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



Document information

More support for: DB2 for z/OS

Software version: 11.0

Reference #: PI99258

Modified date: 02 August 2018


Translate this page: