|
Problem IT07967
|
Status: Closed |
DB2 MIGHT RETURN SQL0901N WHILE COMPILING A SQL STATEMENT THAT IS ELIGIBLE FOR ZIGZAG JOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
DB2 can return SQL0901N when compiling a SQL statement that fits
the criteria for zigzag join. Refer to the topic "Ensuring that
queries fit the required criteria for the zigzag join" in DB2
InfoCentre for details about the criteria.
The db2diag.log will show the following:
MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"
DATA #1 : String, 62 bytes
An unexpected error was detected during statement compilation.
...
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 58
sqlerrmc: sqlno_get_best_plan [120]:rc( 0)
sqlerrp : SQLNN067
sqlerrd : (1) 0x803100AF (2) 0x000000AF (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFDA8 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
...
DATA #10: String, 383 bytes
Compiler error stack for rc = -2144272209:
sqlnn_cmpl[380]
sqlno_exe[900]
sqlno_planning_phase[200]
sqlno_planning_scan[400]
...
sqlno_plan_end_opr[100]
sqlno_gen_partitions_for_Q[300]
sqlno_open_part[410]
sqlno_generate_zzjn_plans[20]
sqlno_get_best_plan[120] |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW users *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 can return SQL0901N when compiling a SQL statement that *
* fits the *
* criteria for zigzag join. Refer to the topic "Ensuring that *
* queries fit the required criteria for the zigzag join" in *
* DB2 *
* InfoCentre for details about the criteria. *
* *
* The db2diag.log will show the following: *
* MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS *
* "unexpected error but state is OK" *
* DATA #1 : String, 62 bytes *
* An unexpected error was detected during statement *
* compilation. *
* ... *
* DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes *
* sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 *
* sqlerrml: 58 *
* sqlerrmc: sqlno_get_best_plan [120]:rc( 0) *
* *
* sqlerrp : SQLNN067 *
* sqlerrd : (1) 0x803100AF (2) 0x000000AF (3) *
* 0x00000000 *
* (4) 0x00000000 (5) 0xFFFFFDA8 (6) *
* 0x00000000 *
* sqlwarn : (1) (2) (3) (4) (5) *
* (6) *
* (7) (8) (9) (10) (11) *
* sqlstate: *
* ... *
* DATA #10: String, 383 bytes *
* Compiler error stack for rc = -2144272209: *
* sqlnn_cmpl[380] *
* sqlno_exe[900] *
* sqlno_planning_phase[200] *
* sqlno_planning_scan[400] *
* *
* ... *
* sqlno_plan_end_opr[100] *
* sqlno_gen_partitions_for_Q[300] *
* sqlno_open_part[410] *
* sqlno_generate_zzjn_plans[20] *
* sqlno_get_best_plan[120] *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 v10.5 fp7. *
**************************************************************** |
| Local Fix: |
Disable zigzag join: db2set -im DB2_REDUCED_OPTIMIZATION="ZZJN
OFF" |
| Solution |
Fix is provided in current db2level and applied in Future
Fixpacks. |
| Workaround |
Disable zigzag join: db2set -im DB2_REDUCED_OPTIMIZATION="ZZJN
OFF" |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.03.2015
25.01.2016
25.01.2016 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.7
|
 |
