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 |
