DB2 - Problembeschreibung
Problem IT03428 | Status: Geschlossen |
SQL0973N ERROR RETURNED WHEN COMPILING A STAR JOIN ELIGIBLE QUERY WITH A SNOWFLAKE THAT APPLIES A SUBQUERY PREDICATE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
DB2 might return a SQL0973N error when compiling a query, if the resulting plan is an Index-Anding Star Join Plan with a snowflake that applies a subquery predicate. This is very similar to APAR IT00300, although it will reproduce in version 10.1.0.3. If you encounter a SQL0973N error, you can identify this problem by identifying messages similar to the following in the db2diag.log: FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670 MESSAGE : ZRC=0x8031006D=-2144272275=SQLNN_CA_BUILT "SQLCA has already been built" DATA #1 : String, 51 bytes An error was detected during statement compilation. DATA #2 : String, 971 bytes Compiler error stack for rc = -2144272275: sqlnn_cmpl[380] sqlno_exe[900] sqlno_planning_phase[200] sqlno_planning_scan[400] sqlno_scan_utils_qgm[200] sqlno_each_qur[400] sqlno_top_qtb[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[200] sqlno_walk_qun[100] sqlno_each_opr[1200] sqlno_plan_end_opr[100] sqlno_gen_partitions_for_Q[300] sqlno_open_part[400] sqlno_starjn[1934] sqlno_crule_starjn_and[650] sqlno_copy_plan[1600] sqlno_plan_args_fetch::cop[60] sqlno_copy_plan_map[200] sqlno_copy_plan[1600] sqlno_plan_args_scan::copy[10] sqlno_copy_plan[1600] sqlno_plan_args_temp::copy[10] sqlno_copy_plan[1600] sqlno_plan_args_pipe::copy[10] and FUNCTION: DB2 UDB, access plan manager, sqlra_compile_var, probe:750 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -973 sqlerrml: 14 sqlerrmc: AGENT_STACK_SZ sqlerrp : SQLNO187 sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFFF6 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 and Fix Pack 5 * **************************************************************** | |
Local-Fix: | |
Use the registry setting DB2_REDUCED_OPTIMIZATION='STAR_DETECTION SEPARATE' This can be applied at the application or statement level by using a REGISTRY optimization guideline. | |
Lösung | |
Problem was first fixed in DB2 Version 10.1 and Fix Pack 5 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 28.07.2014 13.07.2015 13.07.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.5 |