DB2 - Problembeschreibung
Problem IT00513 | Status: Geschlossen |
SQL STATEMENT WITH LARGE NUMBER OF JOINS BETWEEN MULTIPLE RANGE PARTITIONED TABLES MIGHT RETURN SQL0101N ERROR | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
When compiling a complex SQL statement with a large number of range partitioned tables that join on the partition by range key columns, you might receive a SQL0101 error. For example, the following SQL statement contains a large number of joins between tables that are each partitioned by range on key column A: SELECT * FROM T1,T2,T3,T4,T5,T6,T7,T8,T9,T10, ... WHERE T1.A = T2.A and T2.A = T3.A and T3.A = T4.A and T4.A = T5.A and T5.A = T6.A and T6.A = T7.A and T7.A = T8.A and T8.A = T9.A and T9.A = T10.A and ... | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * Range partitioned tables users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to db2 Version 9.7 FixPack 10 or higher * **************************************************************** | |
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 are 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 by using an equivalent expression; for example, the predicate T1.A = T2.A can be rewritten to T1.A = (T2.A + 0) if column A is an integer. | |
Lösung | |
Fixed in Version 9.7 FixPack 10 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 24.03.2014 10.11.2014 10.11.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP10 | |
Problem behoben lt. FixList in der Version | |
9.7.0.10 |