středa 3. dubna 2013

Glassfish JMS broker perzistující zprávy do Sybase ASE

Co budeme potřebovat:
  1. Oracle Glassfish 3.1.2.2 Dále je předpokládáno umístění v ~/glassfish3122
  2. Sybase Adaptive Server Enterprise 15.5
  3. JMS broker (integrovaný v Glassfishi)
  4. JDBC konektor pro Sybase ASE - jConnect 7.0.7
Postup:
1) Ve webové administraci serveru Glassfish otevřeme Configurations->server-config->Java Message Service. JMS Service Type nastavíme na hodnotu LOCAL. Poté v Additional Properties postupně nastavíme následující:
 <property name="imq.persist.store" value="jdbc"></property>  
     <property name="imq.persist.jdbc.sybase.driver" value="com.sybase.jdbc4.jdbc.SybDriver"></property>  
     <property name="imq.persist.jdbc.sybase.user" value="db_user"></property>  
     <property name="imq.persist.jdbc.sybase.password" value="db_user_passwd"></property>  
     <property name="imq.persist.jdbc.dbVendor" value="sybase"></property>  
     <property name="imq.brokerid" value="testBroker"></property>  
     <property name="imq.autocreate.reaptime" value="1"></property>  
     <property name="imq.instanceconfig.version" value="300"></property>  
     <property name="imq.jmsra.managed" value="true"></property>  
     <property name="imq.persist.jdbc.sybase.closedburl" value="jdbc:sybase:Tds:IPAddress:port/database"></property>  
     <property name="imq.persist.jdbc.sybase.opendburl" value="jdbc:sybase:Tds:IPAddress:port/database"></property>  
 Toto je samozřejmě možné zadat i v souboru config/domain.xml příslušné domény.
Pokud se rozhodnete namísto com.sybase.jdbc4.jdbc.SybDriver použít com.sybase.jdbc4.jdbc.SybDataSource nejspíš vás v logu bude čekat tato chyba:
 ERROR [B3000]: Could not open persistent message store:  
 com.sun.messaging.jmq.jmsserver.util.BrokerException: [B3026]: Failed to get connection to jdbc:sybase:Tds:IPAddress:2030/database  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.newConnection(CommDBManager.java:796)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.getNewConnection(CommDBManager.java:757)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.DBConnectionPool.createConnection(DBConnectionPool.java:483)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.DBConnectionPool.<init>(DBConnectionPool.java:352)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.DBConnectionPool.<init>(DBConnectionPool.java:233)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBManager.getDBManager(DBManager.java:184)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.<init>(JDBCStore.java:112)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)  
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)  
      at java.lang.Class.newInstance0(Class.java:372)  
      at java.lang.Class.newInstance(Class.java:325)  
      at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:169)  
      at com.sun.messaging.jmq.jmsserver.Globals.getStore(Globals.java:995)  
      at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:955)  
      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:456)  
      at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:2151)  
 Caused by: java.sql.SQLException: JZ0PN: Specified port number of -1 was out of range. Port numbers must meet the following conditions: 0 <= portNumber <= 65535  
      at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2780)  
      at com.sybase.jdbc4.jdbc.SybConnection.handleSQLE(SybConnection.java:2648)  
      at com.sybase.jdbc4.jdbc.SybConnection.tryLogin(SybConnection.java:479)  
      at com.sybase.jdbc4.jdbc.SybConnection.handleHAFailover(SybConnection.java:3109)  
      at com.sybase.jdbc4.jdbc.SybConnection.<init>(SybConnection.java:328)  
      at com.sybase.jdbc4.jdbc.SybDriver.createConnection(SybDriver.java:681)  
      at com.sybase.jdbc4.jdbc.SybDriver.connect(SybDriver.java:620)  
      at com.sybase.jdbc4.jdbc.SybDriver.connect(SybDriver.java:653)  
      at com.sybase.jdbc4.jdbc.SybDataSource.getConnection(SybDataSource.java:320)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.newConnection(CommDBManager.java:782)  
      ... 17 more|#]  
