• Share
  • ?
  • Profiles ▼
  • Communities ▼
  • Apps ▼

Blogs

  • My Blogs
  • Public Blogs
  • My Updates
  • Administration
  • Log in to participate

▼ Tags

 

▼ Similar Entries

Modernize Db2 mainfr...

Blog: Db2 Tools for...
Calene_Janacek 270000DKGJ
Updated
2 people like thisLikes 2
No CommentsComments 0

Db2 for z/OS at Thin...

Blog: Db2 for z/OS ...
EricRadzinski 060000F2X2
Updated
1 people likes thisLikes 1
No CommentsComments 0

TBSM 6.2 Installatio...

Blog: Network and S...
Mihaela.Gheorghe1@ibm.com 5500022Y6A
Updated
2 people like thisLikes 2
No CommentsComments 0

How to do an increme...

Blog: Thoughts from...
Jian_Tang 50T5CPU10M
Updated
0 people like thisLikes 0
No CommentsComments 0

How to insert data i...

Blog: Thoughts from...
Jian_Tang 50T5CPU10M
Updated
0 people like thisLikes 0
No CommentsComments 0

▼ Similar Ideas

使用Flashcopy Image Co...

Ideation Blog: 主机知刊
tianna 310001QCY5
Updated
Votes 2 No CommentsComments 0

▼ Archive

  • September 2014
  • June 2014
  • May 2014
  • November 2013
  • November 2012
  • July 2012

▼ Blog Authors

DB2 Corner

View All Entries
Clicking the button causes a full page refresh. The user could go to the "Entry list" region to view the new content.) Entry list

How to create Federated Procedures in DB2 10.5

RamyaYeleswarapu 270004WT0H | | Tags:  procedures stored oracle federated db2 create ‎ | 11,316 Views
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
Modified on by RamyaYeleswarapu 270004WT0H
  • Add a Comment Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry
Notify Other People
notification

Send Email Notification

+

Quarantine this entry

deleteEntry
duplicateEntry

Mark as Duplicate

  • Previous Entry
  • Main
  • Next Entry
Feed for Blog Entries | Feed for Blog Comments | Feed for Comments for this Entry