IBM Support

IT21240: SQL0083C - MEMORY ALLOCATION ERROR WHEN SAME COLUMN IN LOCAL ANDIN PREDICATE IN CASE STATEMENT

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher               *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 3 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT21240

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-06-29

  • Closed date

    2018-03-19

  • Last modified date

    2018-12-06

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    PH06245

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 December 2018