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