DataSource configuration

This article described how to configure a DataSource for the Orion Application Server either using a real JDBC 2.0 DataSource or by using Orion's DataSource emulation driver.

Introduction
General deployment settings
Configuring a vendor implementation of a DataSource
Configuring Emulation of DataSource
Using a DataSource
Debugging JDBC usage
Sample DataSource configurations

1 Introduction

The goal of this article is to explain how to configure a DataSource configuration file for the Orion Application Server.

The document describes both how to use real JDBC 2.0 DataSource or Orion's DataSource wrappers for Connection drivers.

DataSources was introduced in JDBC 2.0 as an easier way of obtaining a JDBC connection and shielding the developer from issues regarding configuration such as pooling of connections. With the usage of JDBC 2.0 DataSources, the actual configuration of the DataSource is handled outside of the application using the DataSource, leaving the developer with the single task of obtaining the DataSource through JNDI.

As a lot of vendors are still lacking support for DataSources Orion includes a DataSource wrapper that works as an emulator for JDBC connection drivers and optional pooling for these.

Even if using a real JDBC 2.0 DataSource, Orion wrappers should be used if these DataSource are to be used for EJBs utilizing CMT (Container Managed Transactions).

An introduction to JDBC 2.0 and optional package for DataSources can be found here.

2 General deployment settings

A DataSource can be deployed so that it is available to all applications of a certain server, or so that it is only available to a certain application. A DataSource configuration file can span over multiple DataSource configuration entries.

To deploy an DataSource globally, the DataSource entry should be added to the global data-sources.xml file.

The data-sources.xml file is normally located in the /orion/config/ directory.

To deploy a application specific DataSource, the location of the data-sources.xml configuration file should be modified in the applications orion-applicaton.xml file to point to a application specific DataSource configuration file.

The orion-application.xml file is normally located in the deployment directory of the application.

If a DataSource is removed, any application that has a EJB-module utilizing this DataSource needs to be redeployed, or be manually updated to point at the new DataSource name to use.

A description of the tags the data-sources.xml consists of can be found here.

3 Configuring a vendor implementation of a DataSource

If a vendor's DataSource implementation is available, Orion can be configured to take advantage of this vendor specific implementation in a number of different ways.

Even if a vendor implementation of a DataSource is used, Orion must be set up to wrap this DataSource in order to handle EJBs with CMT (Container Managed Transactions), as described in the subsection below.

In all the scenarios listed below, Orion instantiates the vendor's DataSource implementation. The scenarios listed below is not for referencing an external instance of a DataSource, that information is available here.

Configuration scenarios:

Configuring a vendor DataSource for common usage
Configuring a vendor pooling DataSource for common usage
Configuring a vendor XA Datasource for common usage
Configuring a vendor DataSource for pooling usage
Configuring a vendor XA DataSource for EJB or CMT usage

3.1 Configuring a vendor DataSource for common usage

In order to use a vendor implementation of a DataSource for common usage, no special wrappers needs to be used. The only thing needed is to add a DataSource configuration entry for the vendor's DataSource.

Below is an example of such a setup using Inet Opta 2000 TdsDataSource for MS SQL Server 7.0:

The DataSource configuration listed above creates a Inet Opta2000 TdsDataSource and binds it to the JNDI location " jdbc/vendorCoreDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this information.

More information about setting up a Database schema configuration is available here.

3.2 Configuring a vendor pooling DataSource for common usage

In order to use a vendor implementation of a pooling DataSource (implementing javax.sql.ConnectionPoolDataSource), no special wrappers needs to be used. The only thing needed is to add a DataSource configuration entry for the vendor's pooled DataSource.

Below is an example of such a setup using Inet Opta 2000 PDataSource for MS SQL Server 7.0:

<data-sources>

<data-source
name="vendorPooled"
location="jdbc/vendorPooledDS"
class="com.inet.tds.PDataSource"
max-connections="10"
min-connections="2"
inactivity-timeout="60"
staleness-timeout="0"
alive-poll-query="query"
wait-timeout="30"
username="sa"
password=""
host="localhost"
schema="database-schemas/ms-sql.xml"
>
<property name="databaseName" value="sample"/>
</data-source>
</data-sources>

Listing 2: A sample DataSource entry.

The DataSource configuration entry listed above creates a Inet Opta2000 PDataSource and binds it to the JNDI location " jdbc/vendorPooledDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this information.

The value of the max-connections attribute is the maximum number of open connections the pool will hold.

