home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC95319 Status: Geschlossen

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

Produkt:
DB2 FOR LUW / DB2FORLUW / A50 - DB2
Problembeschreibung:
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
Problem-Zusammenfassung:
**************************************************************** 
* 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.                       * 
****************************************************************
Local-Fix:
Alter the wrapper to use unfenced wrapper: 
 
ALTER WRAPPER <WRAPPER NAME> OPTIONS(SET DB2_FENCED 'N')
verfügbare FixPacks:
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

Lösung
The problem is firstly fixed in Federation Server V10.5FP3.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
27.08.2013
27.02.2014
27.02.2014
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.5.0.3 FixList
10.5.0.3 FixList