DB2 - Problem description
Problem IT22962 | Status: Closed |
CREATED FEDERARED PROCEDURE FOR A SP WITH NO ARGUMENTS IN ORACLEDATABASE MAY FAIL WITH -901 | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Create federated store procedure for a SP with no arguments in Oracle database may fail with -901: 2017-09-20-13.16.07.188754+480 E66394A1654 LEVEL: Info (Origin) PID : 16711756 TID : 3600 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMPLE APPHDL : 0-8 APPID: *LOCAL.db2inst1.170920051147 AUTHID : DB2INST1 HOSTNAME: myhost EDUID : 3600 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 MESSAGE : ZRC=0x80260160=-2144992928=SQLQG_ERROR "Error constant for gateway." DATA #1 : String, 62 bytes An unexpected error was detected during statement compilation. DATA #2 : Boolean, 1 bytes false DATA #3 : Boolean, 1 bytes false DATA #4 : Boolean, 1 bytes true DATA #5 : Boolean, 1 bytes false DATA #6 : Hex integer, 4 bytes 0x00000000 DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 0 sqlerrmc: sqlerrp : SQLNN067 sqlerrd : (1) 0x80260160 (2) 0x00000160 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFDA8 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: DATA #8 : Hex integer, 4 bytes 0x00000040 DATA #9 : String with size, 165 bytes CREATE OR REPLACE PROCEDURE ... DATA #10: String, 171 bytes Compiler error stack for rc = -2144992928: sqlnn_cmpl[300] sqlnp_main[250] sqlnp_parser[510] sqlnp_smactn[100] sqlnq_create_fedproc_stmt[40] sqlnq_create_fedproc_one[130] The root cause is that Federation Server uses a SQL against sys.all_arguments incorrectly to verify if a particular procedure exists - after bugfix 14675058 of Oracle, the SQL always returns empty result indicating the SP doesn't exist. Oracle changed this behavior starting from 12.1.0.2 release and this change doesn't affect procedures without package, so this problem only happens with data source Oracle 12.1.0.2 or later, and this problem only happens when the procedure in the source database is within a package. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * The user is running oracle wrapper. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 V10.5 FP10 or later. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT22979 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 29.10.2017 11.07.2018 11.07.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |