DB2 - Problem description
Problem IC87037 | Status: Closed |
DB2 MIGHT HANG WHILE COMPILING AN SQL STATEMENT ELIGIBLE FOR ZIGZAG JOIN STRATEGY. | |
product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problem description: | |
In DB2 Version 10.1, the optimizer can consider the zigzag join strategy, and might choose it if the query satisfies the following criteria: - the fact table is joined with multiple dimension tables by equality predicates between each dimension table's primary key, unique index or unique constraint and the fact table columns; and - there is a suitable multicolumn index on the fact table that covers the above mentioned equality predicates between the fact table and at least two dimension tables. During query compilation, the DB2 server might hang if the optimizer chooses a zigzag join. The following stack trace can be observed if you encounter this problem: sqlno_compute_temp_index_stats sqlno_cost_iot_temp sqlno_cost_temp sqlno_prop_temp TEMP | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 3. * **************************************************************** | |
Local Fix: | |
db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF, ZZJN_MULTI_FACT OFF" OR Use optimization level 0 | |
available fix packs: | |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 Version 10.1 Fix Pack 3. | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 05.10.2012 14.10.2013 14.10.2013 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) | |
10.1.0.3 | |
10.1.0.3 |