|
Problem IT38508
|
Status: Closed |
EXCESSIVE QUERY COMPILATION TIME WITH A VERY LONG SELECT DISTINCT COLUMN LIST AND VERY MANY COLUMN GROUPS
|
| product: |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 |
| Problem description: |
A query might take an extremely long time to compile if it has a
very long SELECT DISTINCT column list and there are a very large
number of column group statistics groups on the columns
referenced in that SELECT DISTINCT list. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 11.5.7 *
* *
* Once you have upgraded, set the DB2_REDUCED_OPTIMIZATION *
* registry variable to "KC_KEYSZ n" where n is a number lower *
* than the number of columns in the SELECT DISTINCT column *
* list. *
* If DB2_REDUCED_OPTIMIZATION is already set, append this *
* setting to the end, separated by a comma (no spaces). *
* For example, if you already have the setting *
* DB2_REDUCED_OPTIMIZATION=YES *
* do this: *
* db2set -im DB2_REDUCED_OPTIMIZATION="YES,KC_KEYSZ 50" *
* (substitute in whatever number suits your situation for 50). *
* Note that DB2_REDUCED_OPTIMIZATION can also be specified via *
* an optimization profile rather than being set instance-wide. *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 11.5.7 *
* *
* Once you have upgraded, set the DB2_REDUCED_OPTIMIZATION *
* registry variable to "KC_KEYSZ n" where n is a number lower *
* than the number of columns in the SELECT DISTINCT column *
* list. *
* If DB2_REDUCED_OPTIMIZATION is already set, append this *
* setting to the end, separated by a comma (no spaces). *
* For example, if you already have the setting *
* DB2_REDUCED_OPTIMIZATION=YES *
* do this: *
* db2set -im DB2_REDUCED_OPTIMIZATION="YES,KC_KEYSZ 50" *
* (substitute in whatever number suits your situation for 50). *
* Note that DB2_REDUCED_OPTIMIZATION can also be specified via *
* an optimization profile rather than being set instance-wide. *
**************************************************************** |
| Comment |
First fixed in DB2 Version 11.5 Mod 5 Fix Pack 1 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.09.2021
22.11.2021
22.11.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
