DB2 - Problembeschreibung
Problem IC64145 | Status: Geschlossen |
LARGE OR PREDICATES CAN TAKE A LONG TIME AND A LOT OF MEMORY TO COMPILE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
This APAR applies to SQL queries which have all of the following conditions: 1) The WHERE clause of the SQL query applies OR operators with a large number of subterms, and each subterm is of the form (C1=literal_1 AND C2=literal_2 AND ...), where literal_1 etc. is either a literal, a host variable, a special register, or a parameter marker. 2) The OR predicate contains only local equality predicates in all of the subterms. 3) All of the subterms reference the same set of columns, and there are at least two columns referenced. 4) There is an index with leading columns matching at least two of the columns referenced, or there is column group statistics with leading columns matching at least two of the columns referenced This kind of SQL query can take a long time and a lot of memory to compile. Furthermore, when an application is compiling this kind of SQL query, the application cannot be forced off; that is, the "force application" command may not take effect. SQL example: SELECT * FROM T WHERE (C1 = '20070301' AND C2 = '01' AND C3 = 6 ) OR (C1 = '20070204' AND C2 = '05' AND C3 = 23 ) ..... ..... ..... OR (C1 = '20071212' AND C2 = '08' AND C3 = 55 ) | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All platform * **************************************************************** * PROBLEM DESCRIPTION: * * This APAR applies to SQL queries which have all of * * thefollowing conditions:1) The WHERE clause of the SQL query * * applies OR operatorswith a large number of subterms, and * * each subterm is of theform(C1=literal_1 AND C2=literal_2 AND * * ...), where literal_1etc. is either a literal, a host * * variable, a specialregister,or a parameter marker.2) The OR * * predicate contains only local equality predicatesinall of * * the subterms.3) All of the subterms reference the same set * * ofcolumns, and there are at least two columns referenced.4) * * There is an index with leading columns matchingat least two * * of the columns referenced, or there is columngroup * * statistics with leading columns matching at least twoofthe * * columns referencedThis kind of SQL query can take a long * * time and a lot ofmemoryto compile. Furthermore, when an * * application is compilingthiskind of SQL query, the * * application cannot be forced off;thatis, the "force * * application" command may not take effect.SQL example:SELECT * * * FROM TWHERE(C1 = '20070301' AND C2 = '01' AND C3 = 6 )OR * * (C1 = '20070204' AND C2 = '05' AND C3 = 23 * * )...............OR (C1 = '20071212' AND C2 = '08' AND C3 = * * 55 ) * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.5 Fix Pack 6 * **************************************************************** | |
Local-Fix: | |
Modify the OR predicate to a in-list. For example, the above SQL query can be modified as SELECT * FROM T WHERE (C1, C2, C3) IN ( VALUES ('20070301', '01', 6 ), ('20070204', '05', 23 ), ..... ..... ..... ('20071212', '08', 55 ) ) | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in DB2 Version 9.5 Fix Pack 6 | |
Workaround | |
see local fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC64200 IC64201 IC64202 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 25.10.2009 21.10.2010 21.10.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP6 | |
Problem behoben lt. FixList in der Version |