FAQ for using JDBC as the data-store for Open Message Queue Cannot resolve external resource into attachment.If you are interested in contributing to this FAQ, please read the Contribution Guidelines. General JDBCQ: Can I only use High Availability Databases, to benefit from the High Availability features in Open MQ? A: The simple answer is No. Open MQ makes no special demands on the database that you configure it to use. If you have a sufficient level of availability to meet your application requirements, from your standard JDBC datasource, you can use that datasource and configure Open MQ to treat it as an HA database. If you have not redundancy in your database server, when it goes out, JMS service will stop. If you are using transactional messaging, there may be exceptions generated if the database is unavailable. However, messages should never be in an uncertain status. Open MQ is tested for HA use with GlassFish HADB (a component of the Enterprise Profile) and with Oracle. We have completed our tests and everything checked out so we will add support for MySQL Cluster Edition in release 4.2. We have done bench level testing, using MySQL Community Edition with positive results. To configure MySQL for HA, read this Setup Information. If you want redundancy and resiliancy to failure, you must install and configure a database that also has redundancy such as HADB, MySQL Cluster Edition, Etc. This allows your operations to continue even if there's a failure at one of the database server nodes. Q: How do I increase the number of database connections for the JDBC persistent store? A: You can increase the number of database connections from the default value of 5 by setting the broker's property: imq.persist.jdbc.connection.limit=5 Using MySQLQ: Is there a general guide for setting up MySQL with the NDB cluster enginer? A: We have started a guide which illustrates the minimal cluster setup. It also includes information for more advanced settings. This guide is available at this link. Q: How do I configure a broker to use MySQL database as the JDBC-based persistence store? A: Do the following steps: 1. Set the following JDBC-related properties in the broker's instance configuration file: imq.brokerid=<alphanumeric_id> imq.persist.store=jdbc imq.persist.jdbc.dbVendor=mysql imq.persist.jdbc.mysql.user=<db_username> imq.persist.jdbc.mysql.password=<db_user_password> imq.persist.jdbc.mysql.property.url=jdbc:mysql://<hostname>:<port>/<database> 2. Place a copy of MySQL Connector/J JDBC driver in the following location, depending on your platform: Solaris: /usr/share/lib/imq/ext/ Linux: /opt/sun/mq/share/lib/ext/ Windows: IMQ_VARHOME\\lib\\ext 3. Create the database schema needed for Message Queue persistence using the imqdbmgr create tbl command. 4. Start the broker. If you expect to have messages that are larger than 1 Megabytes (1m), make sure that you configure MySQL's default max_allowed_packet variable according to your need when starting the database. For additional information, see Configuring a Persistent Data Store, Broker Configuration section of the Sun Java System Message Queue 4.1 Administration Guide. Q: Do I need to do any special configuration for MySQL Cluster? A: The broker by default is configured to use the the InnoDB Storage Engine because it provides MySQL with a transaction-safe (ACID compliant) storage engine. We do this by using the ENGINE option with the CREATE TABLE statement. To configure the broker to use the NDB Storage Engine, the storage engine that enables clustering in MySQL, you will need to do the following additional steps depending on which version of MQ you've downloaded: For MQ 4.2 RC 1 (build of 20080616), which contains a fix for CR 6713071: 1. Follow the instructions as described above for configuring MySQL database as the JDBC-based persistence store with an additional property added to step 1: imq.persist.jdbc.mysql.tableoption=ENGINE=NDBCLUSTER For prior version of MQ: 1. Edit the broker's default configuration file (default.properties) from Solaris: /usr/share/lib/imq/props/broker/ Linux: /opt/sun/mq/share/lib/props/broker/ Windows: IMQ_VARHOME\\lib\\props\\brokerUWC_TOKEN_DBBS 2. Modify the attribute ENGINE=InnoDB from the table schema properties for MySQL to ENGINE=NDB. For example, change the table schema for the Version table from
imq.persist.jdbc.mysql.table.MQVER41=\
CREATE TABLE ${name} (\
STORE_VERSION INT NOT NULL,\
LOCK_ID VARCHAR(100)) ENGINE=InnoDB
to
imq.persist.jdbc.mysql.table.MQVER41=\
CREATE TABLE ${name} (\
STORE_VERSION INT NOT NULL,\
LOCK_ID VARCHAR(100)) ENGINE=NDB
To avoid changing the broker's default.properties file, you can add the modified properties in the instance configuration file which will override the values specified in the default configuration file. .../instances/instanceName/props/config.properties If the file does not yet exist, you must start the broker by using the -name instanceName option, so that Message Queue can create the file. Using Java DB (Derby)Q: How do I configure a broker to use Derby in Network Server mode as the JDBC-based persistence store? A: By default, the broker is configured to access a Derby database using the Embedded Derby JDBC driver, i.e. the database engine runs inside the same Java Virtual Machine (JVM) as the broker. The following steps will show you how to configure the broker to access a Derby database using the more familiar client/server mode, i.e. Derby Network Server. 1. Set the following JDBC-related properties in the broker's instance configuration file: imq.brokerid=<alphanumeric_id> imq.persist.store=jdbc imq.persist.jdbc.dbVendor=derby imq.persist.jdbc.derby.driver=org.apache.derby.jdbc.ClientDriver imq.persist.jdbc.derby.opendburl=jdbc:derby://<hostname>:<port>/<database>;create=true imq.persist.jdbc.derby.closedburl= imq.persist.jdbc.derby.createdburl= 2. Place a copy of Derby Network Client JDBC driver (derbyclient.jar) that is necessary for communication with the Network Server in the following location, depending on your platform: Solaris: /usr/share/lib/imq/ext/ Linux: /opt/sun/mq/share/lib/ext/ Windows: IMQ_VARHOME\\lib\\ext 3. Start the Network Server if it has not already been started. For additional information, see Derby Network Server, Start Network Server section of the Derby Tutorial. 4. Create the database schema needed for Message Queue persistence using the imqdbmgr create tbl command. 5. Start the broker. |
