DB2 - Problembeschreibung
Problem IC66952 | Status: Geschlossen |
SERVER OPTION DB2_SELECT_SCALAR_SQ COULD CAUSE NICKNAME QUERY WITH A SUBQUERY NOT TO BE PUSHED DOWN TO MVS SERVER | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
db2_select_scalar_sq is set to 'N' for MVS, VM, iSeries data sources by default. Federated queries involving subqueries in their select list will be blocked from being pushed down if this option was set to 'N'. E.g. sleect c1, (select max(c2) from NN) where from NN This query can not be fully pushed down to remote server if db2_select_scalar_sq is set to 'N', and hence a fully remote query plan can not be generated. In addition, this option can affect other queries's pushdownability, with a subquery in predicate of where-clause. The predicate has to be one with a constant and a subquery. E.g. select c1 from NN A where 1 > (select max(B.c2) from NN B where B.c3 = A.c3) Note that, following query can be affect by this option too, although the subquery is in predicate with a column A.c2 instead of a constant: select c1 from NN A where A.c2 = (select max(B.c2) from NN B where B.c3 = A.c3) and A.c2 < 1 The reason is that, an implicit predicate "1 > (select max(B.c2) from NN B where B.c3 = A.c3)" will be generated by DB2, deriving from the existing two predicates. The implicit predicate will then prevent the query from being pushed down. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * performance low * **************************************************************** * PROBLEM DESCRIPTION: * * none * **************************************************************** * RECOMMENDATION: * * Setting db2_select_scalar_sq to 'Y' if remote server * * supports a * * scalar subquery present in select list. * **************************************************************** | |
Local-Fix: | |
Setting db2_select_scalar_sq to 'Y' if remote server supports a scalar subquery present in select list. In fact data sources like MVS V9 support it and this option should be set to 'Y' for them. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 08.03.2010 25.09.2010 25.09.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
9.7.0.3 | |
9.7.0.3 |