The value of the min-connections attribute is the minimum number of open connections the pool will hold, with a default value of "0"

The value of the inactivity-timeout attribute is the time (in seconds) open connections should be cached before they are closed.

The value of the wait-timeout attribute is the time (in seconds) to wait for a free connection if the pool is used up (i.e. reached max-connections used), with a default value of "60".

The value of the staleness-timeout attribute is the time (in seconds) that a open connection will be allowed to wait in the pool before it is replaced, thereby illiminating issues where the DB is revoking connections due to inactivity.

3.3 Configuring a vendor XA Datasource for common usage

In order to use a vendor implementation of a XA DataSource (implementing javax.sql.XADataSource) for common usage, no special wrappers needs to be used. The only thing needed is to add a DataSource configuration entry for the vendor's XA DataSource.

Below is an example of such a setup using Inet Opta 2000 PDataSource for MS SQL Server 7.0:

<data-sources>

<data-source
name="vendor"
location="jdbc/vendorXACoreDS"
class="com.inet.tds.XDataSource"
username="sa"
password=""
host="localhost"
schema="database-schemas/ms-sql.xml"
>
<property name="databaseName" value="sample"/>
</data-source>
</data-sources>

Listing 3: A common DataSource configuration sample.

The DataSource configuration listed above creates a Inet Opta2000 XDataSource and binds it to the JNDI location " jdbc/vendorXACoreDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this information.

3.4 Configuring a vendor DataSource for pooling usage

In order to give a vendor implementation of a DataSource pooling ability, the DataSource can be wrapped by the >OrionPooledDataSource class.

Below is an example of such a setup using Inet Opta 2000 TdsDataSource for MS SQL Server 7.0:

<data-sources>

<data-source
name="vendor"
location="jdbc/vendorCoreDS"
class="com.inet.tds.TdsDataSource"
username="sa"
password=""
host="localhost"
schema="database-schemas/ms-sql.xml"
>
<property name="databaseName" value="sample"/>
</data-source>


<data-source
name="default"
location="jdbc/defaultCoreDS"
class="com.evermind.sql.OrionPooledDataSource"
source-location="jdbc/vendorCoreDS"
max-connections="10"
min-connections="2"
inactivity-timeout="60"
wait-timeout="30"
staleness-timeout="20"
alive-poll-query="query"
schema="database-schemas/ms-sql.xml"
/>
</data-sources>

Listing 4: A pooled DataSource entry example.

The first DataSource configuration entry listed above creates a Inet Opta2000 DataSource and binds it to the JNDI location "jdbc/vendorCoredDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this value.

The second DataSource configuration entry listed above creates a OrionPooledDataSource on top of the first DataSource and binds it to the JNDI location "jdbc/defaultCoreDS". As this DataSource needs to be used on top of a existing DataSource, the JNDI location value of the source-location attribute is given a value of "jdbc/vendorCoredDS", which will point it to the first DataSource (see above).

The value of the max-connections attribute will be the maximum number of open connections the pool will hold.

The value of the min-connections attribute will be the minimum number of open connections the pool will hold, with a default value of "0"

The value of the inactivity-timeout attribute will be the time (in seconds) open connections should be cached before they are closed.

The value of the wait-timeout attribute will be the time (in seconds) to wait for a free connection if the pool is used up (i.e. reached max-connections used), with a default value of "60".

The value of the alive-poll-query attribute is a optional query to detect if a connection is still alive. If specified, this query will be executed whenever a connection is requested. This attribute is not stated by default. An example query for Oracle DataSource's would be "select * from dual", where dual is a system table.

3.5 Configuring a vendor XA DataSource for EJB or CMT usage

In order to use a vendor implementation of a XA DataSource for EJB or CMT usage, the DataSource has to be wrapped by the OrionCMTDataSource class in order for Orion to add support for distributed transactions and pooling as necessary.

This setup requires two DataSource configuration entries; one defining the vendor XA DataSource and another defining the wrapping Orion DataSource (OrionCMTDataSource) for EJB or CMT usage.

Below is an example of such a setup using Inet Opta 2000 XDataSource for MS SQL Server 7.0:

<data-sources>

<data-source
name="vendorXA"
location="jdbc/vendorXACoreDS"
class="com.inet.tds.XDataSource"
username="sa"
password=""
host="localhost"
>
<property name="databaseName" value="sample"/>
</data-source>


