888-685-3101, ext. 2

ORA-01013 when Accessing Oracle 11gR2 from a Federated DB2 Database

by | Feb 25, 2015 | Blog, Data Management, Databases

Problem Description

A working federation environment – querying an 11gR2 Oracle database from a DB2 10.5 FP4 database – suddenly started returning error ORA-01013.  Four minutes into each statement, the following error was returned:

SQL1822N  Unexpected error code "1013" received from data source 
"SRCSRV_ORANODE". Associated text and tokens are "ORA-01013: user requested 
cancel of current operat".  SQLSTATE=560BD

As far as we could tell, nothing had changed on either side. Multiple tables, when queried started to return this error at the same time.

Problem Resolution

Upon investigation, we found this technote:

http://www-01.ibm.com/support/docview.wss?uid=swg21499426

The issue is due to a bug 12877221 introduced with Oracle 11gR2. It has to do with how Oracle handles SIGINT for DB2 edus. From the problem description, it sounds like we were lucky to only see queries and load processes fail – the issue can cause DB2 to crash.

Three of the suggested actions relate to the Oracle server. Hopefully Oracle will fix this bug soon. The fourth recommended workaround is to change the wrapper to run as fenced instead of unfenced. I first collected the name of the Oracle wrapper in the DB2 database by querying SYSIBM.SERVERS. I then verified that the wrapper was indeed unfenced using this syntax:

select 	substr(wrapname,1,20) as wrapname
		, substr(option,1,30) as option
		, substr(setting,1,12) as setting 
	from syscat.wrapoptions 
	where wrapname='NET8'
	with ur

WRAPNAME             OPTION                         SETTING
-------------------- ------------------------------ ------------
NET8                 DB2_FENCED                     N

  1 record(s) selected.

After verifying this, I updated the wrapper to instead run fenced using:

ALTER WRAPPER NET8 OPTIONS (SET DB2_FENCED 'Y')

 

And verification showed that the change was successful:

 

WRAPNAME             OPTION                         SETTING
-------------------- ------------------------------ ------------
NET8                 DB2_FENCED                     Y

  1 record(s) selected.

 

After this change was made, the issue immediately went away, and the problem was resolved. I did see a DB2 APAR out there for a memory leak on DB2 10.5 fixpack 2 and earlier when repetitive inserts and updates are being done – this environment is at 10.5 fixpack 4, and they also do not insert/update through the nicknames. http://www-01.ibm.com/support/docview.wss?uid=swg1IC95319