Minerva Pools

Once your JDBC driver is installed, you can add one or more connection pools that use it. Any number of EJBs may share one connection pool, but you may want to create multiple pools for a number of reasons. For example, you may want a dedicated pool for an application that requires very high reponse time, while other applications share a pool of limited size.

To add a pool, you need to add sections to jboss.conf and jboss.jcml, both of which can be found in the conf directory.

The JDBC 2.0 Optional Package

Before we configure the pool, we need to take a brief detour into specifications. The JDBC API distributed with JDKs 1.1 through 1.3 defines a transaction for every connection. It is not possible to have more than one connection in a transaction, or to use a single connection for more than one transaction at a time.

Though perfectly adequate for normal use, this falls short of the functionality mandated by the J2EE specification for enterprise applications. In the J2EE environment, beans are allowed to use multiple data source, which may include messaging services, legacy systems, and other non-database sources. Further, all work against all data sources can be committed or rolled back together. This means that a EJBs must be able to use more than one data source per transaction, and in particular more than one connection per transaction.

Thus was born the JDBC 2.0 Optional Package (the API formerly known as the JDBC 2.0 Standard Extension). This API defines the javax.sql package, including interfaces such as DataSource, XADataSource, and XAConnection. Some drivers support this already, though most do not. And some that do support it do not do a very good job yet (some Oracle implementations, in particular, neglect important event notifications).

You must determine whether your driver supports the JDBC 2.0 Optional Package in order to configure JBoss appropriately. If it does not, JBoss will simulate it so that your EJBs will operate appropriately, but there are two important restrictions:

  1. If you request more than one connection from a DataSource in the context of the same transaction, JBoss will return the same connection every time. This is so changes made by one bean will be visible to other beans operating in the same transaction.

  2. The connections cannot determine ahead of time whether it is possible for them to commit, so they cannot participate fully in the two-phase commit protocol used to commit multiple data sources. This means that if there's a problem with one of the data sources, some may commit and others may rollback. This is why we want all DB vendors to fully support the JDBC 2.0 Optional Package.

Minerva configuration

After you have successfully installed your JDBC driver as described in the section called “Installing JDBC Drivers” , you are ready to define pool settings. All pool entries are specified in jboss.jcml file. Take a look at Figure 3.3. pool configuration example. It defines , in jboss.jcml, pool name "DefaultDS" that uses underlying Hypersonic database. All database pool attributes are discussed in details below.

Note that "PoolName" attribute defines jndi name under which this DataSource is going to be bound in java: namespace. Therefore if we used pool defined in Figure 3.3. it would be bound under java:/DefaultDS.

Figure 3.3. Pool configuration MBean example

<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=DefaultDS">
 <attribute name="PoolName">DefaultDS</attribute>
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="Properties"></attribute>
 <attribute name="URL">jdbc:HypersonicSQL:hsql://localhost:1476</attribute>
 <attribute name="GCMinIdleTime">1200000</attribute>
 <attribute name="JDBCUser">sa</attribute>
 <attribute name="MaxSize">10</attribute>
 <attribute name="Password" />
 <attribute name="GCEnabled">false</attribute>
 <attribute name="InvalidateOnError">false</attribute>
 <attribute name="TimestampUsed">false</attribute>
 <attribute name="Blocking">true</attribute>
 <attribute name="GCInterval">120000</attribute>
 <attribute name="IdleTimeout">1800000</attribute>
 <attribute name="IdleTimeoutEnabled">false</attribute>
 <attribute name="LoggingEnabled">false</attribute>
 <attribute name="MaxIdleTimeoutPercent">1.0</attribute>
 <attribute name="MinSize">0</attribute>	
</mbean>

You are not required to specify all pool parameters - Minerva pool is going to provide defaults. The minimum required PoolName, DataSourceClass, JDBC URL, user name, and password:

Figure 3.4. Pool configuration MBean example for Oracle