<data-source
name="default"
location="jdbc/defaultCoreDS"
class="com.evermind.sql.OrionCMTDataSource"
xa-source-location="jdbc/vendorXACoreDS"
schema="database-schemas/ms-sql.xml"
/>
</data-sources>

Listing 5: A sample XA Datasource for EJB or CMT usage DataSource entry.

The first DataSource configuration entry listed above creates a Inet Opta2000 DataSource and binds it to the JNDI location " jdbc/vendorXACoredDS". The name of the database to use is stated in a parameter name-value pair as this parameter is not part of the normal parameters defined for data-source.xml entries, but the vendor's implementation needs this value.

The second DataSource configuration entry listed above creates a OrionCMTDataSource on top of the first DataSource and binds it to the JNDI location " jdbc/defaultCoreDS". As this DataSource needs to be used on top of a existing XA DataSource, the JNDI location value of the xa-source-location attribute is given a value of " jdbc/vendorXACoredDS", which will point it to the first DataSource (see above).

The second DataSource configuration entry listed above would become the default DataSource for all applications deployed after this DataSource descriptor has been deployed as its the first DataSource listed to use the OrionCMTDataSource.

4 Configuring Emulation of DataSource

If the vendor's implementation does not include a DataSource, Orion can be configured to emulate DataSource over Connection Drivers in a number of different ways.

Below are the configuration scenarios:

Configuring a DataSource emulation for common usage
Configuring a DataSource emulation for pooling usage
Configuring a DataSource emulation for XA usage
Configuring a DataSource emulation for EJB or CMT usage

4.1 Configuring a DataSource emulation for common usage

In order to use a vendor implementation of a Connection driver for common usage as if it were a DataSource, Orion wants to wrap this Connection with a DataSource emulator of the type DriverManagerDataSource.

The DriverManagerDataSource is a DataSource that pulls the connections from the java.sql.DriverManager (pre-DataSource way of retrieving connections).

Below is an example of such a setup using HypersonicSQL:s JDBC driver:

<data-sources>

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Hypersonic"
location="jdbc/HypersonicCoreDS"
connection-driver="org.hsql.jdbcDriver"
username="sa"
password=""
url="jdbc:HypersonicSQL:./database/sampledb"
inactivity-timeout="30"
schema="database-schemas/hypersonic.xml"
/>
</data-sources>

Listing 6: A sample DataSource entry.

4.2 Configuring a DataSource emulation for pooling usage

In order to use a vendor implementation of a Connection driver as if it were a pooling DataSource, Orion wants to wrap this Connection with a DataSource emulator of the type DriverManagerDataSource. This DataSource is then extended with the pooling abilities of the OrionPooledDataSource added to the specified location.

Below is an example of such a setup using Oracle's JDBC driver:

<data-sources>

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="OracleDS"
location="jdbc/OracleCoreDS"
pooled-location="jdbc/OraclePooledDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="sa"
password=""
url="jdbc:oracle:thin:@localhost:1521:oracle"
max-connections="5"
min-connections="2"
inactivity-timeout="30"
schema="database-schemas/oracle.xml"
/>
</data-sources>

Listing 7: A sample DataSource entry.

The DataSource configuration entry listed above creates a DriverManagerDataSource bound to the location " jdbc/OracleCoreDS" that pulls its connections from the Driver Manager located at " oracle.jdbc.driver.OracleDriver". By adding a pooled-location attribute Orion will bind a OrionPooledDataSource to the location " jdbc/OraclePooledDS". The DataSource at the pooled-location will consist of a maximum of 5 connections and will at all times keep 2 of these connections open. Connections in the pool will remain open for 30 seconds if inactive, after which they will be closed.

4.3 Configuring a DataSource emulation for XA usage

In order to use a vendor implementation of a Connection driver as if it were a XA DataSource, Orion wraps this Connection with a DataSource emulator of the type DriverManagerDataSource. This DataSource is then extended with the XA ability of the >DriverManagerXADataSource added to a specified location. The DriverManagerXADataSource can then be used to emulate distributed transactions.

Below is an example of such a setup using HypersonicSQL:s JDBC driver:

<data-sources>

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Hypersonic"
location="jdbc/HypersonicCoreDS"
xa-location="jdbc/HypersonicXADS"
connection-driver="org.hsql.jdbcDriver"
username="sa"
password=""
url="jdbc:HypersonicSQL:./database/sampledb"
max-connections="5"
min-connections="2"
inactivity-timeout="30"
schema="database-schemas/hypersonic.xml"
/>
</data-sources>

Listing 8: A sample DataSource entry.

