Hi,

THREE PROBLEMS - All arising from trying to use SYBASE ASE as the message persistence engine rather than HSQL.

I'm using JBOSS 4.2.2-GA on Solaris10 with JDK1.5.0_12 and Sybase ASE 12.5.0.3, EBF 10977, ESD #1/P/HP9000-879/HP-UX11.0/rel12503/1919/64-bit

Problem - 1. I can't get the DestinationManager to deploy because the StateManager does not appear to deploy correctly, the final message in server.log being:
--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.jca:service=DataSourceBinding,name=SybaseMMDB
State: NOTYETINSTALLED
Depends On Me:
jboss.mq:service=StateManager
I can't see any errors or failures to deploy of any MBeans further up the log.

2. The Hypersonic connection now takes around 20min to start up and appears to be the cause of JBOSS hanging on shutdown and never completely shutting down unless I use 'kill -9' on the running JVM. (Last few lines of log
2008-04-23 16:24:07,828 DEBUG [org.jboss.resource.connectionmanager.RARDeployment] Stopping jboss.jca:service=ManagedConnectionFactory,name=DefaultDS
2008-04-23 16:24:07,828 DEBUG [org.jboss.resource.connectionmanager.RARDeployment] Stopped jboss.jca:service=ManagedConnectionFactory,name=DefaultDS
2008-04-23 16:24:07,829 DEBUG [org.jboss.jdbc.HypersonicDatabase] Stopping jboss:service=Hypersonic,database=localDB
2008-04-23 16:24:08,954 DEBUG [org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread] Shutdown
2008-04-23 16:24:33,114 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Wed, 23 Apr 2008 16:24:33>
2008-04-23 16:24:33,115 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-04-23 16:24:33,115 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-04-23 16:24:33,116 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
)

3. I have successfully configured a SYBASE datsource (SybaseMMDB) and JBOSS can connect to it. The sybase-jdbc2-service.xml copied from the examples area and modified with my database name etc reads as listed below but failed to deploy because Sybase ASE didn't like this SQL with a subquery (SELECT_MAX_TX = SELECT MAX(X.TXID) FROM (SELECT MAX(TXID) AS TXID FROM g1_mm_dev..JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM g1_mm_dev..JMS_MESSAGES) X). I got past this problem by creating a view with the union (not bothered about the performance just yet...) and then changing the SQL to

SELECT MAX_VAL FROM JMS_HIGHEST_NUM

Is there an ASE setting on SYBASE that 'turns on' support for subqueries? They do work on other ASEs running on different boxes here but I don't have access to them. Assuming my workaround is ok, how come the DestinationManager still won't deploy?

***sybase-jdbc2-service.xml***:

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: sybase-jdbc2-service.xml 61575 2007-03-22 14:35:46Z adrian@jboss.org $ -->

<server>
 <!-- ==================================================================== -->
 <!-- Persistence and caching using Sybase -->
 <!-- Based on configuration provided by amresh.deshmukh at drkw.com -->
 <!-- ==================================================================== -->

 <!--
 | The destination manager is the core service within JBossMQ
 -->
 <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
 <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
 <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
 <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
 </mbean>

 <!--
 | The MessageCache decides where to put JBossMQ message that
 | are sitting around waiting to be consumed by a client.
 |
 | The memory marks are in Megabytes. Once the JVM memory usage hits
 | the high memory mark, the old messages in the cache will start getting
 | stored in the DataDirectory. As memory usage gets closer to the
 | Max memory mark, the amount of message kept in the memory cache aproaches 0.
 -->
 <mbean code="org.jboss.mq.server.MessageCache"
 name="jboss.mq:service=MessageCache">
 <attribute name="HighMemoryMark">50</attribute>
 <attribute name="MaxMemoryMark">60</attribute>
 <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
 </mbean>

 <!--
 | The jdbc2 PersistenceManager is the new improved JDBC implementation.
 | This implementation allows you to control how messages are stored in
 | the database.
 |
 | Take care that the selected blob column type in jms_messages can store
 | all message data. Some databases (e.g. mySQL) offer blob types with
 | different maximum capacity (e.g. mySQL-type BLOB=64K, LONGBLOB=2G).
 |
 | If you encouter problems with the configured BLOB_TYPE try a different
 | setting. Valid settings are OBJECT_BLOB, BINARYSTREAM_BLOB and BYTES_BLOB.
 -->
 <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
 name="jboss.mq:service=PersistenceManager">
 <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=jdbc/SybaseMMDB</depends>
 <attribute name="SqlProperties">
 BLOB_TYPE=BYTES_BLOB
 INSERT_TX = INSERT INTO g1_mm_dev..JMS_TRANSACTIONS (TXID) values(?)
 INSERT_MESSAGE = INSERT INTO g1_mm_dev..JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
 SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM g1_mm_dev..JMS_TRANSACTIONS
 SELECT_MAX_TX = SELECT MAX(X.TXID) FROM (SELECT MAX(TXID) AS TXID FROM g1_mm_dev..JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM g1_mm_dev..JMS_MESSAGES) X
 DELETE_ALL_TX = DELETE FROM g1_mm_dev..JMS_TRANSACTIONS
 SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM g1_mm_dev..JMS_MESSAGES WHERE DESTINATION=?
 SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM g1_mm_dev..JMS_MESSAGES WHERE DESTINATION=?
 SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM g1_mm_dev..JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
 MARK_MESSAGE = UPDATE g1_mm_dev..JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
 UPDATE_MESSAGE = UPDATE g1_mm_dev..JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
 UPDATE_MARKED_MESSAGES = UPDATE g1_mm_dev..JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
 UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE g1_mm_dev..JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
 DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM g1_mm_dev..JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM g1_mm_dev..JMS_TRANSACTIONS) AND TXOP=?
 DELETE_TX = DELETE FROM g1_mm_dev..JMS_TRANSACTIONS WHERE TXID = ?
 DELETE_MARKED_MESSAGES = DELETE FROM g1_mm_dev..JMS_MESSAGES WHERE TXID=? AND TXOP=?
 DELETE_TEMPORARY_MESSAGES = DELETE FROM g1_mm_dev..JMS_MESSAGES WHERE TXOP='T'
 DELETE_MESSAGE = DELETE FROM g1_mm_dev..JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
 CREATE_MESSAGE_TABLE = CREATE TABLE g1_mm_dev..JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
 DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER NULL, TXOP CHAR(1) NOT NULL, \
 MESSAGEBLOB IMAGE NOT NULL, PRIMARY KEY (MESSAGEID, DESTINATION) ) LOCK DATAROWS
 CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON g1_mm_dev..JMS_MESSAGES (TXOP, TXID)
 CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON g1_mm_dev..JMS_MESSAGES (DESTINATION)
 CREATE_TX_TABLE = CREATE TABLE g1_mm_dev..JMS_TRANSACTIONS ( TXID INTEGER NOT NULL, PRIMARY KEY (TXID) ) \
 LOCK DATAROWS
 CREATE_TABLES_ON_STARTUP = FALSE
 </attribute>
 <!-- Uncomment to override the transaction timeout for recovery per queue/subscription, in seconds -->
 <!--attribute name="RecoveryTimeout">0</attribute-->
 <!-- The number of blobs to load at once during message recovery -->
 <attribute name="RecoverMessagesChunk">0</attribute>
 </mbean>
</server>