JMS with DB2

[mpelzsherman]

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

Thanks!
- Michael

[genman]

$JBOSS_DIST/docs/examples/jca

[mpelzsherman]

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

[mpelzsherman]

Still struggling with this.

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

- Michael

[schrouf]

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

[mpelzsherman]

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

[schrouf]

Which JBoss version are your using ?

[mpelzsherman]

4.0.0

[mpelzsherman]

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

[ahall105]

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

[ccaprar]

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 :(