IBM Support

IC68699: MEMORY LEAK WHILE CALLING NESTED STORED PROCEDURES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • we see an application failing due to unavailable memory.
    Allocating more
    memory does not help - the application consumes everything and
    fails.
    
    
    SQL/PL stored procedure 1 calls SQL/PL sp 2 which calls SQL/PL
    sp 3.
    sp3 opens a cursor fetches a row and returns to sp2 which then
    returns
    to sp1. sp1 iterates several hundred thousand times.
    
    Monitoring we see memory consumed by the agent increasing
    continually.
    
    
    However, if we instead call sp3 from sp1 the problem does not
    occur  -
    memory consumption is constant.
    
    Also, changing the cursor to a select ... fetch first 1 row
    avoids the
    problem.
    
    Steps to reproduce the issue :
    
    create procedure three
    begin
      declare N integer;
    
      declare C1 cursor for
        select 42 from sysibm.sysdummy1;
    
      open C1;
      fetch C1 into N;
      close C1;
    end %
    
    create procedure two(in iterations integer)
    begin
      declare i integer default 0;
    
      while i < iterations do
        call three;
        set i = i + 1;
      end while;
    end %
    
    create procedure one
    begin
      call two(500000);
    end %
    
    connect reset %
    terminate %
    
    If you 'db2 call one', you get the memory leak.
    

Local fix

  • if we instead call sp3 from sp1 the problem does not occur  -
    memory consumption is constant.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 v9.7 on all platform                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Memory leak when calling nested stored procedures            *
    *                                                              *
    * When SQL/PL Stored Procedure 1 calls SQL/PL Stored Procedure *
    * 2 which calls SQL/PL Stored Procedures.                      *
    *                                                              *
    * If Stored procedures 3  opens a cursor which fetches a row   *
    * and returns to Stored Procedure 2,  which then               *
    * returns to Stored procedure 3, a small memory leak will      *
    * occurs.                                                      *
    *                                                              *
    * Memory consumed by the agent will increase continuously if   *
    * the Stored procedures are repeated being executed            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v9.7 Fp3                                      *
    ****************************************************************
    

Problem conclusion

  • Upgrade to DB2 v9.7 Fp3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC68699

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-18

  • Closed date

    2010-09-29

  • Last modified date

    2010-09-29

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

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

    IC69628

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU029","label":"Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7"}]

Document Information

Modified date:
10 September 2020