|
Problem IC69569
|
Status: Closed |
SELECT QUERIES USING UDF IN CONJUNCTION WITH ORDER BY DO NOT PARALLELIZE WELL IN DPF |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Select queries which use external scalar UDF in conjunction with
the ORDER BY clause do not parallelize well in DPF and lead to
bad performance.
To benefit from the optimization the following conditions must
be satisfied:
1. The environment must be DPF.
2. There must be a order by clause declared on the outer most
select.
3. The select must have a reference to an external scalar UDF.
4. The UDF must not be non-deterministic, perform external
action or have a scratchpad.
eg.
select CD_DEMO_SK, PROMO(CD_DEP_COUNT,CD_PURCHASE_ESTIMATE) as
UDF
from T1
ORDER BY CD_DEMO_SK;
In the SQL example above "CD_DEMO_SK" is Primary Key and PROMO
is an external UDF. |
| Problem Summary: |
|
| Local Fix: |
rewrite the above query as:
select temp.CD_DEMO_SK, temp.UDF FROM (
select CD_DEMO_SK,
PROMO(CD_DEP_COUNT,CD_PURCHASE_ESTIMATE) as UDF from T1) as temp
ORDER BY temp.CD_DEMO_SK; |
| 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 |
| Comment |
N/A |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 30.06.2010
23.09.2010
23.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
|
|