The DataSource configuration entry listed above creates a DriverManagerDataSource bound to the location " jdbc/HypersonicCoreDS" that pulls its connections from the Driver Manager located at " org.hsql.jdbcDriver". By adding a xa-location attribute Orion will bind a DriverManagerXADataSource to the location " jdbc/HypersonicXADS". The DataSource at the xa-location will consist of a maximum of 5 connections and will at all times keep 2 of these connections open. Connections in the pool will remain open for 30 seconds if inactive, after which they will be closed.

4.4 Configuring a DataSource emulation for EJB or CMT usage

In order to use EJB or CMT a OrionCMTDataSource needs to be used to add support for distributed transactions and pooling as necessary.

By specifying a "ejb-location" attribute, Orion will bind a OrionCMTDataSource to the specified location. In order to instantiate the OrionCMTDataSource at the specified location, a xa-location attribute needs to be given with a bound DriverManagerXADataSource.

Below is an example of such a setup using HypersonicSQL:s JDBC driver:

<data-sources>

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Hypersonic"
location="jdbc/HypersonicCoreDS"
pooled-location="jdbc/HypersonicPooledDS"
xa-location="jdbc/HypersonicXADS"
ejb-location="jdbc/HypersonicDS"
connection-driver="org.hsql.jdbcDriver"
username="sa"
password=""
url="jdbc:HypersonicSQL:./database/sampledb"
max-connections="5"
min-connections="2"
inactivity-timeout="30"
schema="database-schemas/hypersonic.xml"
/>
</data-sources>

Listing 9: A sample DataSource entry.

5 Using a DataSource

This section quickly goes through referencing, mapping, retrieving and using DataSources.

5.1 Referencing a DataSource

In order for your module to be able to use a DataSource the DataSource must be defined as a resource in the modules descriptor.

Below is an example resource-reference:

<resource-ref>
<res-ref-name>jdbc/myDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Listing 10: A sample Resource-reference entry.

The resource-reference described above would bind a DataSource to the modules environment context at the specified location ("java:comp/env/jdbc/myDataSource").

The actual DataSource that is mapped to this resource-reference depends on the environment the module is deployed into. The person responsible for deploying the module should edit the Orion specific deployment settings and map the resource-reference to an existing DataSource. If no such mapping is carried out, the resource-reference will be mapped to the current default DataSource.

5.2 Looking up a DataSource

A component inside a module with a resource-reference set up to point at a DataSource might retrieve this DataSource with for example the following lines of code:

...
InitialContext context=new InitialContext();
DataSource ds=(DataSource)context.lookup("java:comp/env/jdbc/myDataSource");
...

Listing 11: Sample DataSource lookup.

