You might encounter a trap when compiling an SQL statement
that 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 the generated column, and
5. the query includes a 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)
In a trap file, you might see the stack trace as following:
...
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
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 f (d1key int, d2key int, c int, g int generated
always as (c/10))
create index ix1 on f(g,d1key,d2key)
select f.c from d1,d2,f where f.c=100 and f.d1key=d1.pk and
f.d2key=d2.pk and d1.c1=10 and d2.c2=10 |
****************************************************************
* USERS AFFECTED: *
* AIX, Linux, Windows *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V10.5 Fix Pack 3 or higher. *
**************************************************************** |
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 from d1,d2,f where f.c=100 and f.d1key=d1.pk and
f.d2key=d2.pk and d1.c1=10 and d2.c2=10 |
