2011-07-28

Oracle DB to IBM MQ connector

In my quest to find a way for communicating Oracle DB to IBM MQ, I found my answer using Oracle Messaging Gateway (OMG) using this provided article from IBM developer works.

It seems working, and actually good idea, but main issue was the cost versus the functionality required. My main need is either to send a text message to queue (Asynchronous / Synchronous) i.e. (Send and forget / Send and Receive). Price by the time of this article was around (xx,000 USD per CPU) !!!!

So, I have decided to look into how to build custom code to solve this issue, and be platform independent as I have situation where Oracle is running on AIX, as well as Windows. As, I have developed several Java stored procedure before I thought it will be a good idea and will do the trick, and I was right :)

I have tried this using Oracle 11gr2 on Windows 2008r2 having IBM MQ 7.0.0.2 installed on same server and another AIX 6.1 server as well. I think the steps can be applied on different versions as long you understand the below concepts.
I- Concepts:
1- You need to know what version of Oracle JVM, you are running, this can simply done by issuing the following command:
%ORACLE_HOME%\dbhome_1\jdk\bin>java -version
For Oracle 11g, the following result should be shown:
java version "1.5.0_17"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b04)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b04, mixed mode)

This is important as Oracle JVM is just SE (Standard Edition) while we need to import Java Enterprise Edition into Oracle to be able to connect to MQ, so now you know which version of Enterprise should be imported.

2- Oracle JVM doesn't support JNI, so connectivity to IBM MQ can never be in Binding mode and should be in client mode, as binding mode use native OS call so Java classes will need to load dll on windows which is not allowed.

3-If u r a Java developer u will always think of modifying CLASSPATH to include external Java libraries of MQ, but actually Oracle JVM is not working with same concept, and all libraries should be loaded into Oracle DB schema. For more details; I think better read this Oracle page from documentation.

4- Oracle supports loading jar files but not nested jar files (jar files containing other jar files).

II- Required Jar files:
My trial was based on asktom old posting it didn't provide complete solution but the idea which is the most important part in finding the solution, which is trying to load jar by jar and looking into errors to identify dependency on other jar files till I get my main jar (com.ibm.mq.jar) file imported to DB without errors.

I have done this practice and found below list of required jar files:
Jar name Description and Location
javaee-api-5.0.0.jar Java Enterprise edition
This version should match Oracle JVM version.
Available here
com.ibm.msg.client.commonservices.jar IBM message client common services
This is inside content of another jar, but Oracle loadjava utility can’t load nested jar files, so this file needs extraction.
%IBM_MQ%\java\lib\OSGi\com.ibm.msg.client.osgi.jms_7.0.1.3.jar\com.ibm.msg.client.commonservices.jar
com.ibm.mq.commonservices.jar IBM MQ common services
%IBM_MQ%\java\lib\com.ibm.mq.commonservices.jar
com.ibm.mq.pcf.jar IBM MQ PCF
%IBM_MQ%\java\lib\com.ibm.mq.pcf.jar
com.ibm.mq.headers.jar IBM MQ headers
%IBM_MQ%\java\lib\com.ibm.mq.headers.jar
com.ibm.mq.jmqi.jar IBM MQ jmqi
%IBM_MQ%\java\lib\com.ibm.mq.jmqi.jar
com.ibm.mq.jar IBM MQ
%IBM_MQ%\java\lib\com.ibm.mq.jar
commons-codec-1.3.jar Apache commons codec
Used for converting RAW to HEX and vice versa for printing MQ message id, we are using very small functionality from the jar so upgrade is not required unless actually needed.
Download from apache common codec

III- User creation & Permissions:
1- Create user to have all related jars (Or use your own user but make sure to have required privileges) & java classes loaded into “ORAMQ”:
create user ORAMQ identified by <password>;

2- Grant roles to user:
grant connect to ORAMQ;
grant resource to ORAMQ;
grant debug connect session to ORAMQ;

