Using Database Stores : Configuring Database Stores

Configuring Database Stores
This section describes the steps required to configure and deploy database stores. For general conceptual information about the multiple store feature, see Store Messages in Multiple Stores.
Settings for creating and configuring database stores are managed in the EMS server, and are transparent to clients. To configure the database stores feature, follow these steps:
1.
Enable the database store feature in the tibemsd.conf by setting the parameters:
For detailed information about the dbstore parameters, see Configuration in tibemsd.conf. The jre_library parameter, which enables the JVM in the EMS server, is described in JVM Parameters.
2.
You can create multiple database stores, or a combination of database and file-based stores. Each store must have a unique name. Parameters determine whether the store is a database store, provide the location of the database server, and specify the username and password that the EMS server uses to access the database.
For a list of database store parameters, see Configuration in stores.conf below.
3.
Messages are sent to different stores according to their destinations. You associate a destination with a specific store using the store parameter in the topics.conf and queues.conf files. You can also change store associations using the setprop topic or setprop queue command in the EMS Administration Tool.
Multiple destinations can be mapped to the same store, either explicitly or using wildcards. Even if no stores are configured, the server sends persistent messages that are not associated with a store to default stores. See Default Store Files for more information.
For details about the store parameter, see store on page 70.
4.
When the EMS server is configured to store messages in a database, the database schema must be exported before the server is started. Use the EMS Schema Export Tool to create, drop, and update the database tables.
For details, see EMS Schema Export Tool.
Configuration in tibemsd.conf
These parameters are set in the tibemsd.conf configuration file.
dbstore_classpath
dbstore_classpath = pathname
Includes all the JAR files required by the EMS server when employing the database store feature. This parameter must be set when a store of type dbstore has been created in the stores.conf file.
Required JAR files are determined by the installed Hibernate release, and are documented in the README.txt file that is located in the lib/ directory of the Hibernate distribution. Many of these JAR files are version-specific, and the required versions may change with new Hibernate releases. You should verify the required version and modify the dbstore_classpath variable accordingly.
If you are using Hibernate release 3.2.5, for example, the dbstore_classpath should include paths to the following JAR files:
For an example, see EMS_HOME/samples/config/tibemsd-db.conf.
dbstore_driver_name
dbstore_driver_name = name
Specifies the name of the JDBC driver used by Hibernate.
For example:
dbstore_driver_name=com.mysql.jdbc.Driver
dbstore_driver_name= com.microsoft.sqlserver.jdbc.SQLServerDriver
dbstore_driver_name=oracle.jdbc.driver.OracleDriver
dbstore_driver_name=com.ibm.db2.jcc.DB2Driver
dbstore_driver_dialect
dbstore_driver_dialect = dialect
Specifies the Hibernate SQL dialect used to construct SQL commands.
For example, if you are using the MySQL with InnoDB database server:
dbstore_driver_dialect = org.hibernate.dialect.MySQLInnoDBDialect
The SQL dialect is defined by Hibernate. For a list of databases and the associated dialects, see the readme.txt file located in the Hibernate install directory archive.
Configuration in stores.conf
This section describes parameters configured for each database store in the stores.conf file. The stores.conf includes definitions for both database and file-based stores. For information about configuring file-based stores, see stores.conf on page 237.
The format of the file is:
[store_name] # mandatory -- square brackets included.
  type = dbstore
  dbstore_driver_url = JDBCURL
  dbstore_driver_username = username
  dbstore_driver_password = password
 
[store_name]
[store_name] is the name that identifies this store configuration.
Note that the square brackets [ ] DO NOT indicate that the store_name is an option; they must be included around the name.
file — for file-based stores.
dbstore — for database stores.
Provides the location of the database server. The URL entered uses the syntax specified by the JDBC driver for your database.
Please see documentation specific to your JDBC driver for more information. If you are using an Oracle RAC database, also see Using a TAF Configured URL.
The password that the server uses, in conjunction with the username provided in dbstore_driver_username, to access the database.
You can mangle this and other passwords by way of the tibemsadmin tool. See Table 15, tibemsadmin Options for more information about using tibemsadmin to mangle passwords.
Example Using MySQL Server
[$sys.failsafe]
  type=dbstore
  dbstore_driver_url=jdbc:mysql://mysqlsrv_1:3306/sysfs
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
 
[$sys.meta]
  type=dbstore
  dbstore_driver_url=jdbc:mysql://mysqlsrv_1:3306/sysmeta
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
Example Using Microsoft SQL Server
[$sys.meta]
  type=dbstore
  dbstore_driver_url=jdbc:sqlserver://sqlsrv_1:3415;databaseName=sysmeta
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
 
[$sys.failsafe]
  type=dbstore
  dbstore_driver_url=jdbc:sqlserver://sqlsrv_1:3415;databaseName=sysfs
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
Example Using Oracle 10g
[$sys.meta]
  type=dbstore
  dbstore_driver_url=jdbc:oracle:thin:adminmeta/admin123@//osrv_1:1521/orclperf
  dbstore_driver_username=adminmeta
  dbstore_driver_password=admin123
 
[$sys.failsafe]
  type=dbstore
  dbstore_driver_url=jdbc:oracle:thin:adminfs/admin123@//osrv_1:1521/orclperf
  dbstore_driver_username=adminfs
  dbstore_driver_password=admin123
Example Using Oracle RAC 10g
[$sys.failsafe]
  type=dbstore
  dbstore_driver_url=jdbc:oracle:oci:<user>/<passwd>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host1>)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=<host2>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
For more information, see Configuration for the Oracle RAC Database below.
Example Using IBM DB2 Server
[$sys.meta]
  type=dbstore
  dbstore_driver_url=jdbc:db2://db2srv_1:50000/SYSMETA
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
 
[$sys.failsafe]
  type=dbstore
  dbstore_driver_url=jdbc:db2://db2srv_1:50000/SYSFS
  dbstore_driver_username=admin
  dbstore_driver_password=admin123
Configuration for the Oracle RAC Database
The TIBCO Enterprise Message Service server must connect to the Oracle RAC 10g database using the Oracle JDBC OCI driver and TAF configuration.
Installing the OCI Driver
We recommend using the Oracle Instant Client, which is an optimized light-weight OCI driver package available from Oracle:
http://www.oracle.com/technology/tech/oci/instantclient/index.html
Follow the instructions provided to install the Oracle Instant Client.
Using a TAF Configured URL
To ensure that the EMS server does not lose its connection to the database during a database failover, the server should connect to the database using a Transparent Application Failover (TAF) configured URL. For example:
jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))