|
Problem IC66952
|
Status: Closed |
SERVER OPTION DB2_SELECT_SCALAR_SQ COULD CAUSE NICKNAME QUERY WITH A SUBQUERY NOT TO BE PUSHED DOWN TO MVS SERVER |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.03.2010
25.09.2010
25.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
