|
Problem IC76120
|
Status: Closed |
COMPILING AN SQL STATEMENT THAT INVOLVES VOLATILE TABLES CAN FAIL WITH SQL0901N |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Compiling an SQL statement can fail with SQL0901N when all of
the following conditions are met.
1 The SQL statement involves at least one volatile table.
2 In the optimized SQL statement, the volatile table is joined
with other tables on equity predicates.
3 runstats has been done on the volatile table.
4 The SQL statement is compiled with Inter-Partition
Parallelism.
5 The registry variable DB2_WORKLOAD is set to SAP.
The SQL0901N error message contains the following text:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "Potential
corruption in
statistics.".) SQLSTATE=58004
The corresponding call stack can contain the following:
sqlno_prds_is_NPK_adjust_eligible
sqlno_prop_compute_cgs_join_adjust_qun
sqlno_prop_compute_cgs_join_adjustment
sqlno_prop_compute_cgs_adjustments
sqlno_prop_compute_ff_adjustments
sqlno_plan_begin_opr |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Any users with volatile tables in a query *
****************************************************************
* PROBLEM DESCRIPTION: *
* Compiling an SQL statement can fail with SQL0901N when a set *
* of specific conditions are met. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V9.7 fixpack 5 or later *
**************************************************************** |
| Local Fix: |
Alter the tables involved in the query to remove the volatile
setting.
ALTER TABLE <tabname> NOT VOLATILE |
| available fix packs: |
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 |
This problem was first fixed in V9.7 Fixpack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.05.2011
22.12.2011
22.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