<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=OracleDB">
 <attribute name="PoolName">OracleDB</attribute>
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="URL">jdbc:oracle:thin:@serverhostname:1521:ORCL</attribute>
 <attribute name="JDBCUser">scott</attribute>
 <attribute name="Password">tiger</attribute>
</mbean>

Connection Pool Parameters

Here is the list of possible parameters for each pool's entry in jboss.jcml. Again, after you run JBoss once with your new pool, it will add entries for all of these to jboss.jcml, using the default values for anything you didn't specify.

Table 3.1. Connection pool parameters

NameConnection pool parametersdefault
URLThe JDBC URL used to connect to the data source 
JDBCUserThe user name used to connect to the data source. 
PasswordThe password used to connect to the data source. 
PropertiesAny properties required to connect to the data source. This should be expressed in a String of the form name=value;name=value;name=value.... 
MinSizeThe minimum size of the pool. The pool always starts with one instance, but if shrinking is enabled the pool will never fall below this size. It has no effect if shrinking is not enabled.0
MaxSizeThe maximum size of the pool. Once the pool has grown to hold this number of instances, it will not add any more instances. If one of the pooled instances is available when a request comes in, it will be returned. If none of the pooled instances are available, the pool will either block until an instance is available, or return null (see the Blocking parameter). If you set this to zero, the pool size will be unlimited.0
BlockingControls the behavior of the pool when all the connections are in use. If set to true, then a client that requests a connection will wait until one is available. If set to false, then the pool will return null immediately (and the client may retry). Note: If you set blocking to false, your client must be prepared to handle null results!true
LoggingEnabledWhether the pool should record activity to the JBoss log. This includes events like connections being checked out and returned. It is generally only useful for troubleshooting purposes (to find a connection leak, etc.).false
GCEnabledWhether the pool should check for connections that have not been returned to the pool after a long period of time. This would catch things like a client that disconnects suddenly without closing database connections gracefully, or queries that take an unexpectedly long time to run. This is not generally useful in an EJB environment, though it may be for stateful session beans that keep a DB connection as part of their state. This is in contrast to the idle timeout, which closes connection that have been idle in the pool.false
GCMinIdleTimeIf garbage collection is enabled, the amount of time (in milliseconds) that must pass before a connection in use is garbage collected - forcibly returned to the pool.1200000 (20m)
GCIntervalHow often garbage collection and shrinking should run (in milliseconds), if they are enabled.120000 (2m)
IdleTimeoutEnabledWhether the pool should close idle connections. This prevents the pool from keeping a large number of connections open indefinitely after a spike in activity. Any connection that has been unused in the pool for longer than this amount of time will be closed. If you do not want the pool to shrink so rapidly, you can set the MaxIdleTimeoutPercent and then some connections will be recreated to replace the closed ones. This is in contrast to garbage collection, which returns connections to the pool that have been checked out of the pool but not returned for a long period of time.false
MaxIdleTimeoutPercentSets the idle timeout percent as a fraction between 0 and 1. If a number of connections are determined to be idle, they will all be closed and removed from the pool. However, if the ratio of objects released to objects in the pool is greater than this fraction, some new objects will be created to replace the closed objects. This prevents the pool size from decreasing too rapidly. Set to 0 to decrease the pool size by a maximum of 1 object per test, or 1 to never replace objects that have exceeded the idle timeout. The pool will always replace enough closed connections to stay at the minimum size.1.0
IdleTimeoutSet the idle timeout for unused connections. If a connection has been unused in the pool for this amount of time, it will be released the next time garbage collection and shrinking are run (see GCInterval).1800000 (30m)
TimestampUsedSets whether object clients can update the last used time. If so, the last used time will be updated for significant actions (executing a query, navigating on a ResultSet, etc.). If not, the last used time will only be updated when the object is given to a client and returned to the pool. This time is important if shrinking or garbage collection are enabled (particularly the latter).false