The lines of code given above would instantiate the InitialContext and use this to retrieve the DataSource from the specified location (defined in the modules descriptor's resource-reference tag).

5.3 Using a DataSource

The sample code given below shows a common usage of a DataSource:

...
InitialContext context=new InitialContext();
DataSource ds=(DataSource)context.lookup("java:comp/env/jdbc/defaultDS");
Connection connection=null;
try{
connection=ds.getConnection();
ResultSet resultSet = connection.getMetaData().getTables(null,null,"%",null);
//do something with the ResultSet
}catch(Exception e){
// exception occured
}finally{
try {
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
//could not close connection
}
}
...

Listing 12: Sample DataSource usage.

In the example listed above, a DataSource is retrieved by looking up a JNDI location. This DataSource is then used to get hold of a Connection. The Connection is then used to query the database for metadata in the form of the tables it contains. After the usage of the Connection, it is finally closed.

5.4 Mapping a DataSource

Sometimes its necessary to use another DataSource than the default one, especially when you have more than one DataSource you want to use. In order to specify what DataSource the resource-reference should be pointing at, it's necessary to map the resource-reference to a DataSource configuration entry.

As mapping a resource-reference to an actual resource is not covered by the J2EE specification, this procedure is a bit different depending on what Application Server you are using. In this article, we will of course only look at how mapping is performed when using the Orion Application Server.

For every module deployed, no matter if it is an EJB-module, Web-module or Application-client-module, a Orion specific deployment descriptor is written.

The Orion specific deployment descriptor holds a line similar to the one listed below for each and every resource that is described in the original deployment descriptor:

<resource-ref-mapping
name="jdbc/myDataSource"
/>

Listing 13: Sample resource-ref-mapping entry.

This tag has an optional attribute named " location" where the JNDI location of a instantiated DataSource can be found (as defined in a DataSource configuration entry's " location" attribute). If no location is specified, the applications default DataSource will be used. If the Application that this module is part of does not have a default DataSource defined in its Orion specific deployment descriptor the Servers default DataSource will be used.

Notice that the Orion specific deployment descriptors are located in the modules deployment directory, normally in " /[Orion dir]/application-deployments/[application]/[module]". These deployment descriptor has a "orion-" prefix to their names.

Given that a DataSource is configured to be located at the JNDI location "jdbc/defaultDS", the resource described in the sample line above could be mapped to this DataSource by adding a location attribute to the tag so that it now looks like the following:

<resource-ref-mapping
location="jdbc/defaultDS"
name="jdbc/myDataSource"
/>

Listing 14: Sample resource-ref-mapping entry.

To set the default DataSource for an application, the Orion specific deployment descriptor for the application (orion-application.xml) should be edited. Add a default-data-source attribute to the orion-application tag with a value of the JNDI location for the DataSource to be used as default for all modules in this application (except for those explicitly mapped to other DataSources) as in the example below:

<orion-application
...
deployment-version="1.5.2"
default-data-source="jdbc/otherDS"
...
>
...
</orion-application>

Listing 15: Example entry in orion-application.xml to set the default DataSource to use for the Application.

6 Debugging JDBC usage

A good way to find unclosed connections and similar is by running Orion in JDBC debugging mode.

Starting Orion in JDBC debugging mode is achieved with the following start-up command:

java -jar -Djdbc orion.jar

Listing 16: Starting Orion in JDBC debugging mode.

Orion will now output the name of the class that opened the connection that was not closed to System.out.

7 Sample DataSource configurations

This section holds sample DataSource configurations for comon DBMS.

Sybase sample DataSource configuration
MS SQLServer sample DataSource configuration
Mckoi sample DataSource configuration
Mckoi in-process sample DataSource configuration

7.1 Sybase sample DataSource configuration

Listing 17 below shows a sample configuration for Sybase, using jconn2.jar .

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="SybaseDS"
location="jdbc/SybaseDSCore"
pooled-location="jdbc/SybaseDSPooled"
xa-location="jdbc/xa/SybaseDSXA"
ejb-location="jdbc/SybaseDS"
connection-driver="com.sybase.jdbc2.jdbc.SybDriver"
username="user"
password="password"
schema="database-schemas/sybase.xml"
url="jdbc:sybase:Tds:192.168.0.101:11222/sample"
connection-retry-interval="1"
/>

Listing 17: Sybase sample configuration

7.2 MS SQLServer sample DataSource configuration

Listing 18 below shows a sample configuration for Microsoft SQLServer, using mssqlserver.jar (and the helpers msutil.jar and msbase.jar).

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="MSSQLServerDS"
location="jdbc/MSSQLServerDSCore"
pooled-location="jdbc/MSSQLServerDSPooled"
xa-location="jdbc/xa/MSSQLServerDSXA"
ejb-location="jdbc/MSSQLServerDS"
connection-driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"
username="user"
password="password"
schema="database-schemas/ms-sql.xml"
url="jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=sample;selectMethod=cursor"
/>

Listing 18: MS SQLServer sample configuration

7.3 Mckoi sample DataSource configuration

Listing 19 below shows a sample configuration for Mckoi, using mkjdbc.jar.

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="MckoiDS"
location="jdbc/MckoiDSCore"
pooled-location="jdbc/MckoiDSPooled"
xa-location="jdbc/xa/MckoiDSXA"
ejb-location="jdbc/MckoiDS"
connection-driver="com.mckoi.JDBCDriver"
username="user"
password="password"
schema="database-schemas/mckoi.xml"
url="jdbc:mckoi://192.168.0.1/"
/>

Listing 19: Mckoi sample configuration

7.4 Mckoi in-process sample DataSource configuration

Listing 20 below shows a sample configuration for Mckoi in-process, using mckoidb.jar.

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="MckoiDS"
location="jdbc/MckoiDSCore"
pooled-location="jdbc/MckoiDSPooled"
xa-location="jdbc/xa/MckoiDSXA"
ejb-location="jdbc/MckoiDS"
connection-driver="com.mckoi.JDBCDriver"
username="user"
password="password"
schema="database-schemas/mckoi.xml"
url="jdbc:mckoi:local:///usr/local/orion/database/sample/db.conf"
/>

Listing 20: Mckoi i-process sample configuration