This post is useful for DB2 developers, administrators, database architects to learn how to access Oracle database stored procedures from DB2 database sources
A federated system is a special type of distributed database management system (DBMS). A federated system consists of a DB2® database system that operates as a federated server, a database that acts as the federated database, one or more data sources, and clients (users and applications) that access the database and data sources. 
The power of a federated system is in its ability to:

    1. Correlate data from local tables and remote data sources, as if all the data is stored locally in the federated database
    2. Update data in relational data sources, as if the data is stored in the federated database
    3. Move data to and from relational data sources
    4. Take advantage of the data source processing strengths, by sending requests to the data sources for processing
    5. Compensate for SQL limitations at the data source by processing parts of a distributed request at the federated server

A federated procedure is a federated database object that references a procedure on a data source.
Federated procedures are not alternative names for data source procedures in the same way that aliases are alternative names. A federated procedure is defined at the federated database but calls a data source procedure when the federated procedure is invoked. Because the federated procedure is a federated database object, users and client applications can invoke the data source procedure logic by calling a federated procedure. The results of the data source procedure, such as the output parameters, are returned by the federated procedure. Using a federated procedure makes the location of the data source procedure transparent to users and client applications. You use the name of the federated procedure to call the data source procedure.

A federated procedure is to a remote procedure what a nickname is to a remote table. Nicknames and federated procedures are objects on the federated database. A nickname is an object that references an object, such as a table or view, on the data source. With a nickname, you query a data source object. With a federated procedure, you call a data source procedure.

You use the CREATE PROCEDURE (Sourced) statement to register a federated procedure and use the CALL statement to call a procedure. You can embed the CREATE PROCEDURE (Sourced) statement in an application program or issue the statement with dynamic SQL statements. 
The CREATE PROCEDURE (sourced) statement can be used to link a remote stored procedure:
- "stand-alone" or in a package - after the SOURCE keyword you could define the procedure you want federate as, in the most complete version, source schemaname.packagename.procname.
- with zero or more input and/or output parameter - in case you have some parameters, after the source name, it's enough define the keyword NUMBER OF PARAMETERS followed by the number of parameters (not the type).


This example shows how to use the CREATE PROCEDURE statement to create a federated procedure for a data source procedure on Oracle.

Step 1:  Original Oracle DB source procedure code

CREATE OR REPLACE PROCEDURE ORASCHEMA.TESTFEDERATION (p_in IN VARCHAR2(10), p_out OUT INTEGER)
 AS
    v_count INTEGER := 0;
    BEGIN
	SELECT count(*)
	  INTO v_count
	  FROM datdb.nation;
	p_out := v_count;
END;

Step 2 : Create a DB2 procedure referring to the oracle source procedure code

CREATE OR REPLACE PROCEDURE DB2INST1.REM_TESTFEDERATION
SOURCE ORASCHEMA.TESTFEDERATION 
NUMBER OF PARAMETERS 2
FOR SERVER TSMEDB 
SPECIFIC TESTFEDERATION;

DB2INST1.REM_TESTFEDERATION=> This is the DB2 name referring to the Oracle source procedure
ORASCHEMA.TESTFEDERATION => This is the Oracle source procedure name
Number of parameters => 2
TSMEDB => This is the server name created by federation setup steps
Below are for reference on Oracle DB:
	a. db2 create wrapper orawrapper library 'libdb2net8.a'
	b. db2 "create server TSMEDB  TYPE ORACLE VERSION 11.1 WRAPPER orawrapper 
                  OPTIONS (ADD NODE 'TMP_NODENAM')"
	c.  db2 "create user mapping for db2inst1 server TSMEDB options
                   (ADD REMOTE_AUTHID 'col_team', ADD REMOTE_PASSWORD '*******')"
SPECIFIC =>  Oracle procedure name

Step 3: To test the procedure just call it

db2 "call DB2INST1.REM_TESTFEDERATION('a',?)"

These posts are my opinions and do not necessarily represent IBM’s positions, strategies, or opinions
 Happy Reading!!

Cheers

Ramya Yeleswarapu
mailto :- ramyeles@in[dot]ibm[dot]com