DB2 - Problembeschreibung
Problem IT08665 | Status: Geschlossen |
COMPILING SQL STATEMENT WITH JOINS AND OR PREDICATES ON PARTITIONED TABLES MIGHT RETURN SQL0101N ERROR | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
When compiling a SQL statement that includes joins with one or more range partitioned tables and includes multiple OR, or equivalent IN, predicates that references the partition by range key in a subset of the subterms, you might receive a SQL0101 error. You are more likely to encounter this error if the OR predicates contain a large number of subterms and the IN predicates contain a large list of values. The following is an example of an SQL statement that includes a join with a table, RP, that is partitioned by range on key column A and multiple IN and OR predicates that references it: SELECT * FROM RP, T1, ... WHERE T1.X = RP.X AND RP.A IN (1,2,3,4,5,6,...) AND (RP.A = 1 OR T1.A = 1 OR ...) AND (RP.A = 2 OR T1.A = 2 OR ...) AND ... | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * all editions * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to version 10.5 Fix Pack 7 * **************************************************************** | |
Local-Fix: | |
You can increase the database STMTHEAP value or set it to AUTOMATIC to avoid the SQL0101N error, but if there are other limits, such as INSTANCE_MEMORY, causing the SQL0101N error to persist, you can try any of the following solutions to limit the complexity of the query: 1. evaluate whether any of the joins or IN/OR predicatesare redundant or unnecessary, and if so, remove them from the query; or 2. reduce the query optimization class to 0, 1, or 2, which will limit optimizations performed and reduce the memory requirements; or 3. rewrite the join predicates or OR predicate subterms that reference the partition by range key column by using an equivalent expression; for example, the subterm RP.A = 1 in the example SQL statement can be rewritten to (RP.A + 0) = 1 since column A is an integer. | |
Lösung | |
First fixed in version 10.5 Fix Pack 7 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 01.05.2015 20.01.2016 20.01.2016 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.7 |