DB2 - Problem description
Problem IC98924 | Status: Closed |
DB2 MIGHT TRAP WHILE COMPILING A SQL STATEMENT THAT IS ELIGIBLE FOR ZIGZAG JOIN | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
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 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * AIX, Linux, Windows * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 V10.5 Fix Pack 3 or higher. * **************************************************************** | |
Local Fix: | |
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 | |
available fix packs: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Solution | |
Fixed in DB2 V10.5 Fix Pack 3. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 23.01.2014 28.01.2015 28.01.2015 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) | |
10.5.0.4 |