IBM Support

How to call an Oracle stored procedure by using Db2 Federation

Question & Answer


Question

How to call an Oracle stored procedure by using Db2 Federation

Answer

Before you create a federated procedure for a remote stored procedure in Oracle, you need to connect to Oracle via a DB2 Federated Database.  Refer to How does Federation Server connect to Oracle data source by using Instant Client?
For example,
1. Modify db2dj.ini in sqllib/cfg and add TNS_ADMIN, ORACLE_HOME, DB2LIBPATH.
(haijs@db2i-haijs-tb6th-x86) /home/haijs/sqllib/cfg
$ cat db2dj.ini
ORACLE_HOME=/home/haijs/fed_oracle/client_oracle
TNS_ADMIN=/home/haijs/bin
LIBPATH=/home/haijs/fed_oracle/client_oracle/lib:/home/haijs/sqllib/lib:
DB2LIBPATH=/home/haijs/fed_oracle/client_oracle/lib:/home/haijs/sqllib/lib:
LD_LIBRARY_PATH=/home/haijs/sqllib/lib
SHLIB_PATH=/home/haijs/sqllib/lib

DJX_ODBC_LIBRARY_PATH=/home/haijs/sqllib/federation/odbc/lib:
DB2_FED_LIBPATH=/home/haijs/sqllib/federation/odbc/lib:/home/haijs/sqllib/federation/netezza/lib64:
ODBCINST=/home/haijs/sqllib/cfg/odbcinst.ini
NZ_ODBC_INI_PATH=/home/haijs/sqllib/cfg
NLS_LANG=American_America.UTF8
2. Check tnsnames.ora configuration.
(haijs@db2i-haijs-tb6th-x86) /home/haijs/sqllib/cfg
$ cat /home/haijs/bin/tnsnames.ora
ora12cfyre =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test1.fyre.ibm.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb.fyre.ibm.com)
    )
  )
ora12ccsdl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9.123.139.66)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb.cn.ibm.com)
    )
  )
3. Create wrapper, server, user mapping, and federated procedure.
update dbm cfg using federated yes;

db2stop force;
db2start;

connect to testdbu;

drop wrapper "WRAPPER1";
drop server "SERVER1";

CREATE WRAPPER "WRAPPER1" LIBRARY 'libdb2net8.so' OPTIONS(DB2_FENCED 'N');

create server "SERVER1" type oracle version 12.0 wrapper "WRAPPER1" 
authorization "J15USER1"  password "J15USER1" options (node 'ora12cfyre');

create user mapping for user server "SERVER1" options ( REMOTE_AUTHID 'J15USER1',
 REMOTE_PASSWORD 'J15USER1' );

set passthru server1;

drop table alltype_test;

create table alltype_test (DAT date,TMP timestamp,CLB clob,BLB blob,CHA char(5),
VCH varchar2(20),GPH nchar(8),VGP nvarchar2(10),DCM decimal,ITG int,SMI smallint,
DOB float(2),REL real,CHA_NUMBER char(10),VCH_NUMBER varchar2(10),SMI_SMALL smallint,
ITG_SMALL int,DCM_SMALL decimal(5, 2),REL_SMALL real,ITG_DATE int,DOB_DATE float,
CHA_ONECHAR char(1),VCH_ONECHAR varchar2(25),DCM_DATETIME decimal(5, 2),
CHA_DATETIME char(25),VCH_DATETIME varchar2(25),TMPDAY date,TME_DATA_CHA char(25),
CLB_NUMBER varchar2(10),VCH_TRUNC varchar2(25),CHA_TRUNC char(25));

insert into alltype_test values ('1970-11-23', '1970-11-23 12:12:12', 'ab', 'ab', 
'abcd0', 'abcdefghijklmnopqrs0', 'abcdefg0', 'abcdefghi0', 0.99, 1, 1, 0.99, 0.99, '2', 
'4', 1, 1, 1.0, 1, 50, 50, 'r', 'u', 78.69, '1989-10-09 07:05:30',
'1989-10-09 07:05:30', '1970-11-23', '1989-10-09 07:05:30', '6', 1, 
'1989-10-09 07:05:30');

CREATE OR REPLACE procedure proc12(arg0 in number, arg1 out date, arg2 out timestamp, 
arg5 out varchar2, arg6 out varchar2, arg7 out nvarchar2, arg8 out nvarchar2, arg9 
out decimal, arg10 out int, arg11 out smallint, arg12 out float,arg13 out real) is 
begin 
select DAT,TMP, CHA,VCH,GPH,VGP, DCM,ITG,SMI,DOB, REL into arg1, arg2,arg5, arg6,
 arg7, arg8, arg9, arg10, arg11, arg12, arg13 from alltype_test where SMI = arg0;
end;;

set passthru reset;

create nickname nk1 for server1."J15USER1".alltype_test;

CREATE or replace PROCEDURE nick_proc1 SOURCE J15USER1.proc12 for server SERVER1;

call nick_proc1(1,?,?,?,?,?,?,?,?,?,?,?);
NOTE:
RAW, LONG_RAW, LONG, CLOB, NCLOB, BLOB, UROWID, BFILE, are not supported as federated procedure IN, OUT and INOUT parameters.
TIMESTAMP, CHAR, GRAPHIC, and REAL as IN, INOUT parameter causes ORA-01460: unimplemented or unreasonable conversion requested. ORA-01460 is an error from ORACLE.

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
08 June 2022

UID

ibm16471961