DataSource configuration

DataSource configuration

For the original documentation, refer to the connectors chapter in the application server guide.

 

DataSources are defined inside a <datasources> element.

 

• <no-tx-datasource> - a DataSource that does not take part in JTA transactions using a java.sql.Driver

• <local-tx-datasource> - a DataSource that does not support two phase commit using a java.sql.Driver

• <xa-datasource> - a DataSource that does support two phase commit using a javax.sql.XADataSource

 

Parameters specific for java.sql.Driver usage

 

• <connection-url> - the JDBC driver connection url string

• <driver-class> - the JDBC driver class implementing java.sql.Driver

• <connection-property> - used to configure the connections retrieved from the java.sql.Driver, e.g.

<connection-property name="char.encoding">UTF-8</connection-property>

Parameters specific for javax.sql.XADataSource usage

 

• <xa-datasource-class> - the class implementing the XADataSource

• <xa-datasource-property> - properties used to configure the XADataSource e.g.

    <xa-datasource-property name="IfxWAITTIME">10</xa-datasource-property>
    <xa-datasource-property name="IfxIFXHOST">myhost.mydomain.com</xa-datasource-property>
    <xa-datasource-property name="PortNumber">1557</xa-datasource-property>
    <xa-datasource-property name="DatabaseName">mydb</xa-datasource-property>
    <xa-datasource-property name="ServerName">myserver</xa-datasource-property>

• <isSameRM-override-value> - set to false to fix problems with Oracle

• <track-connection-by-tx/> - set to fix problems with Oracle (not necessarily in JBoss-5.x where it is enabled by default and the element is deprecated)

• <no-tx-separate-pools/> - Pool Transactional and non-Transactional connections separately.  Using this option will cause your total pool size to be twice max-pool-size because two actual pools will be created.  Used to fix problems with Oracle.

 

Common DataSource parameters

 

• <jndi-name> - the JNDI name under which the DataSource should be bound.

• <use-java-context> - A boolean indicating if the jndi-name should be prefixed with java: which causes the DataSource to only be accessible from within the jboss server vm. The default is true.

• <user-name> - the user name used when creating the connection (not used when security is configured)

• <password> - the password used when creating the connection (not used when security is configured)

• <transaction-isolation> - the default transaction isolation of the connection (unspecified means use the default provided by the database):

  • TRANSACTION_READ_UNCOMMITTED

  • TRANSACTION_READ_COMMITTED

  • TRANSACTION_REPEATABLE_READ

  • TRANSACTION_SERIALIZABLE

  • TRANSACTION_NONE

 

• <new-connection-sql> - an sql statement that is executed against each new connection. This can be used to set the connection schema, etc.

• <check-valid-connection-sql> - an sql statement that is executed before it is checked out from the pool (see <validate-on-match>) to make sure it is still valid. If the sql fails, the connection is closed and new ones created. Also it will be used by <background-validation>

• <valid-connection-checker-class-name> - a class that can check whether a connection is valid using a vendor specific mechanism

• <exception-sorter-class-name> - a class that looks at vendor specific messages to determine whether sql errors are fatal and thus the connection should be destroyed.  If none specified, no errors will be treated as fatal.

• <track-statements> - (a) whether to monitor for unclosed Statements and ResultSets and issue warnings when the user forgets to close them (default nowarn)

• <prepared-statement-cache-size> - the number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.

• <share-prepared-statements> - (b) with prepared statement cache enabled whether two requests in the same transaction should return the same statement (from jboss-4.0.2 - default false).

• <set-tx-query-timeout> - Whether to enable query timeout based on the length of time remaining until the transaction times out.  This was not ported to 4.0.x until 4.0.3.  Also, it exists in 4.2.x but doesn't function with the JBossTS transaction manager.  JBossTS incorporated this functionality in 5.0.  Default is false.

• <query-timeout> - A static configuration of the maximum of seconds before a query times out (since 4.0.3).  This value will be overridden appropriately if <set-tx-query-timeout> is true.  The default is "0" (i.e. infinite).

• <metadata/typemapping> - a pointer to the type mapping in conf/standardjbosscmp.xml (from JBoss4)

• <validate-on-match> - whether to validate the connection when the JCA layer matches a managed connection (i.e. when the connection is checked out of the pool). With the addition of <background-validation> this is not necessarily required.  Note: Specifying "true" for <validate-on-match> is typically not done in conjunction with specifying "true" for <background-validation> as this would be overkill in most scenarios.  Default is true.

• <prefill> - whether to attempt to prefill the connection pool to the minimum number of connections. NOTE: only supporting pools (OnePool) support this feature. A warning can be found in the logs if the pool does not support this.  Default is false.

