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.

Update 14-Nov-2017:
For resolving any issues, you will need to debug the Java code to identify the issue, I have provided another version MQClientDebug.java for logging with samples for logging in Win & Linux, you will just need to download and rename the file to MQClient.java, do whatever edit you want to log & trace and load it to Oracle as per step V.2.
You will need to tell Oracle to give write permission to Java for this purpose using the following command:
call DBMS_JAVA.grant_permission('ORAMQ', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
Commit;

Note this is for debugging only (Development environment, you should not deploy such version to production or grant Java this permission)

40 comments:

  1. Mr. Fouad, thank you so much for this information!!!
    Im from Panama City, and we were already giving up on the "connect oracle and MQ by stored java procedures" idea because we were not able to upload the java classes in a correct way.

    But I kept doing some research, and when i was about to give up too and try a Webservice... I found your blog.

    Im really grateful, you have no idea how much youve helped me with this tutorial... You saved my life (and helped me get a bonus too hahahaha)

    Greeting from Panama City.

    PS: Sorry for my english, its not my first language.

    Aldo

    ReplyDelete
  2. Anytime :) glad to hear this article helped someone somewhere

    ReplyDelete
  3. We have just went production last Friday (2011-Dec-09) on Oracle 11gr2, and AIX 7.1 without any issues.

    I'm just recording this to make sure this solution is platform independent.

    ReplyDelete
  4. Hello,

    I am trying to get this working on an Oracle 11g - 11.2 - instance on linux.

    I have tested the method I am trying to call - JmsProducer - on a command prompt with a standalone JRE and it works.

    When trying to load the jars in Oracle, I also received some errors when loading, but I still continued. When executing the procedure, I get a NullPointerException, when I try to execute. This happens on the line whenre I am trying to get JmsFactoryFactory instance.


    Now, I am also running into another problem. I was unable to use dropjava to drop the jars that I had added as -jarsasdbobjects. So I asked the DBA to recreate the schema.

    After he did that, now I am not able to load the javaee jar - that I was previously able to. It loads part of it and I start getting connection closed error. Could the DBA have missed some set-up when he re-created the schema?

    Thank you

    ReplyDelete
    Replies
    1. I think you are not trying the same code I provided as I'm not using JMS I'm connecting to MQ using MQ native Java libraries, please check my code MQClient.java the import section, you will see com.ibm.mq....

      The code of JMS won't work as it is part of Java Enterprise Edition, not as Oracle JVM which is just SE (Standard Edition). Importing javaee-api-5.0.0.jar won't solve your JMS issue as it requires a container holding queue definition (As any application server) and Oracle doesn't support something like this.

      Regarding your second part of not being able to drop jars, there is a force option in the command you can use it without re-creating the schema every time, and you need to drop the jars in reverse order of loading, below are sample of batch windows script you can use for loading and dropping:

      --SAMPLE IN ANOTHER POST FOR LIMITED CHARACTERS--

      Delete
    2. Loading (You might need some editing based on your environment config & pathes):
      ---------------------------------------------------------------------------------------
      echo on

      set INSTALL_DIR=C:\install
      set LOG_DIR=C:\temp
      set ORACLE_HOME=C:\app\ashraf\product\11.2.0
      set DB_USER=ORAMQ
      set DB_PASSWORD=ORAMQ
      set DB_SID=DEVSRVR
      set DB_LISTENER=1521
      set DB_HOST=localhost
      set CONNECT_STRING=%DB_USER%/%DB_PASSWORD%@%DB_HOST%:%DB_LISTENER%:%DB_SID%

      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\javaee-api-5.0.0.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.msg.client.commonservices.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.mq.commonservices.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.mq.pcf.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.mq.headers.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.mq.jmqi.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\com.ibm.mq.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\lib\commons-codec-1.3.jar
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\src\java\MQReasonCodeResolver.java
      %ORACLE_HOME%\dbhome_1\BIN\loadjava -resolve -thin -user %CONNECT_STRING% -verbose -fileout %LOG_DIR%\loadjava.log %INSTALL_DIR%\src\java\MQClient.java

      Delete
    3. Dropping (You might need some editing based on your environment config & pathes):
      ----------------------------------------------------------------------------------
      echo on

      set INSTALL_DIR=C:\install
      set LOG_DIR=C:\temp
      set ORACLE_HOME=C:\app\ashraf\product\11.2.0
      set DB_USER=ORAMQ
      set DB_PASSWORD=ORAMQ
      set DB_SID=DEVSRVR
      set DB_LISTENER=1521
      set DB_HOST=localhost
      set CONNECT_STRING=%DB_USER%/%DB_PASSWORD%@%DB_HOST%:%DB_LISTENER%:%DB_SID%

      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\src\java\MQReasonCodeResolver.java
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\src\java\MQClient.java
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\commons-codec-1.3.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.mq.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.mq.jmqi.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.mq.headers.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.mq.pcf.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.mq.commonservices.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\com.ibm.msg.client.commonservices.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\connector.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\jta.jar
      %ORACLE_HOME%\dbhome_1\BIN\dropjava -thin -user %CONNECT_STRING% -verbose -synonym -fileout %LOG_DIR%\loadjava.log -force %INSTALL_DIR%\lib\javaee-api-5.0.0.jar

      Delete
  5. Thank you so much for the detailed explanation. I will have to wait until I am able to load the javaee-api-5.0.0.jar. The loadjava command loads part of it and I start getting Connection Closed error - ORA-00600: internal error code, arguments: [kqlInvObj:user], [70], [], [], [], [], [], [], [], [], [], []
    ORA-06512: at line 1

    java.sql.SQLException: ORA-00600: internal error code, arguments: [kqlInvObj:user], [70], [], [], [], [], [], [], [], [], [], []
    ORA-06512: at line 1

    Even when I was getting the nullpointerexception error for which you have given a detailed explanation, I used to be able to load the javaee jar. But after the DBA recreated the schema for me when I wasn't able to drop the jars, I am unable to load this jar.

    Once I get all of it squared away, I will follow your steps / instructions and let you know how that worked out for me.

    Thanks again for your help

    ReplyDelete
  6. I am trying to run a simple java application for the MQClient. I created a tester class and tried to call all the three methods. SendAndReceive, SendAndForget and Receive.

    I am able to successfully put a message in the queue, but the receive part in SemndAndReceive always fails with the error - MQJE001: Completion Code '2', Reason '2033'. When searching, at many places it indicates that you can get this error if there is nothing in the queue, but when I browse the queue, there are about 60 messages.

    The stacktrace shows

    [MQJE001: Completion Code '2', Reason '2033'. MQRC_NO_MSG_AVAILABLEcom.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2033'.
    at com.ibm.mq.MQDestination.getInt(MQDestination.java:647)
    at com.ibm.mq.MQDestination.get(MQDestination.java:456)
    at com.test.javasp.mq.MQClient.getMessage(MQClient.java:233)
    at com.test.javasp.mq.MQClient.sendAndReceive(MQClient.java:362)
    at com.test.javasp.mq.MQClientTester.main(MQClientTester.java:34)
    , null, null, null, null, null, null, null, null, null]

    I tried to use both MQ 7.0.1.8 jars as well as MQ 7.5 jars, with the same outcome.

    Am I supposed to do some additional set-up when running it as an application? Of course, the put part is working, so I don't know, how would that be the case.

    Thank you,

    ReplyDelete
    Replies
    1. No need to change in the code, it has been built to be used as generic library.

      I don't think the issue is from the code, but somewhere either by usage or from your MW back-end reply, let me clarify how the library is working in picking up the message (which is not related to Oracle) but a common practice in MQ development:

      1- Have a look at the code of procedure getMessage, there is parameter waitTimeInMilliSeconds to indicate how much the library will be waiting on the reply to queue before it returns to you no message available, you need to make sure the value you are specifying is greater than the time required from your MW to reply to your message request. You can check this with your MW team to get best timeout as setting in your application, better to have it configurable per MW service not once for all.

      2- Procedure getMessage, I'm setting the correlationId of the reply message with the same message id of the request message which is very important in multi-threading environment as replyTo queue will be having lots of messages, while you need the reply message of your request only, this is my matching criteria which is used in most enterprises, this also needs to be checked with your MW team, as they need to set something like this explicitly in their code.

      For more details about 2033 reason code, please refer to:
      http://publib.boulder.ibm.com/infocenter/wmqv7/v7r0/topic/com.ibm.mq.csqsao.doc/fm12370_1.htm

      From my experience, I'm expecting second option will be the issue. I hope this helps.

      Delete
  7. Thank you for the reply. I have not done any MQ development before so learning to do that as well along with learning to put this in as a Java Stored Procedure in Oracle.

    I do agree that the code you have provided is generic and is supposed to run as is. But due to my lack on knowledge in MQ, I wasn't sure if I needed to make some change on my side to add any options when doing the get call. When I looked at the message, it has correlationId set to zero, not the mesage id, so I thought, I may need to change the put call to do that. From your explanation, it appears that MQ administrator needs to make that change.

    I will let you know once it gets through.

    ReplyDelete
  8. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this.
    Are you aware of any other websites on this
    IBM-MQ WEBSPHERE Online training

    .

    ReplyDelete
    Replies
    1. Thanks for encouragement. Unfortunately, I don't know other sites like this online training for MQ. But I always found the information of IBM help is more than enough http://pic.dhe.ibm.com/infocenter/wmqv7/v7r5/index.jsp


      Delete
    2. Hi Mr Fouad,
      For this solution Is That Mandatory to have Mq server installed on DB server?
      Plz do reply me on dhaval.sharma@nucleussoftware.com

      Delete
    3. The code I have provided connects to MQ using client connectivity not binding connectivity i.e. the server of MQ is irrelevant to the server of DB, and it also can exist on same server.

      From IBM MQ perspective, it is recommended to have common IBM MQ (Usually called MQ HUB) for all of your applications in the enterprise (Might be spanned across multiple servers for high load situation), so I'm not recommending installing MQ server on same server of Oracle DB. You are paying MQ license for every installation of MQ server, and you don't pay anything when you are installing MQ client. If you are installing MQ on DB server which normally comes in high CPU configuration you will pay too much for licensing MQ as number of PVU will be very high.I have taken this into consideration so the code uses client connectivity.

      I hope this clarify the picture.

      Delete
  9. attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Synonyms In Oracle


    ReplyDelete
  10. Your earlier explanations helped me a lot and I could get it working in our environment. Thak you so much for your detailed help.

    I have one question that I haven't found an answer to.

    In the start of the article, it mentions Oracle 11g supports JDK 1.5 level. And a little later in the table of required jars, it indicates we should use javaee-api-5.0.0.jar Java Enterprise edition ( This version should match Oracle JVM version )

    I had thought JavaEE 5 supports JDK 1.6 and J2EE 1.4 supports JDK 1.5. So, how can we use the JavaEE 5.0 api jar instead of 1.4? Am I missing something here?

    Thanks again

    ReplyDelete
    Replies
    1. Happy to hear it worked for u :)))

      Regarding yr question of using 1.4 ee, this I never thought about, but let me think loudly trying to answer:
      1- you might be right in yr assumption, u will never know unless u try.
      2- I really don't know what differs Oracle JVM from normal Java sdk, all I know it is special and have some limitation, so I was looking for compatibility to avoid any problems especially not all classes will be loaded.

      May I ask what difference it will make to u for such info?

      Delete
    2. Sure. After I did my POC and told my boss and the colleagues that it can be done and what the requirements were - that we should use JDK 1.5 and the compliant libraries and gave them the list of libraries - one of my colleagues tried with JDK 1.6 and told me basically I was wrong because the list says to use JavaEE 5 jars, he should be able to use it with JDK 1.6, but it doesn't work.

      I know, I had tried with a different version of the codec library that was JDK 1.6 compliant and that didn't work. That got me wondering about it.

      How come the JavaEE 5.0 jar works? I also was looking to find out how is this jar used in the database ( from what I understood from reading from the net searches, this jar contains only interfaces, no implementations ). So, I tried to go deeper and tried to understand purpose of this jar and how this plays a role in this set-up etc. Still looking to find the purpose of this jar and which other situations ( like using MQ from OJSP as in this article ) this jar would be required etc.

      Thank you for indulging my questions and I would appreciate, if you have answers to any / all of my questions above that will help me understand the basics of how this is working rather than "just getting it to work".

      Thank again

      Delete
    3. I c.
      1- using library that is jdk 1.6 will never work, think of it as normal Java IDE that u specified to use jdk 1.5 for yr project, so all yr jars can't use higher version, u can only use same or lower versions.

      2- jee 5 works as ibm mq is depending on classes that do not require container to run, think of it again as including it in yr classpath.

      3- Regarding the purpose of this jar, please refer to original article mentioning asktom website, this was the path to the solution, what happened is that I need com.ibm.mq.jar in my code as I have imports using it, it fails and was giving that it can't find other classes, so I was searching where to get other classes so I knew I needed to import other jars. The whole point is that all those jars were required to get com.ibm.mq.jar to be imported successfuly.

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Hello!
    I am trying to run a simple java application for the MQClient. I tried to call SendAndForget. In NetBeans it works fine, but in Oracle 11g (11.2 - instance on linux) I have same error:
    java.lang.NullPointerException
    at MQClient.putMessage(MQClient_2:198)
    at MQClient.sendAndForget(MQClient_2:306)

    this line is:
    MQMessage mqMessage = new MQMessage();

    Thank you

    ReplyDelete
  14. I am trying to implement this solution and on first grant command I get an error.
    grant connect to ORAMQ;
    "ORA-01919: role 'CONNECT' does not exist"
    SQLPLUS reports oracle release
    "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production"
    JAVA -version
    java version "1.4.2_14"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_14-b05)
    Java HotSpot(TM) Client VM (build 1.4.2_14-b05, mixed mode)
    So far I have been not been successful in deploying MQ to Oracle getting warning when loading my client app. and warning becomes error when running the app.
    "ORA-29552: verification warning: java.lang.VerifyError: signature: (Ljava/lang/String;)I) catch_type not subclass of Throwable"
    and got this when loading com.ibm.mq.jar
    "loading com.ibm.ma.jar in Oracle
    errors : class com/ibm/mq/MessageBufferManager
    ORA-29552: verification warning: java.lang.VerifyError: (class: com/ibm/messageBufferManager, method: getBufferedMessage signature: (IILcom/ibm/mq/MQM;Lcom/ibm/mq/MQGetMessageOptions;)[B) Incompatible argument to function"
    Seems like my case is different than what you described here.

    ReplyDelete
    Replies
    1. First error 01919 is Oracle error not related to the MQ, please check this forum http://www.orafaq.com/forum/t/177415/

      Java version, Oracle 11gr2, you should be having Java 1.5, please execute the command from this path:
      %ORACLE_HOME%\dbhome_1\jdk\bin>java -version

      Please when posting multiple issues, please have some separator to make it easier in reading.

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Where can I find the correct javaee-api-5.0.0.jar?

    When I try to load javaee-api-5.0.0.jar (jar size: 3994473) I got the following error:
    ORA-29532: Java call terminated by uncaught Java exception: oracle.aurora.rdbms.MalformedDefinitionException: In ClassHandle.create in schema XXXXXX
    ORA-29509: incorrectly formed Java binary class definition

    When I use javaee-api-5.0-1.jar (jar size: 748851) then I can load com.ibm.mq.jar successfully without error, but when I run the program I get error at the following lines:
    MQQueueManager queueManager = new MQQueueManager( qManager, props );
    Error at the first run:
    ORA-29532: Java call terminated by uncaught Java exception: java.lang.ExceptionInInitializerError
    Error at the second run:
    ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError

    The Orce JVM version:
    java version "1.5.0.28"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0.28-_20_mar_2013_05_55)
    Java HotSpot(TM) Server VM (build 1.5.0.28 jinteg:03.20.13-05:25 IA64, mixed mode)

    It is possible that the the solution not work at HP environment, or I not use the correct javaee-api?
    Thank you!

    ReplyDelete
    Replies
    1. The version size I have used for javaee-api-5.0.0.jar is (3,994,473 bytes)

      I have shared it in this link
      https://drive.google.com/file/d/0B9tUTHxSSn7jOEF0ZDBob01rT3c/view?usp=sharing

      Please retry again & let me know if u still face issue.

      Solution is not platform specific, It has been running Windows, AIX, Linux without change in the solution itself, just changes in the commands issuing related to platform

      Delete
    2. Thank you the response!

      The solution for the problem is: Grant the JAVAUSERPRIV role or the "java.io.FilePermission <> read" permission to schema user.
      I don't know why need FilePermission when we not use files in the program.
      Our DBA's not allow the <> right.

      Delete
    3. <> means: <<ALL FILES>>

      Delete
  17. Hi Ashraf,

    We are trying to implement your solution but unfortunately we are still stuck at the issue that when executing the stored procedure we get the following error:

    ERROR at line 1:
    ORA-29532: Java call terminated by uncaught Java exception:
    java.lang.ExceptionInInitializerError
    ORA-06512: at "EMCF_MIS.SET_AND_FORGET", line 1
    ORA-06512: at line 1

    We tried several approaches to know where the ExceptionInInitializerError is coming from, but to no avail. Looking in the oracle database for which classes are invalid (not resolved) does not gives us a lot of clues unfortunately. Do you know of a way we debug the cause of the behaviour and hopefully solve it?

    Thanks, Paul

    ReplyDelete
    Replies
    1. For debugging, I used to log from Java component "MQClient.java" to file, but it requires that you give Oracle permission to write files before doing such change.

      BTW, the steps has been used from multiple friends & it didn't cause issue before, most issues, comes from environment permission or missing a step.

      If u identified the line causing error & need more help, please send the Oracle version u r working, and Java version & more details, about what u r sending & parameters.

      Delete
  18. Hi Ashraf,

    Thanks for your help, these are the versions etc:
    [oracle@hi032009 pf]$ java -version
    java version "1.5.0_30"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_30-b03)
    Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_30-b03, mixed mode)
    Oracle Path: /opt/oracle/product/11.2.0/db_1/jdk/bin:

    I used your MQClient source with only the addition of a main method to aid in debugging which calls the set_and_forget method:
    public static void main(String[] args) {
    String [] messageIds = new String[10];
    String [] result = new String[10];
    sendAndForget( "emcfqa", 1414, "SMB.TST.SVRCONN", 1208, "QMNAA017", "SMB2CCM.PUT", "Hello World","SMB2CCM.GET", messageIds, result);
    System.out.println(result[0]); // Display the string.
    }

    When I run this code, after the call to createQueueManager, the program counter immediately goes to the finally block with queue.close() as you can see in the output of jdb (which I attached)

    Root Thread[1] next
    >
    Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=306 bci=36
    306 result[0] = "1: Start";

    Root Thread[1] list
    302 result[0] = "";
    303
    304 try
    305 {
    306 => result[0] = "1: Start";
    307 queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );
    308 result[0] = "2: QManager created";
    309 queue = queueManager.accessQueue( queueName, qWriteOptions );
    310 result[0] = "3: Write Queue created";
    311
    Root Thread[1] next
    >
    Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=307 bci=48
    307 queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );

    Root Thread[1] list
    303
    304 try
    305 {
    306 result[0] = "1: Start";
    307 => queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );
    308 result[0] = "2: QManager created";
    309 queue = queueManager.accessQueue( queueName, qWriteOptions );
    310 result[0] = "3: Write Queue created";
    311
    312 mqMessage = putMessage( queue, messageContent, replyToQueueName );
    Root Thread[1] next
    >
    Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=330 bci=354
    330 queue.close();

    Root Thread[1] print result[0]
    result[0] = "1: Start"
    Root Thread[1] print queue
    queue = null

    Root Thread[1] print queueManager
    queueManager = null
    Root Thread[1] cont

    The queueManager and queue are both null as you can see.

    The permissions of the user are:
    EMCF_MIS CONNECT NO YES NO
    EMCF_MIS JAVADEBUGPRIV NO NO NO
    EMCF_MIS JAVAIDPRIV NO NO NO
    EMCF_MIS JAVASYSPRIV NO NO NO
    EMCF_MIS JAVAUSERPRIV NO NO NO
    EMCF_MIS JAVA_ADMIN NO NO NO
    EMCF_MIS JAVA_DEPLOY NO NO NO
    EMCF_MIS OWB$CLIENT NO NO NO
    EMCF_MIS OWB_DESIGNCENTER_VIEW NO YES NO
    EMCF_MIS OWB_USER NO YES NO
    EMCF_MIS RESOURCE NO NO NO
    EMCF_MIS SELECT_CATALOG_ROLE NO NO NO

    Now looking at the privileges it could be that these are wrong?

    Thanks again for your help!


    ReplyDelete
    Replies
    1. Dear Paul,
      Adding main method and running your MQClient outside Oracle JVM is different than running within Oracle JVM.

      Running from outside, you will need to declare classpath and pass it to the application. This was not my intention, as you need to trust that Java code is working :) (BTW, you can find similar libraries nearly coding the same way to connect to IBM MQ). The problem I'm expecting will be something in your environment (Running within Oracle) we need to discover, so my intention was to add some java lines for logging to a file in the current MQClient.java, and load it to Oracle and run it to discover the error cause.

      The permission, I was talking about, Oracle won't allow to give you permission to write to external files without permission to your user. It will cause issue, and that's why you will need to grant permission to your user.

      I hope u got me, let me know if this can help, or u need more help, as u can see this was published 2011 and till today it is quite stable during upgrades. Anyway, I will try to check in my work some samples of debugging and permissions to give better idea if possible.

      Delete
    2. Hi Ashraf,

      I was not clear apparently, the code is running from within Oracle. Either with the job debugger or not, I get exactly the same behaviour with the exception being thrown.
      In fact, if I'm running the MQClient (with the main method added) as a standalone program outside Oracle it runs fine. It is really the integration within Oracle which does not work.
      It could be that we did not set a proper privilege as in the previous comments from xpet71 as we get the same error with the ' java.lang.ExceptionInInitializerError' .
      We'll investigate once more the privileges if they are wrong. The loading of com.ibm.mq.jar and MQClient was successful, hence we suspect the privileges.

      Thanks again, Paul

      Delete
    3. Hi Paul,
      I have updated the post (plz have a look at the end with update with todays date) & uploaded new version for both MQClient.java and new File MQClientDebug.java

      MQClient: changes just added catch for RuntimeException in two locations.
      MQClientDebug.java: Sample for debugging to file.

      In the post I have added permission from Oracle to be given.

      Please check & apply changes, and let me know for any help.

      Delete
  19. Hi Ashraf,

    We finally got it to work; it appears that 2 additional java permissions needed to be set:

    Call dbms_java.grant_permission( 'ORAMQ', 'SYS:javax.management.MBeanServerPermission', 'createMBeanServer', '' );
    Call dbms_java.grant_permission( 'ORAMQ', 'SYS:javax.management.MBeanPermission', 'com.ibm.mq.jmqi.monitoring.TraceControlImpl#-[MQ Classes for Java:name=TraceControl,type=Jmqi CommonServices]', 'registerMBean' );

    We did not see the error message before as you need to redirect the java output to the sql console:
    set serveroutput on;
    set serveroutput on size 1000000;
    call dbms_java.set_output(1000000);

    We're all happy now :-)


    ReplyDelete
    Replies
    1. I didn't require such permissions.

      Anyway, happy to c it worked for u :)

      Delete
    2. Dear Paul,
      Sorry for posting too late, but I have a question. While you were testing, you were using IBM MQ version 8 or 9?

      Delete