PM61161: QUERY GETS INCORROUT (DUPLICATE VALUES OF VARBINARY COLUMN OR EXPRESSIONS) WHEN ACCESS PATH USES HYBRID JOIN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Query gets incorrout - duplicate values for varbinary column or
  expressions when HYBRID JOIN is effective.
  IDAAV2R1/K
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 and DB2 10 for z/OS users of hybrid    *
  *                 join and varbinary data                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output may be returned for a  *
  *                      query that references varbinary data    *
  *                      and involves a hybrid join access path. *
  *                      The value of the varbinary column has   *
  *                      the same value instead of the value     *
  *                      for each row.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The incorrect output may occur for a query using hybrid join
  which references varbinary data.  The inner table has an
  index which is clustered and no sort is needed (hybrid join
  case 2). The varbinary column is selected from the outer table.
  The problem occurs when the internal buffer for the varbinary
  data is greater than 256 bytes.  The buffer length includes
  the declared length of the column plus 2 bytes for length and
  1 byte for null if the data type is nullable.
  
  For example, the following query uses hybrid join where T1
  is the outer table and T2 is the inner table.
  
  CREATE TABLE T1 (C1 INT,  C2 VARBINARY(254));
  CREATE TABLE T2 (C1 INT,  C2 VARBINARY(254));
  
  CREATE UNIQUE INDEX IX1 ON T1(C1);
  CREATE UNIQUE INDEX IX2 ON T2(C1) CLUSTER;
  
  INSERT INTO T1 VALUES(1,VARBINARY('11111'));
  INSERT INTO T1 VALUES(2,VARBINARY('22222'));
  INSERT INTO T1 VALUES(3,VARBINARY('33333'));
  
  INSERT INTO T2 VALUES(1,VARBINARY('44444'));
  INSERT INTO T2 VALUES(2,VARBINARY('55555'));
  INSERT INTO T2 VALUES(3,VARBINARY('66666'));
  
  SELECT  A.C1, A.C2, B.C2
  FROM T1 A, T2 B
  WHERE A.C1 = B.C1
  
  The value returned for A.C2 may be incorrect if the
  length of the varbinary column buffer is more than
  256 bytes. The following is the incorrect data returned:
  
    C1   A.C2   B.C2
    --   -----  -----
    1    33333  44444
    2    33333  55555
    3    33333  66666
  
  The result should be :
  
    C1   A.C2   B.C2
    --   -----  -----
    1    11111  44444
    2    22222  55555
    3    33333  66666
  

Problem conclusion

• DB2 has been fixed to correctly move the varbinary data using
  the actual length instead of the declared length.  This will
  prevent the incorrect output.
  
  Additional keywords : SQLHYBRIDJOIN SQLVARBINARY
  

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:

  UK78286 UK78287

Modules/Macros

• DSNXRFHJ DSNXRFH9 DSNXRFJ9 DSNXRGBJ DSNXRGB9
  DSNXRSFJ DSNXRT3J DSNXRT39
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK78286

     UP12/05/15 P F205

• R910 PSY UK78287

     UP12/05/15 P F205

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.