Pokud se budete snažit použít některý ze starších ovladačů od Sybase, například verze 6.0, bude v logu chyba následující:
 Local broker:ERROR [B3000]: Could not open persistent message store:  
 com.sun.messaging.jmq.jmsserver.util.BrokerException: Failed to open persistent store.  
      at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:219)  
      at com.sun.messaging.jmq.jmsserver.Globals.getStore(Globals.java:995)  
      at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:955)  
      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:456)  
      at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:2151)  
 Caused by: com.sybase.jdbc3.utils.UnimplementedOperationException: The method com.sybase.jdbc3.jdbc.SybDatabaseMetaData.getSQLStateType() has not been completed and should not be called.  
      at com.sybase.jdbc3.jdbc.ErrorMessage.raiseRuntimeException(ErrorMessage.java:950)  
      at com.sybase.jdbc3.utils.Debug.notImplemented(Debug.java:387)  
      at com.sybase.jdbc3.jdbc.SybDatabaseMetaData.getSQLStateType(SybDatabaseMetaData.java:2261)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.initDBMetaData(CommDBManager.java:177)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBManager.getDBManager(DBManager.java:185)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.<init>(JDBCStore.java:112)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)  
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)  
      at java.lang.Class.newInstance0(Class.java:372)  
      at java.lang.Class.newInstance(Class.java:325)  
      at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:169)  
      ... 4 more|#]  

2) Abychom mohli používat JDBC ovladač pro Sybase ASE uvedený v kroku 1, musíme o něm dát JMS brokerovi vědět. To uděláme v souboru ~/glassfish3122/mq/etc/imqenv.conf přidáním řádku s cestou k ovladači: IMQ_DEFAULT_EXT_JARS=~/glassfish3122/glassfish/lib/jconn4-7.0.7.jar V uvedeném umístění samozřejmě musí tento soubor existovat.
Pokud cesta k ovladači nebude uvedena správně, objeví se v logu chyba:
 Local broker: ERROR [B3000]: Could not open persistent message store:  
 com.sun.messaging.jmq.jmsserver.util.BrokerException: [B3024]: Failed to load JDBC driver: com.sybase.jdbc4.jdbc.SybDriver  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.initDBDriver(CommDBManager.java:495)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBManager.<init>(DBManager.java:259)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBManager.getDBManager(DBManager.java:182)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.<init>(JDBCStore.java:112)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)  
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)  
      at java.lang.Class.newInstance0(Class.java:372)  
      at java.lang.Class.newInstance(Class.java:325)  
      at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:169)  
      at com.sun.messaging.jmq.jmsserver.Globals.getStore(Globals.java:995)  
      at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:955)  
      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:456)  
      at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:2151)  
 Caused by: java.lang.ClassNotFoundException: com.sybase.jdbc3.jdbc.SybDriver  
      at java.net.URLClassLoader$1.run(URLClassLoader.java:366)  
      at java.net.URLClassLoader$1.run(URLClassLoader.java:355)  
      at java.security.AccessController.doPrivileged(Native Method)  
      at java.net.URLClassLoader.findClass(URLClassLoader.java:354)  
      at java.lang.ClassLoader.loadClass(ClassLoader.java:423)  
      at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)  
      at java.lang.ClassLoader.loadClass(ClassLoader.java:356)  
      at java.lang.Class.forName0(Native Method)  
      at java.lang.Class.forName(Class.java:186)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.initDBDriver(CommDBManager.java:467)  
      ... 14 more|#]  

