How do I configure Datasource for J2EE Applications?

How do I configure Datasource for J2EE Applications?

A datasource provides pooled connections to applications. The physical connections are obtained from a JDBC Connection Factory and pooled by the datasource. It also provides other configurable features such as connection validation, pool sizes, connection time out, and pool refresh interval.

A datasource on Server is always associated with a JDBC Connection Factory. Multiple datasources can be associated with the same JDBC Connection Factory.

The JDBC API supports two extensions of the datasource interface. These extensions are:

• ConnectionPoolDataSource: This supports caching and reuse of physical connections improving application performance and scalability
• XAdatasource: This provides connections that can participate in distributed transactions

The X/Open CAE specification (XA) facilitates two-phase commit in transactions and forms a part of the JDBC 2.0 Standard Extension.

Note: In this guide, datasource has been used to define both datasource and XA datasource.

How do I manually create JDBC Resources?

You can configure a datasource manually by using the resource-config.xml. To add a JDBC connection, add a datasource tag under the datasources tag. The resource-config.xml appears as below:

<resource-service>
  <resources>
    <default-resources> </default-resources>
    <datasources>
      <datasource connection-factory="testcf" description="No Description" name="demo" transaction-participation="true">
        <pool-properties cache-size="20" connection-request-timeout-seconds="" idle-timeout-seconds="" initial-pool-size="1" max-pool-size="20" min-pool-size="5" refresh-interval-seconds=""/>
        <connection-initialization db-retrials="0" db-retry-interval-millis="0"/>
        <connection-validation class="" on-acquire="false" on-release="false" sql=""/>
        <connection-tracking enable="false"/>
      </datasource>
    </datasources>
    <connection-factories> <connection-factories>
    <mail-resources> <mail-resources>
    <url-resources> <url-resources>
    <jms-adapters> </jms-adapters>
  <resources>
</resource-service>

The fields name and connection-factory are mandatory for the datasource tag.

How do I use Resource Shell to create Datasources?

To add a datasource using the Resource Shell, on the resourceadmin:> prompt, enter:resourceadmin:> add_datasource

There are two ways to add a datasource using add_datasource.

Interactive

To use the interactive mode, enter add_datasource, and press Enter. This asks for all the required fields one by one. No special flag is required for this mode.

Enter the following values needed for the command resourceadmin:> add_datasource

resourceadmin>  - jndi name * #my_datasource
resourceadmin>  - connection factory name * #my_jdbc_cf
resourceadmin>  - initial pool size [2] #1
resourceadmin>  - max pool size [20] #20
resourceadmin>  - min pool size [10] #10
resourceadmin>  - prepared statement cache size [30] #
resourceadmin>  - transaction participation [true] #
resourceadmin>  - transaction isolation level #
resourceadmin>  - validation sql # (the SQL statement which will be used
by the Server to validate connections in the pool)
resourceadmin>  - validator class # (the name of the validator class
which will be used by the Server to validate connections in the pool)
Creating Data Source my_datasource
Data Source [my_datasource] started
Created Data Source my_datasource
Added DataSource [my_datasource]

Note: Fields followed by * are mandatory. Press Enter for the default values. Typing 'q' and pressing Enter at any point cancels the current command.

Non-interactive

To use the non-interactive mode, enter add_datasource. All the required attributes - the JNDI name and the JDBC Connection Factory Name in this case - have to be specified. All optional attributes have default values that can be modified.

There are three ways to specify the fields:

• add_datasource jndiName=my _datasource connectionFactoryName=my_jdbc_cf
• add_datasource my_datasource my_jdbc_cf
• add_datasource -jndiName my_datasource -connectionFactoryName my_jdbc_cf

How do I use JDBC Resource MBean to create Datasources?

Obtain the Resource Service MBean and call the JDBCResourceMBean by entering JDBCResourceMBean jdbcResource= resourceServiceMBean.getJDBCResourceMBean();

Call the createDataSource method on this MBean. Depending upon the requirement, you can call one the described methods:

Method 1
* @param jndiName: JNDI name of the data source.
*
* @ param connectionFactoryName: JNDI name of the connection factory for the datasource.
*
* @ param initialPoolSize: Initial number of database connections to be
* opened at the starting of the datasource.
*
* @param minPoolSize: Minimum number of database connections opened simultaneously
* at any point of time.
*
* @param maxPoolSize: Maximum number of database connections opened simultaneously
* at any point of time.
*
* @throws ResourceException: If some problem occurs in datasource creation.
* e.g. JNDI name may already be in use.
*/
public void createDataSource(String jndiName, String connectionFactoryName,
int minPoolSize, int maxPoolSize)throws ResourceException;