• <background-validation> - In JBoss 4.0.5 background connection validation was added to help reduce the overall load on the RDBMS system when validating a connection. When using this feature, JBoss will attempt to validate the current connections in the pool is a seperate thread (ConnectionValidator). This must be set to true for <background-validation-minutes> to take effect.  Default is false.

• <idle-timeout-minutes> - indicates the maximum time a connection may be idle before being closed. Setting to 0 disables it.  Default is 15 minutes.

• <background-validation-minutes> - The interval, in minutes, that the ConnectionValidator will run. Default is 10 minutes. NOTE: It is prudent to set this value to something greater or less than the <idle-timeout-minutes>

• <background-validation-millis> - This replaces <background-validation-minutes> since JBoss 5.0, <background-validation-minutes> is no longer supported. Also <background-validation>true</background-validation> is redundant, any background-validation-millis > 0 will enable background validation. See https://jira.jboss.org/browse/JBAS-4088.

• <url-delimiter> - From JBoss5 database failover is part of the main datasource config

• <url-property> - From JBoss5 database failover is part of the main datasource config

• <url-selector-strategy-class-name> - From JBoss5 ONLY database failover is part of the main datasource config
• <stale-connection-checker-class-name> - An implementation of org.jboss.resource.adapter.jdbc.StateConnectionChecker that will decide whether SQLExceptions that notify of bad connections throw org.jboss.resource.adapter.jdbc.StateConnectionException (from JBoss5)
• <max-pool-size> - the maximum number of connections allowed in the pool.  Default is 20.
• <min-pool-size> - the minimum number of connections maintained in the pool.  Unless <prefill> is true then the pool will remain empty until first use at which point the pool will be filled to the <min-pool-size>.  When the pool size drops below the <min-pool-size> due to idle timeouts the pool will be refilled to the <min-pool-size>.  Default is 0.
• <blocking-timeout-millis> - the length of time to wait for a connection to become available when all the connections are checked out.  Default is 30000 (30 seconds).
• <use-fast-fail> - whether or not to continue to attempt to acquire a connection from the pool even if the nth attempt has failed.  This is to address performance issues where validation SQL takes significant time and resources to execute.  Default is false.

 

 

 

(a) From 3.2.6 track-statements has a new option

<track-statements>nowarn</track-statements

which closes Statements and ResultSets without a warning. It is also the new default value.

 

 

(b) The purpose is to workaround questionable driver behavior where the driver applies auto-commit semantics to local transactions.

   Connection c = dataSource.getConnection(); // auto-commit == false
   PreparedStatement ps1 = c.prepareStatement(...);
   ResultSet rs1 = ps1.executeQuery();
   PreparedStatement ps2 = c.prepareStatement(...);
   ResultSet rs2 = ps2.executeQuery();

Assuming the prepared statements are the same. For some drivers, ps2.executeQuery() will automatically close rs1 so we actually need two real prepared statements behind the scenes. This should only be for the auto-commit semantic where re-running the query starts a new transaction automatically. For drivers that follow the spec, you can set it to true to share the same real prepared statement.

 

 

Set up specific DataSources

• CISR9S

• DB2

• Derby

• Facets

• FastObjects

• Firebird

• HSQLDB

• Informix

• JDatastore

• Mimer

• MS-SQL

• MySQL

• ODBC

• Oracle

• Pointbase

• PostgeSQL

• Progress

• SAP DB

• SAP R3

• Solid

• Sybase

 

Generic Datasource Sample

