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

DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 10 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 11 for Linux, UNIX, and Windows

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 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