3) Jelikož JMS broker nepodporuje Sybase ASE je třeba pro Sybase ASE vytvořit databázové schema. Na konec souboru ~/glassfish3122/glassfish/domains/domain1/imq/instances/imqbroker/props/config.properties je třeba přidat následující položky:
 # Beginning of properties to plug in a Sybase ASE database  
 #  
 # User name used to open database connection. Replace username.  
 #imq.persist.jdbc.sybase.user=<username>  
 # Optional property to specify whether the database requires a password.  
 #imq.persist.jdbc.sybase.needpassword=[true|false]  
 # Vendor specific JDBC driver.  
 imq.persist.jdbc.sybase.driver=com.sybase.jdbc3.jdbc.SybConnectionPoolDataSource  
 # Vendor specific properties.  
 # Vendor specific database url to get a database connection.  
 # Replace hostname, port and sid in imq.persist.jdbc.oracle.property.url.  
 # imq.persist.jdbc.oracle.property.url=jdbc:oracle:thin:@<hostname>:<port>:<sid>  
 imq.persist.jdbc.sybase.property.implicitCachingEnabled=true  
 imq.persist.jdbc.sybase.property.maxStatements=25  
 # Properties to define the tables used by the broker. Do not modify the schema.  
 # Version table  
 imq.persist.jdbc.sybase.table.MQVER41=\  
      CREATE TABLE ${name} (\  
        STORE_VERSION INTEGER NOT NULL,\  
        LOCK_ID VARCHAR(100),\  
        PRIMARY KEY(STORE_VERSION))  
 # Configuration change record table  
 imq.persist.jdbc.sybase.table.MQCREC41=\  
      CREATE TABLE ${name} (\  
        RECORD IMAGE NOT NULL,\  
        CREATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(CREATED_TS))  
 # Broker table  
 imq.persist.jdbc.sybase.table.MQBKR41=\  
      CREATE TABLE ${name} (\  
        ID VARCHAR(100) NOT NULL,\  
        URL VARCHAR(100) NOT NULL,\  
        VERSION INTEGER NOT NULL,\  
        STATE INTEGER NOT NULL,\  
        TAKEOVER_BROKER VARCHAR(100),\  
        HEARTBEAT_TS NUMERIC,\  
        PRIMARY KEY(ID))  
 # Store session table  
 imq.persist.jdbc.sybase.table.MQSES41=\  
      CREATE TABLE ${name} (\  
        ID BIGINT NOT NULL,\  
        BROKER_ID VARCHAR(100) NOT NULL,\  
        IS_CURRENT INTEGER NOT NULL,\  
        CREATED_BY VARCHAR(100) NOT NULL,\  
        CREATED_TS BIGINT NOT NULL,\  
        PRIMARY KEY(ID))  
 imq.persist.jdbc.sybase.table.MQSES41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (\  
        BROKER_ID)  
 imq.persist.jdbc.sybase.table.MQSES41.index.IDX2=\  
      CREATE INDEX ${index} ON ${name} (\  
        BROKER_ID, IS_CURRENT)  
 # Destination table  
 imq.persist.jdbc.sybase.table.MQDST41=\  
      CREATE TABLE ${name} (\  
        ID VARCHAR(100) NOT NULL,\  
        DESTINATION IMAGE NOT NULL,\  
        IS_LOCAL INTEGER NOT NULL,\  
        CONNECTION_ID NUMERIC NULL,\  
        CONNECTED_TS NUMERIC,\  
        STORE_SESSION_ID NUMERIC,\  
        CREATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(ID))  
 imq.persist.jdbc.sybase.table.MQDST41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (\  
        STORE_SESSION_ID)  
 # Interest (consumer) table  
 imq.persist.jdbc.sybase.table.MQCON41=\  
      CREATE TABLE ${name} (\  
        ID NUMERIC NOT NULL,\  
        CLIENT_ID VARCHAR(1024),\  
        DURABLE_NAME VARCHAR(1024),\  
        CONSUMER IMAGE NOT NULL,\  
        CREATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(ID))  
 # Interest list (consumer state) table  
 imq.persist.jdbc.sybase.table.MQCONSTATE41=\  
      CREATE TABLE ${name} (\  
        MESSAGE_ID VARCHAR(100) NOT NULL,\  
        CONSUMER_ID NUMERIC NOT NULL,\  
        STATE INTEGER,\  
        TRANSACTION_ID NUMERIC,\  
        CREATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(MESSAGE_ID, CONSUMER_ID))  
 imq.persist.jdbc.sybase.table.MQCONSTATE41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (\  
        TRANSACTION_ID)  
 imq.persist.jdbc.sybase.table.MQCONSTATE41.index.IDX2=\  
      CREATE INDEX ${index} ON ${name} (\  
        MESSAGE_ID)  
 # Message table  
 imq.persist.jdbc.sybase.table.MQMSG41=\  
      CREATE TABLE ${name} (\  
        ID VARCHAR(100) NOT NULL,\  
        MESSAGE IMAGE NOT NULL,\  
        MESSAGE_SIZE NUMERIC,\  
        STORE_SESSION_ID NUMERIC NOT NULL,\  
        DESTINATION_ID VARCHAR(100),\  
        TRANSACTION_ID NUMERIC,\  
        CREATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(ID))  
 imq.persist.jdbc.sybase.table.MQMSG41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (\  
        STORE_SESSION_ID, DESTINATION_ID)  
 # Property table  
 imq.persist.jdbc.sybase.table.MQPROP41=\  
      CREATE TABLE ${name} (\  
        PROPNAME VARCHAR(100) NOT NULL,\  
        PROPVALUE IMAGE,\  
        PRIMARY KEY(PROPNAME))  
 # Transaction table  
 imq.persist.jdbc.sybase.table.MQTXN41=\  
      CREATE TABLE ${name} (\  
        ID NUMERIC NOT NULL,\  
        TYPE INTEGER NOT NULL,\  
        STATE INTEGER,\  
        AUTO_ROLLBACK INTEGER NOT NULL,\  
        XID VARCHAR(256),\  
        TXN_STATE IMAGE NOT NULL,\  
        TXN_HOME_BROKER IMAGE,\  
        TXN_BROKERS IMAGE,\  
        STORE_SESSION_ID NUMERIC NOT NULL,\  
        EXPIRED_TS NUMERIC NOT NULL,\  
        ACCESSED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(ID))  
 imq.persist.jdbc.sybase.table.MQTXN41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (\  
        STORE_SESSION_ID)  
 # JMS Bridge TM LogRecord table  
 imq.persist.jdbc.sybase.table.MQTMLRJMSBG41=\  
      CREATE TABLE ${name} (\  
        XID VARCHAR(256) NOT NULL,\  
        LOG_RECORD IMAGE NOT NULL,\  
        NAME VARCHAR(100) NOT NULL,\  
        BROKER_ID VARCHAR(100) NOT NULL,\  
        CREATED_TS NUMERIC NOT NULL,\  
        UPDATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(XID))  
 imq.persist.jdbc.sybase.table.MQTMLRJMSBG41.index.IDX1=\  
      CREATE INDEX ${index} ON ${name} (BROKER_ID)  
 imq.persist.jdbc.sybase.table.MQTMLRJMSBG41.index.IDX2=\  
      CREATE INDEX ${index} ON ${name} (NAME)  
 # JMS Bridges table  
 imq.persist.jdbc.sybase.table.MQJMSBG41=\  
      CREATE TABLE ${name} (\  
        NAME VARCHAR(100) NOT NULL,\  
        BROKER_ID VARCHAR(100) NOT NULL,\  
        CREATED_TS NUMERIC NOT NULL,\  
        UPDATED_TS NUMERIC NOT NULL,\  
        PRIMARY KEY(NAME))  
 # End of properties to plug in an Sybase ASE database  
