DB2 FOR LUW / DB2FORLUW / A10 - DB2

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:

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 and Fix Pack 5                   * 
****************************************************************

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.

Problem was first fixed in DB2 Version 10.1 and Fix Pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.07.2014
13.07.2015
13.07.2015