Connection Pool Configuration Examples and Driver Notes

Here are some sample database pool configuration file exerpts for a variety of database products. Note that your configuration may differ slightly if you're using a different version, different JDBC driver, etc. The parameters you are most likely to need to change are in bold.

  • Oracle 8i with native JDBC 2 Optional Package XADataSource

    • Driver Notes 
                    Extreme Float or Double values will cause SQLExceptions 
                    The Oracle XADataSource requires the Oracle Xid implementation. Other vendor's XADataSource implementation may or may
                    not be able to interoperate.

    • lib/ext: classes12.zip

    • jboss.properties

      jboss.xa.xidclass=oracle.jdbc.xa.OracleXid

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">oracle.jdbc.driver.OracleDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=OracleDB">
  <attribute name="PoolName">OracleDB</attribute>
  <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
  <attribute name="URL">jdbc:oracle:thin:@host.domain.com:1521:instance</attribute>
  <attribute name="JDBCUser">scott</attribute>
  <attribute name="Password">tiger</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml): Oracle8

  • Oracle 7.x,8.x,8i with JDBC 1/2 Wrapper This configuration is reported to be outdated. It is still here for reference for older versions.

    • Driver Notes 
                    For CMP Entity Beans, Oracle 7 only allows 1 serialized Java Object per bean (column type LONG RAW). Oracle 8 does not have
                    this limitation (column type BLOB). 
                    Extreme Float or Double values will cause SQLExceptions 

    • lib/ext: classes12.zip

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">oracle.jdbc.driver.OracleDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=OracleDB">
 <attribute name="PoolName">OracleDB</attribute>
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="URL">jdbc:oracle:thin:@host.domain.com:1521:instance</attribute>
 <attribute name="JDBCUser">scott</attribute>
 <attribute name="Password">tiger</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml): Oracle7 and Oracle8

  • Hypersonic

    • lib/ext: hsql.jar

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">org.hsql.jdbcDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=Hypersonic">	
 <attribute name="PoolName">HypersonicDS</attribute>	
 <attribute name="URL">jdbc:HypersonicSQL:hsql://localhost</attribute>
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="JDBCUser">sa</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml): Hypersonic SQL

  • DB2 7.1

    • Driver Notes 
                    DB2 does not support variables of type "byte", so with CMP entities they will be serialized. We recommend that you use short or int
                    variables to avoid this. 
                    For CMP entities, serialized objects are stored as type BLOB(n), where n is 2000 by default. If you plan to serialize objects larger
                    than 2000 bytes, you will need to alter the mapping or create the table manually. 
                    Extreme Float or Double values will cause SQLExceptions and may corrupt the driver so that further actions fail. 
                    The "net" driver (type 4) is preferred over the "app" driver (type 2), though only the "app" driver has a native XADataSource
                    implementation. To use the "net" driver, you must run the "db2jstrt [port]" tool on your DB server.  

    • lib/ext: db2java.zip

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">COM.ibm.db2.jdbc.net.DB2Driver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=DB2">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">DB2DS</attribute>		
 <attribute name="URL">jdbc:db2://host.domain.com:port/database</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml):DB2

  • DB2/400

    • lib/ext : jt400.jar

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">com.ibm.as400.access.AS400JDBCDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=AS400">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">AS400DS</attribute>		
 <attribute name="URL">jdbc:as400://hostname</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml):DB2/400

  • Sybase Adaptive Server Anywhere 6.x, Adaptive Server Enterprise 11.9.x, 12.x

    • Driver Notes 
                    You must install jConnect 5.2, including the stored procedures which are distributed with the jConnect package. There are
                    directions for this in the Installation Instructions chapter of the jConnect for JDBC Installation Guide. 
                    JAWS cannot create a table automatically for CMP Entity beans (the server rejects DDL within a transaction) 
                    The jConnect 5.2 JDBC driver does not support variables of type byte or short, so they will be serialized (column type must be
                    varbinary or image). We recommend you use int variables instead to avoid this.  

    • lib/ext :jconn2.jar

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">com.sybase.jdbc2.jdbc.SybDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=SybaseDB">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">SybaseDS</attribute>		
 <attribute name="URL">jdbc:sybase:Tds:host.domain.com:4100/database</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml):Sybase

  • PostgreSQL 7.x

    • Driver Notes 
                    Extreme Java "long" values will cause SQLExceptions and may corrupt the driver so that further actions fail.  

    • lib/ext: jdbc7.0-1.2.jar

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">org.postgresql.Driver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=PostgresDB">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">PostgresDS</attribute>		
 <attribute name="URL">jdbc:postgresql://host.domain.com/database</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

      Note: You must include a user name and password. They can be bogus if your PostgreSQL installation is configured to "trust" the machine you're coming from, but you can't leave them out.

    • CMP Type Mapping Names (for jaws.xml):PostgreSQL

  • Interbase 6.x

    • Driver Notes 
                    For CMP entity beans, serialized Java Objects are limited to 2000 bytes by default in automatically generated tables. You may
                    increase this limit by changing the mapping or creating the table manually. 
                    The interclient JDBC driver seems to have trouble checking whether a table exists, so until that is resolved you can't have JAWS
                    create the table anyway.  

    • lib/ext:interclient-core.jar

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">interbase.interclient.Driver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=InterBaseDB">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">InterBaseDS</attribute>		
 <attribute name="URL">jdbc:interbase://host.domain.com/path/to/database.gdb</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml):InterBase

  • mySQL 3.23.24-beta

    • Notes:
               mySQL does not support transactions before version 3.23.15 (experimental support). Check the consequences for your configuration. 
               get mm.mysql-2.0.2-bin.jar from http://www.worldserver.com/mm.mysql/
               copy the jar to lib/ext/

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">org.gjt.mm.mysql.Driver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=mySQLDB">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">mySQLDS</attribute>		
 <attribute name="URL">jdbc:mysql://host/databasename</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • CMP Type Mapping Names (for jaws.xml):mySQL

  • Microsoft Jet Engine/Access 97

    • Driver Notes 
                    This example uses Sun's Jdbc-Odbc bridge. This type 1 JDBC driver is very convenient if you start working with JBoss-Jet
                    Engine. It can be slow under heavy loads and should be replaced in high-load production environments. Also, the driver supports
                    only JDBC 1, so JDBC 2.0 types like CLOB cannot be used. 
                    The ODBC data source can be created using Control Panel - ODBC Data Sources. 
                    You can let Access and JBoss use the datasource at the same time. To do this, start JBoss first, then start Access. Access will
                    open the datasource in Shared Mode. You can now use Access 97 as editor, viewer, bulk importer/exporter and query builder
                    while JBoss can be stopped and started concurrently.  

    • lib/ext :Sun JRE's rt.jar if your not running on a Sun virtual machine, otherwise none

    • jboss.jcml

      <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
 <attribute name="Drivers">sun.jdbc.odbc.JdbcOdbcDriver</attribute>
</mbean>

    • jboss.jcml

       
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=JetEngineDB">
 <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute>
 <attribute name="PoolName">InterBaseDS</attribute>		
 <attribute name="URL">jdbc:odbc:ODBC datasource name</attribute>
 <attribute name="JDBCUser">username</attribute>
 <attribute name="Password">password</attribute>
</mbean>

    • Note: a default Jet Engine data source has no user and password, therefor the JDBCUser and Password attributes are empty. If you need a user or a password, see the other examples.

      You can download this mapping here (if using JdbcOdbc bridge driver) or here (if using JDBC 2.0 compliant driver). Add the contents to your jaws.xml in the type-mappings section.

    • CMP Type Mapping Names (for jaws.xml):MS Jet Engine