PI82830: NO ROWS RETURNED FOR QUERY REFERENCING A VARBINARY COLUMN WITH UNICODE, OR A QUERY USING INDEX ON EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output can occur for a query referencing a VARBINARY
  column with UNICODE, or a query using index-on-expression
  when running on function mode V12R1M100.
  No rows are returned.                                 (d49314)
  
  This problem is fixed in DB2 11 by APAR PI87200.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of an SQL                          *
  * statement containing a materialized table                    *
  * expression, or a view and a predicate that                   *
  * matches an expression-based index is                         *
  * pushed down.                                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect output may happen on DB2                           *
  * V12 for an SQL statement satisfying                          *
  * all of the following conditions:                             *
  * 1. It contains a materialized table                          *
  * expression, or a view.                                       *
  * 2. The select list of the                                    *
  * materialized table expression                                *
  * or view contains an expression                               *
  * that matches an expression-based                             *
  * index.                                                       *
  * 3. It contains a predicate which is                          *
  * pushed down into the materialized                            *
  * table expression or view and the                             *
  * column in the local predicate is                             *
  * derived from the select list item                            *
  * that matches to the expression-based                         *
  * index.                                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(C1 VARCHAR(200)    WITH DEFAULT,
                  C2 VARCHAR(200)    WITH DEFAULT ) ;
  
  INSERT INTO T1 VALUES('zz', 'zz');
  INSERT INTO T1 VALUES('ZZ', 'ZZ');
  
  CREATE INDEX IX2 ON T1(C1, SUBSTR(C2, 1, 2));
  
  SELECT T1.C1, T2.C2
   FROM T1,
        (SELECT SUBSTR(C2, 1, 2) AS C2,
               (SELECT 1 FROM SYSIBM.SYSDUMMY1)
         FROM T1) AS T2
    WHERE T2.C2 >= 'aa'
      AND T1.C1 >= 'aa'
      AND T2.C2 = T1.C2
  ;
  
  DB2 did not correctly process the aforementioned SQL
  and resulted in incorrect output.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement.
  
  Additional Keywords: SQLVIEW SQLTABLEEXPR SQLINDEXONEXP
                       SQLPREDPUSHDOWN SQLJOIN 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:

  UI49834 PI87200

Modules/Macros

• DSNXOVP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI49834

     UP17/09/11 P F709 ¢

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.