IBM Support

PM87677: INCORROUT FOR DBCLOB COLUMN WHEN THE TABLE IS USED AS OUTER TABLE IN A JOIN

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • It's an incorrect output issue for DBCLOB column when the table
    is used as outer table in a join. Only the first row returned is
    correct, the data of the DBCLOB column is cut off for the
    subsequent rows. The problem occurs when remotely executing the
    query through Windows DB2 client, or Data Studio, etc.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of DBCLOB Large    *
    *                 Objects in a distributed environment with    *
    *                 progressive streaming option.                *
    ****************************************************************
    * PROBLEM DESCRIPTION: When running DB2 applications in a      *
    *                      distributed environment with the        *
    *                      progressive streaming option, DB2 can   *
    *                      return an incorrect result for queries  *
    *                      that contain a nested loop join with an *
    *                      outer table containing a DBCLOB column. *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When running DB2 applications in a distributed environment with
    the progressive streaming option, DB2 can return an incorrect
    result for queries that involve a nested loop join if the outer
    table contains a DBCLOB column.
    
    The incorrect result occurs because DB2 inadvertently modifies
    an internal persistent length field for generating the character
    length.  In fact, DB2 should perform this calculation for
    character length in a separate local variable.
    
    The following JAVA snippet serves as an example which might
    generate an incorrect result.
    
    Step 1. Define two tables, one which contains a DBCLOB column.
    
      CREATE DATABASE MYDBASE CCSID UNICODE;
    
      CREATE TABLE Primary(id integer NOT NULL,
                           short_string vargraphic(16) ,
                           long_string dbclob(16))
      IN DATABASE MYDBASE;
    
      CREATE TABLE Secondary(parent_id integer NOT NULL ,
                             ordinal integer NOT NULL)
      IN DATABASE MYDBASE;
    
    
    Step 2. Insert data into the two tables.
    
      INSERT INTO Primary(id, short_string, long_string)
      VALUES(1, 'short string a', 'long string a');
    
      INSERT INTO Secondary(parent_id, ordinal  VALUES(1, 1);
      INSERT INTO Secondary(parent_id, ordinal  VALUES(1, 2);
      INSERT INTO Secondary(parent_id, ordinal  VALUES(1, 3);
      INSERT INTO Secondary(parent_id, ordinal  VALUES(1, 4);
    
    
    Step 3. Perform a query containing an inner join with the
            progressive streaming option enabled.
    
     try {
      String sql="SELECT id, ordinal, short_string,long_string "+
                 "FROM Primary p INNER JOIN Secondary s " +
                 "ON p.id = s.parent_id;";
      stmt = con.prepareStatement(sql);
      stmt.execute();
      ResultSet rs = stmt.executeQuery();
      while (rs.next())
       {
       System.out.println(rs.getInt(1) + "," + rs.getInt(2)+","+
       rs.getString(3) + "," + rs.getString(4));
       }
      rs.close();
      con.commit();
         } catch (SQLException e) {
      System.out.println("**SQLException in CALL simple proc!"+e);
      System.out.println("***SQLCODE = " + e.getErrorCode());
      System.out.println("***SQLSTATE = " + e.getSQLState());
      System.out.println("***Text of Error Message = "
       + e.getMessage());
      }
    

Problem conclusion

  • The code in DB2 has been modified to prevent returning an
    incorrect result for the case described.
    
    Additional Keywords: SQLLOB SQLDDF SQLNLJ
                         SQLINCORR INCORROUT SQLINCORROUT
                         DB2INCORR/K
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM87677

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-23

  • Closed date

    2013-06-03

  • Last modified date

    2013-07-16

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK94814

Modules/Macros

  • DSNOLMAT
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK94814

       UP13/06/19 P F306 ½

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

Reference #: PM87677

Modified date: 16 July 2013