DB2 FOR LUW / DB2FORLUW / A10 - DB2

DB2 might return error SQL0901N while compiling an SQL 
statement, which satisfies the following conditions: 
 
   1.  the statement qualifies the starjoin eligibility 
criteria, and 
   2.  the all-probes list-prefetch plan alternative for a 
zigzag join is 
   chosen by the optimizer, and 
   3.  the fact table identified in the zigzag join has a 
generated column, 
   and 
   4.  the index used in the fact table's index access under the 
zigzag 
   join includes both the generated column as well as source 
column, and 
   5.  the query includes a join predicate on the source column 
of the fact 
   table's generated column, and 
   6.  the DB2 query compiler can generate a predicate on the 
generated 
   column using the predicate on the source column 
   7.  the generated column is not in the select list of the 
query (or 
   subquery) 
 
The following kind of stack can be observed in the dump file. 
   ... 
   sqlnn_erds 
   sqlng_process_parse_tree_node 
   sqlng_process_BF_node 
   sqlng_walk_BF_chain 
   sqlng_build_thread 
   sqlng_build_TA_op 
   sqlng_process_f_ioa 
   sqlng_process_fetch_op 
   sqlngProcessLolepop 
   sqlngZZBackJoin 
   sqlng_process_zigzag_op 
   ... 
 
The following is a simple example that could hit this issue if 
the optimizer chooses an all-probes list-prefetch zigzag join 
access plan: 
 
   create table d1 (pk int not null primary key, c1 int); 
   create table d2 (pk int not null primary key, c2 int); 
   create table d3 (pk int not null primary key, c3 int); 
   create table f (d1key int, d2key int, c int, g int generated 
always as (c/10)); 
 
   -- note: index has both the generated column 'g' as well as 
source column 'c' 
   create index ix2 on f( c, d1key, d2key, g ); 
 
   select f.c 
   from   d1,d2,d3,f 
   where  f.c = d3.pk     and 
          f.d1key = d1.pk and 
          f.d2key = d2.pk and 
          d1.c1 = 10      and 
          d2.c2 = 10      and 
          d3.c3 = 10;

**************************************************************** 
* USERS AFFECTED:                                              * 
* See Error Description                                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.1 Fix Pack 5.                          * 
****************************************************************

Alternative: 1) 
Include the generated column in the select list. 
For the example query in the error description, include f.g in 
the select list: 
 
   select f.c, f.g 
   from   d1,d2,d3,f 
   where  f.c = d3.pk     and 
          f.d1key = d1.pk and 
          f.d2key = d2.pk and 
          d1.c1 = 10      and 
          d2.c2 = 10      and 
          d3.c3 = 10; 
 
Alternative: 2) 
Disable zigzag join by setting registry variable. 
   DB2_REDUCED_OPTIMIZATION="ZZJN OFF".

Problem was first fixed in DB2 UDB Version 10.1 Fix Pack 5.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

23.07.2014
16.07.2015
16.07.2015