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

Obtain the fix for this APAR.

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