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. |
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. |
