10 Replies Latest reply on Oct 24, 2005 10:29 AM by Cristian Caprar

    JMS with DB2

    Michael Pelz Sherman Newbie

      I'm looking for some example configuration files for using DB2 with JBoss/JMS. Can anyone provide this?

      Thanks!
      - Michael

        • 1. Re: JMS with DB2
          Elias Ross Master

          $JBOSS_DIST/docs/examples/jca

          • 2. Re: JMS with DB2
            Michael Pelz Sherman Newbie

            I see db2-ds.xml and db2-xa-ds.xml, but these are just basic JDBC datasource configurations.


            I'm looking for the equivalent of $JBOSS_DIST/docs/examples/jms/oracle-jdbc2-service.xml for DB2.


            Thanks,
            - Michael

            • 3. Re: JMS with DB2
              Michael Pelz Sherman Newbie

              Still struggling with this.

              Is there something I didn't understand about the reply from genman above?

              - Michael

              • 4. Re: JMS with DB2
                Ulf Schroeter Novice

                What's the problem ? Just take the oracle sample configuration and modify theSQL DDL statements (e.g. data types) for the required jms tables so they can be used with DB2. This shouldn't be too hard.

                Regards
                Ulf

                • 5. Re: JMS with DB2
                  Michael Pelz Sherman Newbie

                  I would have thought so too!

                  Part of the problem is I am a "novice" with JBoss so admittedly this may be a lot simpler than I am am making it.

                  Perhaps some context will help. My team is trying to create a "prefabricated" Jboss domain that can be deployed by simply replacing a bunch of tokens in a number of files (mainly to customize the database connection info, etc.).

                  We've succeeded in getting this working with Oracle, are having some trouble switching to DB2. Our EJB's are deploying OK, so I think our basic JDBC datasource config is OK, but I get the following error when JBoss tries to set up the JMS connection:

                  javax.jms.JMSException: Error creating the dlq connection: XAConnectionFactory not bound
                          at org.jboss.ejb.plugins.jms.DLQHandler.createService(DLQHandler.java:166)

                  I've been through the docs here and all over Google looking for a more detailed explanation of the jms config files and how they actually work. So far I haven't found what I'm looking for. I did see one report of a similar problem that was apparently fixed by simply upgrading to the 4.0.1:

                  http://weblog.neeraj.name/blog/_archives/2005/1/11/237842.html

                  Perhaps things would become clearer if I could get more info from the jboss log. I've set the root logger to "DEBUG"; haven't tried "ALL" yet. Might be helpful also to get more JDBC debugging info. Not quite sure how to do this with DB2; I know with Oracle you have to speficy a different JDBC driver - not sure about DB2.

                  Any advice is greatly appreciated!

                  - mps

                  • 6. Re: JMS with DB2
                    Ulf Schroeter Novice

                    Which JBoss version are your using ?

                    • 8. SUCCESS! Re: JMS with DB2
                      Michael Pelz Sherman Newbie

                      Yay! We finally got this working!

                      I basically just renamed the oracle-jdbc2-service.xml to "db2-jdbc2-service.xml". The issue was with the following differences from the Oracle SQL in the jdbc2-service.xml file:

                      $ diff db2-jdbc2-service.xml oracle-jdbc2-service.xml
                      60c60
                      < DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES MESS WHERE TXOP=? AND EXISTS (SELECT TXID FROM JMS_TRA
                      NSACTIONS TX WHERE TX.TXID = MESS.TXID)
                      ---
                      > DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES MESS WHERE TXOP=:1 AND EXISTS (SELECT TXID FROM JMS_TR
                      ANSACTIONS TX WHERE TX.TXID = MESS.TXID)
                      70c70
                      < CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER NOT NULL, PRIMARY KEY (TXID) )
                      ---
                      > CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )

                      DB2 doesn't like the Oracle bind variable (":1") syntax, and primary key columns must be declared "NOT NULL".

                      - Michael

                      • 9. Re: JMS with DB2
                        ahall105 Newbie

                        Just so everyone knows, the same solution works on 3.2.X.

                        • 10. Re: JMS with DB2
                          Cristian Caprar Newbie

                          Also, do not forget to set the schema in your datasource, in case you use the same db2 user to connect to multiple schemas. This is something missing from the db2 datasource examples and can be located also only on the forum :(