DB2 FOR LUW / DB2FORLUW / A10 - DB2

When DB2 chooses to generate an index-anding star join plan for 
a query, and one of the snowflakes contains a recursive 
subquery, the query compile can fail, and SQL0973N is returned: 
 
SQL0973N  Not enough storage is available in the 
"AGENT_STACK_SZ" heap or stack to process the statement. 
 
If db2pdcfg -catch -973 is used, the following stack will show 
up: 
 
CALLSTCK: (Static functions may not be resolved correctly, as 
they are resolved to the nearest symbol) 
  [0] 0x090000000F5BE130 pdLogPrintf + 0x60 
  [1] 0x090000000E11C5D0 pdLogPrintf@glue139 + 0x68 
  [2] 0x090000000FCF8600 pdInvokeCatchInterface + 0x148 
  [3] 0x090000000F813B48 
sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x80 
  [4] 0x090000000F817390 sqlnn_erds__FiN41e + 0x134 
  [5] 0x090000000F243A44 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator + 0x3B4 
  [6] 0x09000000112CEB28 
copy_plan_args__21sqlno_plan_args_unionFP13sqlno_globalsP9sqlno_ 
qtbP10sqlno_apcbCUiP21sqlno_plan_args_union + 0x110 
  [7] 0x090000001132F730 
sqlno_copy_function_args__FCP13sqlno_globalsCP9sqlno_qtbCP10sqln 
o_apcbCUiP19sqlno_plan_functionT5 + 0x30C 
  [8] 0x090000000F243740 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator + 0xB0 
  [9] 0x090000000EF19808 
copy_plan_args__20sqlno_plan_args_mateFP13sqlno_globalsP9sqlno_q 
tbP10sqlno_apcbCUiP20sqlno_plan_args_mate + 0xCC 
  [10] 0x090000000F2443E8 
sqlno_copy_function_args__FCP13sqlno_globalsCP9sqlno_qtbCP10sqln 
o_apcbCUiP19sqlno_plan_functionT5 + 0x108 
  [11] 0x090000000F243740 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator + 0xB0 
  [12] 0x090000001386E5CC 
copy_plan_args__20sqlno_plan_args_tempFP13sqlno_globalsP9sqlno_q 
tbP10sqlno_apcbCUiP20sqlno_plan_args_temp + 0x17C 
  [13] 0x090000001132F6B8 
sqlno_copy_function_args__FCP13sqlno_globalsCP9sqlno_qtbCP10sqln 
o_apcbCUiP19sqlno_plan_functionT5 + 0x294 
  [14] 0x090000000F243740 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator + 0xB0 
  [15] 0x090000000E12EFFC 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator@glue49B + 0x40 
  [16] 0x09000000113304E8 
copy_plan_args__20sqlno_plan_args_pipeFP13sqlno_globalsP9sqlno_q 
tbP10sqlno_apcbCUiP20sqlno_plan_args_pipe + 0x14 
  [17] 0x090000000F2445E4 
sqlno_copy_function_args__FCP13sqlno_globalsCP9sqlno_qtbCP10sqln 
o_apcbCUiP19sqlno_plan_functionT5 + 0x18 
  [18] 0x090000000F243740 
sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq 
lno_apcbCUiPP19sqlno_plan_operator 
 
Please upgrade to Fixpack 5 which includes this fix, not Fixpack 
3.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 3                       * 
****************************************************************

Create an optprofile with a regvar setting. Use the following 
steps: 
 
(1) create optimization profile table if it does not already 
exist 
 
db2 "call sysinstallobjects('opt_profiles', 'c', '', '')" 
 
(2) use an editor to create a file, name it, say, profile.xml, 
which has the following content: 
 
<?xml version="1.0" encoding="UTF-8"?> 
<OPTPROFILE> 
   <OPTGUIDELINES> 
      <REGISTRY> 
         <OPTION NAME='DB2_REDUCED_OPTIMIZATION' 
VALUE='STAR_DETECTION SEPARATE'/> 
      </REGISTRY> 
   </OPTGUIDELINES> 
</OPTPROFILE> 
 
(3) create another file, name it, say, ins.del, which has the 
following content 
 
"MY_SCHEMA","STAR_SEPARATE","profile.xml" 
 
Note that the first field is the profile schema, and the second 
field is the profile name. You can use any names. The third 
field is the name of the file you created in step (2). 
 
(4) insert the profile into profile table 
 
db2 "IMPORT FROM ins.del OF DEL MODIFIED BY LOBSINFILE INSERT 
INTO SYSTOOLS.OPT_PROFILE" 
 
(5) use the profile in your application. For example, if you 
want to create a stored procedure 
 
db2 "CALL SYSPROC.SET_ROUTINE_OPTS('OPTPROFILE 
MY_SCHEMA.STAR_SEPARATE')" 
 
Now go ahead and create the stored procedure.

Fixed in DB2 version 10.1 Fix Pack 3

See Local Fix

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

13.03.2014
16.06.2014
17.11.2015