Některé z víše uvedených položek je samozřejmě možné upravit podle vlastních potřeb nebo přidat další.
Kdybychom toto neudělali skončili bychom s touto chybou v logu:
 Local broker: ERROR [B3000]: Could not open persistent message store:  
 com.sun.messaging.jmq.jmsserver.util.BrokerException: Table definition not found for sybase  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.comm.CommDBManager.loadTableSchema(CommDBManager.java:1215)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.DBManager.getDBManager(DBManager.java:183)  
      at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.<init>(JDBCStore.java:112)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)  
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)  
      at java.lang.Class.newInstance0(Class.java:372)  
      at java.lang.Class.newInstance(Class.java:325)  
      at com.sun.messaging.jmq.jmsserver.persist.StoreManager.getStore(StoreManager.java:169)  
      at com.sun.messaging.jmq.jmsserver.Globals.getStore(Globals.java:995)  
      at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:955)  
      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:456)  
      at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:2151)|#]  
Více o konfiguraci JMS brokeru je uvedeno zde.
Nyní stačí zapnout Glassfish a zkontrolovat zda se v databázi vytvořili tabulky uvedené ve schematu. Poté je už možné využívat JMS.
V případě zájmu si můžete přečíst více o konfiguraci persistentního uložiště pro Oracle Glassfish Message Queue.
Nastaly problémy, které zde nejsou uvedeny? Podělte se o ně v diskuzi níže.