IBM Support

How to call a stored procedure in remote data source with user-defined function by using Db2 Federation

Question & Answer


Question

How to call a stored procedure in remote data source with user-defined function by using Db2 Federation

Answer

Db2 supports federated procedure by using DRDA wrapper for Db2 data source, you can create federated procedure and call it in Federation. The remote stored procedure can have user-defined function in it.
 

Before You Begin

Enable Federation feature and restart Db2 to make it effective.
db2 update dbm cfg using federated YES
Create function and stored procedure in remote Db2 data source.
Catalog node, create wrapper, server, user mapping, federated procedure and call it in Db2 Federation.
update dbm cfg using federated YES;
uncatalog node FEDNODE2;
uncatalog db DATASDB2;
catalog tcpip node FEDNODE2 remote snore1.fyre.ibm.com server 50008;
catalog db testdbu as DATASDB2 at node FEDNODE2;

db2stop force;
db2start;
connect to testdbu;
drop WRAPPER "WRAPPER1";

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

CREATE SERVER SERVER1 TYPE DB2/UDB VERSION '11.5' wrapper "WRAPPER1" authorization "db2inst8" password "DVM_test123" OPTIONS (DBNAME 'DATASDB2' ,NODE 'FEDNODE2');

create user mapping for user server SERVER1 options (REMOTE_AUTHID 'db2inst8', REMOTE_PASSWORD 'DVM_test123');

--set passthru is to execute operations in remote database
set passthru SERVER1;
drop function test_tan;
drop procedure proc_test1;

create function test_tan(x double) returns double language SQL contains SQL NO EXTERNAL ACTION DETERMINISTIC return SIN(X)/COS(X);

CREATE OR REPLACE procedure proc_test1(IN ARGI1 DOUBLE, OUT ARGI2 DOUBLE) LANGUAGE SQL BEGIN values(test_tan(ARGI1)) into ARGI2 ;END;

--set passthru reset is to return to Db2 federation 
set passthru reset;

CREATE or REPLACE PROCEDURE nickname_proc1 SOURCE db2inst8.proc_test1 for server SERVER1;

call nickname_proc1(1,?);

[{"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:
31 August 2021

UID

ibm16471959