3- Grant Java permissions to user:
Call dbms_java.grant_permission( 'ORAMQ', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
Call dbms_java.grant_permission( 'ORAMQ', 'SYS:java.net.SocketPermission', '<QMANAGER_HOST>:<QMANAGER_LISTENER>', 'connect,resolve' );
Commit;
<QMANAGER_HOST>It should be replaced by Queue manager machine name or IP.
<QMANAGER_LISTENER>It should be replaced by Queue manager listener.

IV- Loading external jars:
1- Loading Java Enterprise to Oracle:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-1-javaee-api-5.0.0.jar.log %INSTALL_DIR%\javaee-api-5.0.0.jar -resolve

exiting : Failures occurred during processing

2- Loading IBM message client common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-2-com.ibm.msg.client.commonservices.jar.log %INSTALL_DIR%\com.ibm.msg.client.commonservices.jar -resolve

Classes Loaded: 87
Resources Loaded: 16
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

3- Loading IBM MQ common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-3-com.ibm.mq.commonservices.jar.log %INSTALL_DIR%\com.ibm.mq.commonservices.jar –resolve

Classes Loaded: 76
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 76
Errors: 0

4- Loading IBM MQ PCF:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-4-com.ibm.mq.pcf.jar.log %INSTALL_DIR%\com.ibm.mq.pcf.jar -resolve

exiting : Failures occurred during processing

5- Loading IBM MQ common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-5-com.ibm.mq.headers.jar.log %INSTALL_DIR%\com.ibm.mq.headers.jar -resolve

exiting : Failures occurred during processing

6- Loading IBM MQ JMQI:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-6-com.ibm.mq.jmqi.jar.log %INSTALL_DIR%\com.ibm.mq.jmqi.jar -resolve

exiting : Failures occurred during processing

7- Loading IBM MQ:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-7-com.ibm.mq.jar.log %INSTALL_DIR%\com.ibm.mq.jar -resolve

Classes Loaded: 117
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 117
Errors: 0

The above is important validation, as this is the jar that is been called from Java utility, it should compile without any errors.


8- Loading Apache commons codec:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-8-commons-codec-1.3.jar.log %INSTALL_DIR%\commons-codec-1.3.jar -resolve

Classes Loaded: 25
Resources Loaded: 8
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

V- Loading Java Stored Procedure:
1- MQReasonCodeResolver.java: This class is mainly adding helpful functionality in MQException to return representative string better than error code:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-9-MQReasonCodeResolver.java.log %INSTALL_DIR%\MQReasonCodeResolver.java –resolve

Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

2- MQClient.java: This class is the main class responsible for MQ connectivity:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-9-MQClient.java.log %INSTALL_DIR%\MQClient.java –resolve

Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

VI- Publish as PL/SQL Stored Procedure:
   PROCEDURE MQ_SEND_AND_RECEIVE(P_HOST            IN VARCHAR2,
                                 P_LISTENER        IN NUMBER,
                                 P_QMCHANNEL       IN VARCHAR2,
                                 P_CCSID           IN NUMBER,
                                 P_QMANAGER        IN VARCHAR2,
                                 P_QUEUE           IN VARCHAR2,
                                 P_MESSAGE         IN VARCHAR2,
                                 P_REPLYTOQUEUE    IN VARCHAR2,
                                 P_WAITMILLISECOND IN NUMBER,
                                 P_MSGID           OUT VARCHAR2,
                                 P_RESULT          OUT VARCHAR2,
                                 P_MESSAGE_REPLY   OUT VARCHAR2) AS
      LANGUAGE JAVA NAME 'MQClient.sendAndReceive(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, int, java.lang.String[], java.lang.String[], java.lang.String[])';

   PROCEDURE MQ_SEND_AND_FORGET(P_HOST        IN VARCHAR2,
                                P_LISTENER    IN NUMBER,
                                P_QMCHANNEL   IN VARCHAR2,
                                P_CCSID       IN NUMBER,
                                P_QMANAGER    IN VARCHAR2,
                                P_QUEUE       IN VARCHAR2,
                                P_MESSAGE     IN VARCHAR2,
                                P_REPLY_QUEUE IN VARCHAR2,
                                P_MSGID       OUT VARCHAR2,
                                P_RESULT      OUT VARCHAR2) AS
      LANGUAGE JAVA NAME 'MQClient.sendAndForget(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String[], java.lang.String[])';


Now u can use above PL/SQL procedures for communicating with IBM MQ from Oracle DB :)

Finally, I know this was a very long posting but I hope it will help someone out there.