Fixes are available
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 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
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
13 January 2022