<datasources>
  <local-tx-datasource>
    <jndi-name>GenericDS</jndi-name>
    <connection-url>[jdbc: url for use with Driver class]</connection-url>
    <driver-class>[fully qualified class name of java.sql.Driver implementation]</driver-class>
    <user-name>x</user-name>
    <password>y</password>
    <!-- you can include connection properties that will get passed in 
     the DriverManager.getConnection(props) call-->
    <!-- look at your Driver docs to see what these might be -->
    <connection-property name="char.encoding">UTF-8</connection-property>
    <transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>

    <!--pooling parameters-->
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <blocking-timeout-millis>5000</blocking-timeout-millis>
    <idle-timeout-minutes>15</idle-timeout-minutes>
    <!-- sql to call when connection is created
    <new-connection-sql>some arbitrary sql</new-connection-sql>
    -->

    <!-- sql to call on an existing pooled connection when it is obtained from pool 
    <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
    -->

    <set-tx-query-timeout></set-tx-query-timeout>
    <query-timeout>300</query-timeout> <!-- maximum of 5 minutes for queries -->

    <!-- pooling criteria.  USE AT MOST ONE-->
    <!--  If you don't use JAAS login modules or explicit login 
     getConnection(usr,pw) but rely on user/pw specified above, 
     don't specify anything here -->

    <!-- If you supply the usr/pw from a JAAS login module -->
    <security-domain>MyRealm</security-domain>

    <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
    <application-managed-security></application-managed-security>

    <!--Anonymous depends elements are copied verbatim into the ConnectionManager mbean config-->
    <depends>myapp.service:service=DoSomethingService</depends>

  </local-tx-datasource>

  <!-- you can include regular mbean configurations like this one -->
  <mbean code="org.jboss.tm.XidFactory" 
      name="jboss:service=XidFactory">
    <attribute name="Pad">true</attribute>
  </mbean>


  <!-- Here's an xa example -->
  <xa-datasource>
    <jndi-name>GenericXADS</jndi-name>
    <xa-datasource-class>[fully qualified name of class implementing javax.sql.XADataSource goes here]</xa-datasource-class>
    <xa-datasource-property name="SomeProperty">SomePropertyValue</xa-datasource-property>
    <xa-datasource-property name="SomeOtherProperty">SomeOtherValue</xa-datasource-property>

    <user-name>x</user-name>
    <password>y</password>
    <transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>



    <!--pooling parameters-->
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <blocking-timeout-millis>5000</blocking-timeout-millis>
    <idle-timeout-minutes>15</idle-timeout-minutes>
        <!-- sql to call when connection is created
        <new-connection-sql>some arbitrary sql</new-connection-sql>
        -->

        <!-- sql to call on an existing pooled connection when it is obtained from pool 
        <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
        -->


    <!-- pooling criteria.  USE AT MOST ONE-->
    <!--  If you don't use JAAS login modules or explicit login 
     getConnection(usr,pw) but rely on user/pw specified above, 
     don't specify anything here -->

    <!-- If you supply the usr/pw from a JAAS login module -->
    <security-domain></security-domain>

    <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
    <application-managed-security></application-managed-security>

  </xa-datasource>

</datasources>

Configuring a DataSource for remote usage

As of jboss-4.0.0 there is support for accessing a DataSource from a remote client. The one change that is necessary for the client to be able to lookup the DataSource from JNDI is to specify use-java-context=false as shown here:

 

<datasources>
  <local-tx-datasource>
    <jndi-name>GenericDS</jndi-name>
    <use-java-context>false</use-java-context>
    <connection-url>...</connection-url>
...

This results in the DataSource being bound under the JNDI name "GenericDS" instead of the default of "java:/GenericDS" which restricts the lookup to the same VM as the jboss server.

 

Note: JBoss does not recommend using this feature on a production environment. It requires accessing a connection pool remotely and this is an anti-pattern as connections are not serializable. Besides, transaction propagation is not supported and it could lead to connection leaks if the remote clients are unreliable (i.e crashes, network failure). If you do need to access a datasource remotely, JBoss recommends accessing it via a remote session bean facade.

 

Configuring a DataSource to use login modules

Add the security-domain parameter to the -ds.xml file.

<datasources>
   <local-tx-datasource>
      ...
      <security-domain>MyDomain</security-domain>
      ...
   </local-tx-datasource>
</datasources>

Add an application-policy to the login-config.xml file.

 

The authentication section should include the configuration for your login-module. For example, if you want to encrypt the database password, use the SecureIdentityLoginModule login module.

<application-policy name="MyDomain">
   <authentication>
      <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">
         <module-option name="username">scott</module-option>
         <module-option name="password">-170dd0fbd8c13748</module-option>
         <module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=OracleDSJAAS</module-option>
      </login-module>
   </authentication>
</application-policy>

In case you plan to fetch the data source connection from a web application, make sure authentication is turned on for the web application. This is in order for the Subject to be populated.

 

If you wish for users to be able to connect anonymously, an additional login module needs to be added to the application-policy, in order to populate the security credentials.

 

Add the UsersRolesLoginModule as the first login module in the chain. The usersProperties and rolesProperties parameters can be directed to dummy files.

 

<login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule" flag="required">
   <module-option name="unauthenticatedIdentity">nobody</module-option>
   <module-option name="usersProperties">props/users.properties</module-option>
   <module-option name="rolesProperties">props/roles.properties</module-option>
</login-module>         

Please look at the following templates for 4.2.0, that gives all the fields and their defaults.

 

<no-tx-datasource>

<local-tx-datasource>

<xa-datasource>