Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure.

I have a scenario where a trigger on a DB2 table will invoke a DB2 stored procedure. And further this stored procedure have to call to connect with Oracle DB to do some certain operation (a stored procedure).

Is my understanding is right, and can Oracle's stored procedure can be called through DB Link?

Edit: The version of DB2 is 9.7 and my platform is AIX 6.

share|improve this question
    
Can you speak to specifically which type of DB2 you have? LUW? DB2 for i? DB2 for z? Also, which version and edition do you have? (ie, Express-C? Enterprise? 9.5? 9.7? 10.1?) These answers may impact the answers. –  Chris Aldrich Aug 27 '13 at 12:55
    
@ChrisAldrich I have edited the description of question. –  Tahir Akram Aug 28 '13 at 5:28
add comment

2 Answers

Depending on the DB2 platform, it may indeed be possible to call an Oracle (or other database) stored procedure from a DB2 stored procedure. With DB2 for Linux, Unix, and Windows the process of setting up communication with a foreign data source is called federation. You can read more about this in the manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.iis.fed.sysconfig.doc/topics/iiyfafsporc.html

share|improve this answer
add comment

I don't know how to call an Oracle stored procedure from a DB2 stored procedure, but the syntax for calling an Oracle procedure over a database link is:

EXEC scemaname.packagename.procedurename@databaselinkname('paramater1','parameter2' );

It's easiest to create a local synonym for the remote package name - makes things easier to read.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.