How to configure JDBC DataSources in JOnAS ?

Configuring DataSources

For both container-managed or bean-managed persistence, JOnAS makes use of relational storage systems through the JDBC interface.

JDBC connections are obtained from DataSource (JDBC standard extensions), provided at the application server level.

A DataSource object identifies a database and a means to access it via JDBC (a JDBC driver).

An application server may request access to several databases and thus provide the corresponding DataSource objects that will be registered in JNDI registry.

JOnAS provides a generic driver-wrapper that emulates the XADataSource interface on a regular JDBC driver. It is important to note that this driver-wrapper does not ensure a real two-phase commit for distributed database transactions.

Neither the EJB specification nor the Java EE specification describe how to define DataSource objects so that they are available to a Java EE platform. Therefore, this document, which describes how to define and configure DataSource objects, is specific to JOnAS. However, the way to use these DataSource objects in the Application Component methods is standard, that is, by using the resource manager connection factory references.

A DataSource object should be defined in a file called .properties.

Example  

Oracle1.properties for an Oracle datasource

Postgres.properties for an PostgreSQL datasource

These files must be located in $JONAS_BASE/conf directory.

In the jonas.properties file, to define a DataSource "Oracle1.properties" add the name "Oracle1" to the line jonas.service.dbm.datasources, as follows :

jonas.service.dbm.datasources  Oracle1, Sybase, PostgreSQL

The property file defining a DataSource may contain two types of information : 

• connection properties
• JDBC Connection Pool properties

Connection properties

datasource.name  : JNDI name of the DataSource

datasource.url  : The JDBC database URL jdbc:<database_vendor_subprotocol>:..

datasource.classname  : Name of the class implementing the JDBC driver

datasource.username  : Database username

datasource.password  : Database password

datasource.isolationLevel  : Database isolation level for transactions.

Possible values are: none, serializable, read_committed, read_uncommitted, repeatable_read

The default depends on the database used.

datasource.mapper  : JORM database mapper

Note If this datasource is used as a persistence unit, the persistence configuration defined in the persistence.xml file must be coherent to those properties, such as the datasource name and the dialect.

Connection Pool properties

Each Datasource is implemented as a connection manager and manages a pool of JDBC connections.

The pool can be configured via some additional properties described in the following table.

All these settings have default values and are not required. All these attributes can be reconfigured when JOnAS is running, with the console JonasAdmin.

Properties

jdbc.connchecklevel

JDBC connection checking level : 

 0 : no check

 1 : check connection still open

 2 : call the test statement before reusing a connection from the pool

Default : 1

jdbc.connteststmt

Test statement in case jdbc.connchecklevel = 2.

Default : select 1

jdbc.connmaxage

nb of minutes a connection can be kept in the pool.

After this time, the connection will be closed, if minconpool limit has not been reached.

Default : 1440 mn (= 1 day)

jdbc.maxopentime

Maximum time (in mn) a connection can be left busy. If the caller has not issued a close() during this time, the connection will be closed automatically.

Default : 1440 mn (= 1 day)

jdbc.minconpool

Minimum number of connections in the pool.

Setting a positive value here ensures that the pool size will not go below this limit during the datasource lifetime.

Default : 0

jdbc.maxconpool

Maximum number of connections in the pool.

Limiting the max pool size avoids errors from the database.

Default : no limit

jdbc.samplingperiod

Sampling period for JDBC monitoring. nb of seconds between 2 measures.

Default : 60 sec

jdbc.maxwaittime

Maximum time (in seconds) to wait for a connection in case of shortage.

This is valid only if maxconpool has been set.

Default : 10 sec

jdbc.maxwaiters

Maximum of concurrent waiters for a JDBC Connection. This is valid only if maxconpool has been set.

Default : 1000

jdbc.pstmtmax

Maximum number of prepared statements cached in a Connection.

Setting this to a bigger value (120 for example) will lead to better performance, but will use more memory.

The recommendation is to set this value to the number of different queries that are used the most often.

This is to be tuned by administrators.

Default : 12

How the pool works ?

When a user requests a jdbc connection, the dbm connection manager first checks to see if a connection is already open for its transaction. If not, it tries to get a free connection from the free list. If there are no more connections available, the dbm connection manager creates a new jdbc connection (if jdbc.maxconpool is not reached).

If it cannot create new connections, the user must wait (if jdbc.maxwaiters is not reached) until a connection is released. After a limited time (jdbc.maxwaittime), the getConnection returns an exception.

When the user calls close() on its connection, it is put back in the free list.

Many statistics are computed (every jdbc.samplingperiod seconds) and can be viewed by JonasAdmin. This is useful for tuning these parameters and for seeing the server load at any time.

When a connection has been open for too long a time (jdbc.connmaxage), the pool will try to release it from the freelist. However, the dbm connection manager always tries to keep open at least the number of connections specified in jdbc.minconpool.

When the user has forgotten to close a jdbc connection, the system can automatically close it, after jdbc.maxopentime minutes.

Note that if the user tries to use this connection later, thinking it is still open, it will return an exception (socket closed).

When a connection is reused from the freelist, it is possible to verify that it is still valid. This is configured in jdbc.connchecklevel. The maximum level is to try a dummy statement on the connection before returning it to the caller. This statement is configured in jdbc.connteststmt

DataSource example

Here is the template for an Oracle dataSource.properties file that can be found in $JONAS_ROOT/conf :

####### Oracle DataSource configuration example

### DataSource configuration

datasource.name  jdbc_1

datasource.url   jdbc:oracle:thin:@<your-hostname>:1521:<your-db>

datasource.classname   oracle.jdbc.driver.OracleDriver

datasource.username   <your-username>

datasource.password   <user-password>

datasource.mapper   rdb.oracle

####### ConnectionManager configuration

### JDBC connection checking level.

# 0 = no special checking |
# 1 = check physical connection is still open before reusing it | 

# 2 = try every connection before reusing it

jdbc.connchecklevel   0

# Max age for jdbc connections - nb of minutes a connection can be kept in the pool

jdbc.connmaxage  1440

# Maximum time (in mn) a connection can be left busy.

# If the caller has not issued a close() during this time, the connection will be closed automatically.

jdbc.maxopentime   60

# Test statement

jdbc.connteststmt   select * from dual

# JDBC Connection Pool size - Limiting the max pool size avoids errors from database.

jdbc.minconpool   10

jdbc.maxconpool   30

# Sampling period for JDBC monitoring : nb of seconds between 2 measures.

jdbc.samplingperiod   30

# Maximum time (in seconds) to wait for a connection in case of shortage. This may occur only when maxconpool is reached.

jdbc.maxwaittime   5

# Maximum of concurrent waiters for a JDBC Connection. This may occur only when maxconpool is reached.

jdbc.maxwaiters   100