DB2 FOR LUW / DB2FORLUW / 970 - DB2

When the result of a SCALAR User Defined Function (UDF), scalar 
sub-select, or subquery with a "FETCH FIRST 1 ROW ONLY" (FF1RO) 
clause specified, is used in a predicate,  the optimizer may 
incorrectly compute the filter factor, and cardinality that 
results from applying the predicate. 
 
There are two instances that this can occur in: 
1) Scalar UDF with begin atomic/end atomic. In this case, due to 
the way in which the UDF is expanded, and incorporated into the 
query access plan, the optimizer may estimate that any predicate 
will filter away the entire data stream. 
 
2) Scalar UDF without begin atomic/end atomic, scalar 
sub-select, or a subquery with a FF1RO clause. In this case, the 
optimizer may treat the predicate as a base table join, which 
may lead to a filter factor that is too small, and lead the 
optimizer to believe the predicate is more filtering then it 
really will be. 
 
In either of these cases, the low cardinality that results from 
applying the predicate or predicates can lead the optimizer to 
generate a suboptimial query access plan.

SCALAR UDF RESULT USED IN A PREDICATE MAY RESULT IN INCORR 
 
CARDINALITY ESTIMATE. The defect for that APAR (wsdbu00536316) 
shows as having shipped in v91fp8.

Workarounds: 
There are a number of work arounds that may get around this 
problem: 
1) If the UDF uses begin atomic/end atomic, but is only a single 
query, it is possible to remove the begin atomic/end atomic. 
This may result in a better query access plan 
2) Include the result of the UDF, scalar sub-select, or subquery 
(if uncorrelated) in a Common Table Expression. For example: 
 Example DDL: 
   create table t1(c1 int, c2 int, c3 int); 
   create table t2(d1 int); 
   create function udf() returns int language sql reads sql data 
return select d1 from t2 fetch first 1 row only; 
 Example Original Query: 
   select c1, c2, c3 from t1 where c1 = udf(); 
 Example Modified Query: 
   WITH UDF_RESULT(result) AS (VALUES udf()) 
   select c1, c2, c3 from t1, udf_result where c1 = result; 
3) If multiple UDFs are used, include multiple results in the 
Common Table Expression 
 
Options 2 and 3 are most likely to be of benefit when there are 
multiple UDFs, scalar sub-selects or subqueries with a FF1RO 
clause, or the UDF(s), scalar sub-select(s) or subqueries with a 
FF1RO clause are used multiple times in the query, and will 
return the same result for each invocation.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

There are a number of work arounds that may get around this 
 
problem: 
1) If the UDF uses begin atomic/end atomic, but is only a single 
query, it is possible to remove the begin atomic/end atomic. 
This may result in a better query access plan 
2) Include the result of the UDF, scalar sub-select, or subquery 
(if uncorrelated) in a Common Table Expression. For example: 
 Example DDL: 
   create table t1(c1 int, c2 int, c3 int); 
   create table t2(d1 int); 
   create function udf() returns int language sql reads sql data 
return select d1 from t2 fetch first 1 row only; 
 Example Original Query: 
   select c1, c2, c3 from t1 where c1 = udf(); 
 Example Modified Query: 
   WITH UDF_RESULT(result) AS (VALUES udf()) 
   select c1, c2, c3 from t1, udf_result where c1 = result; 
3) If multiple UDFs are used, include multiple results in the 
Common Table Expression 
 
Options 2 and 3 are most likely to be of benefit when there are 
multiple UDFs, scalar sub-selects or subqueries with a FF1RO 
clause, or the UDF(s), scalar sub-select(s) or subqueries with a 
FF1RO clause are used multiple times in the query, and will 
return the same result for each invocation.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.10.2009
28.12.2009
28.12.2009