Method 2
* @param jndiName: JNDI name of the data source.
*
* @ param connectionFactoryName: JNDI name of the connection factory for the datasource
*
* @param minPoolSize: Minimum number of database connections opened simultaneously
* at any point of time.
*
* @param maxPoolSize: Maximum number of database connections opened simultaneously
* at any point of time.
*
* @param preparedStatementCacheSize: size of the preparedStatementCache
*
* @param participateInTransaction: indicates whether connection from datasource
* should participate in transaction
*
* @param transactionIsolation: the isolation level that has to be set on all the
* connections from the datasource
*
* @param validationSQL: an SQL that would be used to validate a connection
*
* @param validatorClass: a validation class that would be used to validate the
* connections
*
* @throws ResourceException: If some problem occurs in datasource creation.
* e.g. JNDI name may already be in use.
*/
public void createDataSource(String jndiName, String connectionFactoryName,
int minPoolSize,int maxPoolSize, Integer preparedStatementCacheSize,
Boolean participateInTransaction, String transactionIsolation,
String validationSQL, String validatorClass) throws ResourceException;

How do I configure Datasources using the Console?

To configure a datasource on the Console, select Configure > Resources. On the screen, you can see the following details related to the existing datasources:

Table 1: Details for an existing datasource

Adding Datasources

Click Add to add a datasource.

You can choose to create a datasource on an existing JDBC Connection Factory, or create a new Connection Factory.

• Creating a datasource on an existing Connection Factory: To create a datasource on an existing Connection Factory, select the existing Connection Factory from the drop-down list.
• Creating a datasource on a new Connection Factory: To create a new JDBC Connection Factory and use it for the datasource, select create new. Provide the following information:

Table 2: Details needed for creating a datasource on a new Connection Factory

Table 3: Drivers supported by the Pramati Server

Click Next>> to add a datasource and set its properties.

Table 4: Step 2 of adding a datasource

Note: *Only one of Validation SQL or Validator Class will be used. If none are specified, the Server will use the internal connection validator. Click Save to add the new datasource.

Editing Datasources

The Console manages all necessary validations.

Performance Tuning Tips

In this section we will discuss a few performance tuning tips that can be used to enhance the performance of the Server. These values can be provided at the time of creating the resource and can be modified at runtime using the Console.

Max Pool Size

This represents the maximum number of connection objects that are assigned to a pool. Maintaining a pool of datasource objects avoids the overhead of creating an object every time a request for a connection object is made. The default value for the connection pool size is 20.

This value should be set to the maximum number of connections that can be used at any given time. Setting a value lower than this would result in connections waiting for release of other connections that are in use at peak time.

The resource service ensures that the maximum pool size is not exceeded. If the limit is reached, and a new connection request is made when other connections are busy, the connection pool waits till a connection is free.

Min Pool Size

This value represents the minimum number of connections to be retained in the connection pool at any given time. If the number of requests exceed this value, a new connection object is taken from the pool and served. The default value is 5. For optimum performance, set this value to the average number of connections used at any given time.

Initial Pool Size

This value represents the number of connections the connection pool will have when the datasource is created for the first time. The default value is 1.

The primary purpose of this parameter is to allow faster response times for connection requests when the application uses the datasource pool for the first time.

Idle Timeout

The time interval in seconds after which a connection in the pool is closed if no request arrives. The default value is 300 seconds.

Tuning this value depends on a number of parameters. For example, if connection requests arrive at uneven intervals, this can result in pre-mature close of connections and would require connections to be created on subsequent requests, thus consuming processing time.

On the other hand, if this value is set to too high a value, connections would be idle in the pool, consuming memory. Ideally this value should be set to the average intervals at which connections are requested.

Connection Request Timeout

The time interval in seconds for which an application can wait after making a connection request. After this interval, an exception is thrown to the application.

The default value is 10 seconds.

This parameter should be configured carefully - setting too low a value might result in application connection requests getting timed out prematurely, while setting too high a value might make a connection request wait for longer than is necessary.

Cached Prepared Statements

This option represents the number of prepared statements that should be cached. The caching is done for each connection, that is, each connection has it's own prepared statement cache. Specify here the number of prepared statements to be cached. The value for cache is application dependent. For optimal performance, this value should be equal to the number of prepared statements in the application.

The default value is 50.

Deleting Datasources

Select the check box provided for the Datasource to be deleted on the Resources page, and click Delete. This deletes the selected datasource.

Related Topics:

• JDBC Connection Factory to obtain raw connections to a database
• Datasource cluster to provide auto failover from primary to secondary source
• Mail Resource to send mails from application using the mail session object
• JMS adapter to enable Java programs to create, send, receive, and read messages of a messaging system.
• Connectors to define a set of system-level contracts between the Server and resource adapter.
• URL resource to obtain URL connections.

© Pramati Technologies 2007	Runs on Pramati Server | Feedback | Legal