DB2 - Problembeschreibung
Problem IT00300 | Status: Geschlossen |
DB2 CAN RETURN SQL0973N WHEN GENERATING AN INDEX-ANDING STAR JOIN PLAN THAT CONTAINS A RECURSIVE SUBQUERY | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
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. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.1 Fix Pack 3 * **************************************************************** | |
Local-Fix: | |
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. | |
Lösung | |
Fixed in DB2 version 10.1 Fix Pack 3 | |
Workaround | |
See Local Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 13.03.2014 16.06.2014 17.11.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.5 |