DB2 - Problem description
Problem IT21240 | Status: Closed |
SQL0083C - MEMORY ALLOCATION ERROR WHEN SAME COLUMN IN LOCAL ANDIN PREDICATE IN CASE STATEMENT | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
Compiling the query returns error SQL0083C. ------ ---------------------------------------------------------------- ---- SQL0060W The "C" precompiler is in progress. SQL0083C A memory allocation error has occurred. SQL0095N No bind file was created because of previous errors. SQL0091W Precompilation or binding was ended with "2" errors and "0" warnings. The following message is logged when it happens: <timestamp> I818584A5704 LEVEL: Warning PID : 12345678 TID : 12345 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : DBNAME APPHDL : 0-12345 APPID: <APPID> AUTHID : USER1 HOSTNAME: host1 EDUID : 12345 EDUNAME: db2agent (DBNAME) 0 FUNCTION: DB2 UDB, SQO Memory Management, sqlogmblkEx, probe:1000 MESSAGE : ZRC=0x820F0004=-2112946172=SQLO_MEM_SIZE "Mem Mgt invalid size" DIA8563C An invalid memory size was requested. DATA #1 : String, 43 bytes Memory management block allocation failure. DATA #2 : Codepath, 8 bytes 2 DATA #3 : Memory pool handle pointer, PD_TYPE_MEM_POOL_HANDLE_PTR, 8 bytes 0x0a000100261d8ce8 DATA #4 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 8 bytes 0 DATA #5 : Adjusted block size, PD_TYPE_MEM_ADJUSTED_SIZE, 8 bytes 0 DATA #6 : Options for requested block, PD_TYPE_GET_MEM_OPTIONS, 4 bytes 0x00000000 DATA #7 : File name, PD_TYPE_OSS_MEM_FILE_NAME, 11 bytes sqlng_opr.C DATA #8 : Line of code, PD_TYPE_OSS_MEM_LINE_NUM, 8 bytes 25068 DATA #9 : Resource binding pointer, PD_TYPE_RESOURCE_BINDING_PTR, 8 bytes 0x0000000000000000 CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol) [0] 0x090000006301CD9C sqloGetMemoryBlockExtended + 0x41C [1] 0x0900000068573CB4 sqlngBuildInSetBinaryOp__FP9sqlng_blkPP12sqlri_opparmP9sqlnq_pid b + 0x154 [2] 0x0900000068573988 sqlngBuildInList__FP9sqlng_blkPP12sqlri_opparmP9sqlnq_pid + 0xC8 [3] 0x09000000685AAD60 sqlng_process_parse_tree_node__FP9sqlng_blkP9sqlnq_pid + 0x1060 [4] 0x09000000685A9A00 sqlng_process_BF_node__FP9sqlng_blkP9sqlnq_pid + 0x140 [5] 0x09000000685AA720 sqlng_process_parse_tree_node__FP9sqlng_blkP9sqlnq_pid + 0xA20 [6] 0x09000000685A9A00 sqlng_process_BF_node__FP9sqlng_blkP9sqlnq_pid + 0x140 [7] 0x09000000685A8B5C sqlng_walk_BF_chain__FP9sqlng_blk + 0xCDC [8] 0x09000000650552AC sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x12CC [9] 0x090000006855D6D0 sqlng_build_CASE_op__FP9sqlng_blkP9sqlnq_pid + 0x630 [10] 0x09000000685AA858 sqlng_process_parse_tree_node__FP9sqlng_blkP9sqlnq_pid + 0xB58 [11] 0x09000000685A9A00 sqlng_process_BF_node__FP9sqlng_blkP9sqlnq_pid + 0x140 [12] 0x09000000685A8B5C sqlng_walk_BF_chain__FP9sqlng_blk + 0xCDC [13] 0x09000000650552AC sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x12CC [14] 0x09000000685482BC sqlng_build_TA_op__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_qtb + 0x27BC [15] 0x09000000685D7D14 sqlng_process_f_iscan__FP9sqlng_blkP19sqlno_plan_operator + 0xC94 [16] 0x09000000685D6F3C sqlng_process_fetch_op__FP9sqlng_blkP19sqlno_plan_operator + 0xD3C [17] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [18] 0x0900000065054FB0 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xFD0 [19] 0x090000006864ED40 sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1CA0 [20] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [21] 0x0900000065054FB0 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xFD0 [22] 0x0900000068650990 sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator + 0x38F0 [23] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [24] 0x0900000065054FB0 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xFD0 [25] 0x0900000068650990 sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator + 0x38F0 [26] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [27] 0x0900000065054FB0 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xFD0 [28] 0x0900000068650990 sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator + 0x38F0 [29] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [30] 0x09000000685CA85C sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator + 0x59C [31] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [32] 0x09000000685C9DA4 sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator + 0x6A4 [33] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [34] 0x0900000068584630 sqlng_process_groupby_op__FP9sqlng_blkP19sqlno_plan_operator + 0x710 [35] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [36] 0x09000000685CA85C sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator + 0x59C [37] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [38] 0x09000000685C9DA4 sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator + 0x6A4 [39] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [40] 0x09000000685CA85C sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator + 0x59C [41] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [42] 0x09000000685C9DA4 sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator + 0x6A4 [43] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [44] 0x0900000065054FB0 sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xFD0 [45] 0x09000000685597D0 sqlng_build_SET_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1050 [46] 0x09000000685E1874 sqlng_process_set_op__FP9sqlng_blkP19sqlno_plan_operator + 0x1F4 [47] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC [48] 0x09000000685CA85C sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator + 0x59C [49] 0x090000006853008C sqlngProcessLolepop__FP9sqlng_blkP19sqlno_plan_operator + 0x1CC The query has the following conditions to hit the issue: 1) The query has CASE statement 2) CASE statement has IN predicate 3) Column used in IN predicate is also used in a local predicate Example: SELECT 1 AS col FROM (SELECT A.c1 FROM A, B WHERE A.c2= 'x' AND B.c1 = '610301' AND B.c2 <= CASE WHEN (B.c3 = '039101' OR B.c1 IN ('610201','610401')) THEN A.c2 ELSE B.c2 END ) X | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher * **************************************************************** | |
Local Fix: | |
available fix packs: | |
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows | |
Solution | |
First fixed in Db2 11.1 Mod 3 Fix Pack 3 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 29.06.2017 19.03.2018 06.12.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |