|
Problem IC63091
|
Status: Closed |
QUERY MAY RETURN SQL0901 ERROR WITH SQLNO_ITR_PLAN::NEXT MESSAGE IN THE DB2DIAG.LOG |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A query may return an SQL0901 error in certain circumstances.
The associated error in the db2diag.log indicates that during
planning, all available access plans were pruned and there was
no remaining plan.
2009-07-21-12.59.44.867308-300 I1329601A745 LEVEL: Severe
PID : 1810706 TID : 1 PROC :
db2agent (SAMPLE) 0
INSTANCE: db2insti NODE : 000 DB : SAMPLE
APPHDL : 0-1527 APPID:
GA0AC828.OD11.06E8C1175927DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA,
136 bytes sqlcaid : SQLCA sqlcabc: 136
sqlcode: -901 sqlerrml: 58 sqlerrmc: sqlno_itr_plan::next
[300]:rc( 0) sqlerrp : SQLNO08E
sqlerrd : (1) 0x00000000 (2)
0x00000000 (3) 0x00000000
(4) 0x00000000 (5)
0xFFFFFED4 (6) 0x00000000
sqlwarn : (1) (2) (3)
(4) (5) (6) (7) (8) (9)
(10) (11) sqlstate:
The stack associated with the error may
include:
sqlnn_erds
sqlno_itr_plan::next
sqlno_crule_nljn
sqlno_crule_join_choices
sqlno_crule_join_order
sqlno_crule_join_root
sqlno_crule_join
sqlno_gen_partitions_for_QTB
sqlno_plan_end_opr
sqlno_each_opr
sqlno_walk_qun
sqlno_each_opr
sqlno_walk_qun
sqlno_each_opr
sqlno_top_qtb
sqlno_each_qur
sqlno_scan_qgm
sqlno_planning_scan
sqlno_planning_phase
sqlno_exe |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users, but rarely. *
****************************************************************
* PROBLEM DESCRIPTION: *
* A query may return an SQL0901 error in certain *
* circumstances. *
* The associated error in the db2diag.log indicates that *
* during *
* planning, all available access plans were pruned and there *
* was *
* no remaining plan. *
* *
* *
* *
* *
* *
* 2009-07-21-12.59.44.867308-300 I1329601A745 LEVEL: Severe *
* *
* PID : 1810706 TID : 1 PROC : *
* *
* db2agent (SAMPLE) 0 *
* *
* INSTANCE: db2insti NODE : 000 DB : SAMPLE *
* *
* APPHDL : 0-1527 APPID: *
* *
* GA0AC828.OD11.06E8C1175927DATA #1 : SQLCA, *
* PD_DB2_TYPE_SQLCA, *
* 136 bytes sqlcaid : SQLCA sqlcabc: 136 *
* *
* sqlcode: -901 sqlerrml: 58 sqlerrmc: sqlno_itr_plan::next *
* *
* [300]:rc( 0) sqlerrp : SQLNO08E *
* sqlerrd : (1) 0x00000000 (2) *
* *
* 0x00000000 (3) 0x00000000 *
* *
* (4) 0x00000000 (5) *
* *
* 0xFFFFFED4 (6) 0x00000000 *
* *
* sqlwarn : (1) (2) (3) *
* *
* (4) (5) (6) (7) (8) (9) *
* (10) (11) sqlstate: *
* *
* *
* *
* *
* *
* *
* *
* The stack associated with the error may *
* *
* include: *
* *
* sqlnn_erds *
* *
* sqlno_itr_plan::next *
* *
* sqlno_crule_nljn *
* *
* sqlno_crule_join_choices *
* *
* sqlno_crule_join_order *
* *
* sqlno_crule_join_root *
* *
* sqlno_crule_join *
* *
* sqlno_gen_partitions_for_QTB *
* *
* sqlno_plan_end_opr *
* *
* sqlno_each_opr *
* *
* sqlno_walk_qun *
* *
* sqlno_each_opr *
* *
* sqlno_walk_qun *
* *
* sqlno_each_opr *
* *
* sqlno_top_qtb *
* *
* sqlno_each_qur *
* *
* sqlno_scan_qgm *
* *
* sqlno_planning_scan *
* *
* sqlno_planning_phase *
* *
* sqlno_exe *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 3 or later. *
**************************************************************** |
| Local Fix: |
Setting the following registry variable may avoid the error:
db2set DB2_REDUCED_OPTIMIZATION=ON |
| available fix packs: |
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 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 9a 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 |
Problem was first fixed in Version 9.7 Fix Pack 3. |
| Workaround |
Setting the following registry variable may avoid the error:
db2set DB2_REDUCED_OPTIMIZATION=ON |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.09.2009
20.09.2010
20.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
| 9.7.0.3
|
|
| 9.7.0.3
|
|
