question on jms table "JMS_MESSAGES" column size

[niefeng]

Hi all,

Is there any impact if we increase the column size of "DESTINATION" in the "JMS_MESSAGES" table? Previously I was using Oracle as the DB, and recently i am porting it to Postgres DB. I realize that the saving of the jms message (under PostgresDB) is failed due to the column size of the "Destination" is shorter.

I did a comparison of the "jdbc2-service.xml" for Oracle and Postgres and found out the "destination" column size is variant between these two tables.

For "oracle-jdbc2-service.xml", the column size is 255. For "postgres-jdbc2-service.xml", the column size is "150". Below showing the DDL of the JMS_MESSAGES table for both Oracle and Postgres.

Oracle

CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
         DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
         MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )

Postgres

CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB BYTEA, PRIMARY KEY (MESSAGEID, DESTINATION))

The Jboss i am using is jboss 4.2.2, and deployed it in under Solaris 10.

Will it be any performance issue or any impact if i increased the Destination column size from 150 to 255 char?

Thanks

nie feng

[marco.battaglia]

I'm using MySql 5 deployed under Solaris 10. I have increased the size of DESTINATION field. It works fine.

It's in production from six months on two "twin system" with JBossMQ under JBoss4.2.2.

They managed 120k message per day. I think it's a good test for you.

[marco.battaglia]

Inside this file: deploy/jms/[yourBD]-jdbc2-service.xml

you find queries executed to select messages:

SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?

SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM JMS_MESSAGES WHERE DESTINATION=?

SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?

The field destination contains also the selector and the subscriber.

You can search in java sources of jms where this sql are used, and configure indexes on DB.

Good work!

SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?

      SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM JMS_MESSAGES WHERE DESTINATION=?

      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?