IBM Support

IC95319: FENCED ORACLE WRAPPER LEAKS MEMORY WHEN ITERATIVELY EXECUTES INSERT/UPDATE STATEMENTS INVOLVING A LOB COLUMN

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When some INSERT/UPDATE statements involving a LOB column are
    executed against Oracle nicknames iteratively, *Fenced* Oracle
    Wrapper may leak memory.
    
    This memory leak usually happens in a Q Replication
    scenario(target is Oracle):
    1. QApply program tries to INSERT/UPDATE/DELETE target table in
    Oracle,
    2. But the INSERT/UPDATE/DELETE operation fails (because of
    conflicting key on an INSERT, or NOTFOUND on a DELETE/UPDATE),
    3. So QApply program inserts the IBMQREP_EXCEPTIONS table with
    the SQL code and other information for row changes that could
    not be applied.
    4. QApply program continues to INSERT/UPDATE/DELETE target table
    in Oracle.
    
    NOTE: 1. The memory leak happens at 3. Normally the memory leak
    is not outstanding since QApply program does not have to inserts
    the IBMQREP_EXCEPTIONS table so frequently.
          2. Although happens on the same case, but this issue is
    different than JR44859. JR44859 happens on both fenced and
    unfenced Oracle wrappers, while this issue only happens on a
    fenced Oracle wrapper.
    
    You can reproduce memory leak by following steps:
    
    1. Create the federated objects and store procedure:
    drop wrapper net8@
    create wrapper net8 options (db2_fenced 'Y')@
    create server serv type oracle version '11g' wrapper net8
    options(node 'ORANODE1',VARCHAR_NO_TRAILING_BLANKS 'Y')@
    create user mapping for db2inst1 server serv
    options(remote_authid '<USERID>',remote_password '<PASSWORD>')@
    SET PASSTHRU serv@
    DROP TABLE TEST1@
    DROP TABLE TEST2@
    create table test1 (c1 varchar(10), c2 clob)@
    create table test2 (c1 varchar(10), c2 clob)@
    SET PASSTHRU RESET@
    
    create nickname tm1 for serv."J15USER1"."TEST1"@
    create nickname tm2 for serv."J15USER1"."TEST2"@
    drop table test1@
    create table test1 (c1 varchar(10),c2 clob)@
    insert into test1 values('111','222')@
    
    DROP PROCEDURE TP@
    CREATE PROCEDURE TP()
      SPECIFIC TP
      LANGUAGE SQL
      NOT DETERMINISTIC
      EXTERNAL ACTION
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      INHERIT SPECIAL REGISTERS
    BEGIN
    
    DECLARE C1V   VARCHAR(10);
    DECLARE C2V   CLOB(32768);
    DECLARE DV   VARCHAR(10);
    DECLARE FLAG     INTEGER;
    
    declare cur cursor with hold for select * FROM test1 fetch first
    1 rows only;
         open cur;
         fetch cur into C1V,C2V;
         close cur;
         COMMIT;
    
    SET FLAG=0;
    SET DV='AAA';
    
    LOOP_IUD: LOOP
    
    DELETE FROM TM1 WHERE C1=DV;
    INSERT INTO TM2 VALUES(C1V,C2V);
    
    SET FLAG = FLAG + 1;
      IF (FLAG = 1000) THEN
            LEAVE LOOP_IUD;
      END IF;
    END LOOP LOOP_IUD;
    COMMIT;
    END@
    
    2. Run the store procedure and record the memory usage of the
    federated fmp process:
    
    console 1           console 2
    ------------------- ------------------------------
    
    delete from tm2
                        db2pd -fmp (find the fmp_pid)
    run #1 call tp()
                        ps -elf|grep fmp_pid(record the memory
                        usage)
    run #2 call  tp()
                        ps -elf|grep fmp_pid(record the memory
                        usage)
    
    Run# FMP memory usage  Memory leak
    ---- ----------------  -----------
    1    6546
    2    6600                 54
    3    6664                 64
    4    6724                 60
    5    6788                 64
    

Local fix

  • Alter the wrapper to use unfenced wrapper:
    
    ALTER WRAPPER <WRAPPER NAME> OPTIONS(SET DB2_FENCED 'N')
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * The users are using fenced Oracle wrapper iteratively        *
    * executes insert/update statements which has a lob column.    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Federation Server V10.5FP3.                       *
    ****************************************************************
    

Problem conclusion

  • The problem is firstly fixed in Federation Server V10.5FP3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC95319

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-08-27

  • Closed date

    2014-02-27

  • Last modified date

    2014-02-27

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

    JR46259

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA50 PSN

       UP

  • RA50 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.5

Reference #: IC95319

Modified date: 27 February 2014