|
Problem IC64035
|
Status: Closed |
QUERRIES ON NICKNAMES WITH FETCH FIRST N ROWS ONLY CLAUSE MIGHT RETURN ERROR SQL1822N OR WITH POOR PERFORMANCE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
If you have a query on nickanme which uses the FETCH FIRST n
ROWS ONLY clause and the remote table for the nickname has huge
data, the query might return error SQL1822N or with very poor
performance for some datasources. But the same query would run
quite well in passthru mode.
For example,below is an error message reported by this query for
a DB2/zOS datasource:
SQL1822N Unexpected error code "57051" received from data
source "MYSOURCE". Associated text and tokens are
"func="do_prep" msg=" SQL0495N Estimated processor cost o".
SQLSTATE=560BD
And in the db2diag.log, you may find the detail error returned
from the datasource like follow:
[IBM][CLI Driver][DB2] SQL0495N Estimated processor cost of
"000000000324" processor seconds ("000007529919" service units)
in cost category "A" exceeds a resource limit error threshold of
"000003000003" service units. SQLSTATE=57051 |
| Problem Summary: |
|
| Local Fix: |
If the same query can succeeded in passthru mode, you can set
the federation server option db2_fetch_n_rows to 'Y' by issuing
the statement "alter server SERVER_NAME options(add
db2_fetch_n_rows 'Y')".
Note: Not all data sources support to set this option to 'Y', if
the query failed after setting the option please contact DB2
support. |
| available fix packs: |
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
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
23.11.2009
23.11.2009 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
