Este conteúdo não está disponível no idioma selecionado.

Chapter 13. Datasource Management

13.1. About JBoss EAP Datasources

About JDBC

The JDBC API is the standard that defines how databases are accessed by Java applications. An application configures a datasource that references a JDBC driver. Application code can then be written against the driver, rather than the database. The driver converts the code to the database language. This means that if the correct driver is installed, an application can be used with any supported database.

For more information, see the JDBC 4.0 specification.

Supported Databases

See JBoss EAP supported configurations for the list of JDBC-compliant databases supported by JBoss EAP 7.

Datasource Types

The two general types of resources are referred to as datasources and XA datasources.

Non-XA datasources: Used for applications that do not use transactions, or applications that use transactions with a single database.
XA datasources: Used by applications that use multiple databases or other XA resources as part of one XA transaction. XA datasources introduce additional overhead.

You specify which type of datasource to use when creating the datasource using the JBoss EAP management interfaces.

The ExampleDS datasource

JBoss EAP ships with an example datasource configuration (ExampleDS), which is provided to demonstrate how datasources are defined. This datasource uses an H2 database, which is a lightweight, relational database management system that provides developers with the ability to quickly build applications.

Warning

The ExampleDS datasource and the H2 database should not be used in a production environment. This is a very small, self-contained datasource that supports all of the standards needed for testing and building applications, but is not robust or scalable enough for production use.

13.2. JDBC Drivers

Before defining datasources in JBoss EAP for your applications to use, you must first install the appropriate JDBC driver.

13.2.1. Install a JDBC Driver as a Core Module

JDBC drivers can be installed as a core module using the management CLI using the following steps.

Download the JDBC driver.
Download the appropriate JDBC driver from your database vendor. See JDBC Driver Download Locations for standard download locations for JDBC drivers of common databases.
Make sure to extract the archive if the JDBC driver JAR file is contained within a ZIP or TAR archive.
Start the JBoss EAP server.
Launch the management CLI, but do not use the --connect or -c argument to connect to the running instance.
```
$ EAP_HOME/bin/jboss-cli.sh
```
Use the module add management CLI command to add the new core module.
```
module add --name=MODULE_NAME --resources=PATH_TO_JDBC_JAR --dependencies=DEPENDENCIES
```
For example, the following command adds a MySQL JDBC driver module.
```
module add --name=com.mysql --resources=/path/to/mysql-connector-java-5.1.36-bin.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.
Execute module --help for more details on using this command to add and remove modules.
Use the connect management CLI command to connect to the running instance.
```
connect
```
Register the JDBC driver. When running in a managed domain, be sure to precede this command with /profile=PROFILE_NAME.
```
/subsystem=datasources/jdbc-driver=DRIVER_NAME:add(driver-name=DRIVER_NAME,driver-module-name=MODULE_NAME,driver-xa-datasource-class-name=XA_DATASOURCE_CLASS_NAME, driver-class-name=DRIVER_CLASS_NAME)
```
Note
The driver-class-name parameter is only required if the JDBC driver jar defines more than one class in its /META-INF/services/java.sql.Driver file.
For example, the /META-INF/services/java.sql.Driver file in the MySQL 5.1.36 JDBC driver JAR defines two classes:
- com.mysql.jdbc.Driver
- com.mysql.fabric.jdbc.FabricMySQLDriver
For this case, you would pass in driver-class-name=com.mysql.jdbc.Driver.
For example, the following command registers a MySQL JDBC driver.
```
/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource, driver-class-name=com.mysql.jdbc.Driver)
```

The JDBC driver is now available to be referenced by application datasources.

13.2.2. Install a JDBC Driver as a JAR Deployment

JDBC drivers can be installed as a JAR deployment using either the management CLI or the management console. As long as the driver is JDBC 4-compliant, it will automatically be recognized and installed as a JDBC driver upon deployment.

The following steps describe how to install a JDBC driver using the management CLI.

Note

The recommended installation method for JDBC drivers is to install them as a core module.

Download the JDBC driver.
Download the appropriate JDBC driver from your database vendor. See JDBC Driver Download Locations for standard download locations for JDBC drivers of common databases.
Make sure to extract the archive if the JDBC driver JAR file is contained within a ZIP or TAR archive.
If the JDBC driver is not JDBC 4-compliant, see the steps to Update a JDBC Driver JAR to be JDBC 4-Compliant.
Deploy the JAR to JBoss EAP.
```
deploy PATH_TO_JDBC_JAR
```
Note
In a managed domain, specify the appropriate server groups.
For example, the following command deploys a MySQL JDBC driver.
```
deploy /path/to/mysql-connector-java-5.1.36-bin.jar
```
A message will be displayed in the JBoss EAP server log that displays the deployed driver name, which will be used when defining datasources.
```
WFLYJCA0018: Started Driver service with driver-name = mysql-connector-java-5.1.36-bin.jar_com.mysql.jdbc.Driver_5_1
```

The JDBC driver is now available to be referenced by application datasources.

Update a JDBC Driver JAR to be JDBC 4-Compliant

If the JDBC driver JAR is not JDBC 4-compliant, it can be made deployable using the following steps.

Create an empty temporary directory.
Create a META-INF subdirectory.
Create a META-INF/services subdirectory.
Create a META-INF/services/java.sql.Driver file and add one line to indicate the fully-qualified class name of the JDBC driver.
For example, the below line would be added for a MySQL JDBC driver.
```
com.mysql.jdbc.Driver
```
Use the JAR command-line tool to add this new file to the JAR.
```
jar \-uf jdbc-driver.jar META-INF/services/java.sql.Driver
```

13.2.3. JDBC Driver Download Locations

The following table gives the standard download locations for JDBC drivers of common databases used with JBoss EAP.

Note

These links point to third-party websites which are not controlled or actively monitored by Red Hat. For the most up-to-date drivers for your database, check your database vendor’s documentation and website.

Table 13.1. JDBC Driver Download Locations
Vendor	Download Location
MySQL	http://www.mysql.com/products/connector/
PostgreSQL	http://jdbc.postgresql.org/
Oracle	http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
IBM	http://www-306.ibm.com/software/data/db2/java/
Sybase	The jConnect JDBC driver is part of the SDK for SAP ASE installation. Currently there is not a separate download site for this driver by itself.
Microsoft	http://msdn.microsoft.com/data/jdbc/

13.2.4. Access Vendor-Specific Classes

In some cases, it is necessary for an application to use vendor-specific functionality that is not part of the JDBC API. In these cases, you can access vendor-specific APIs by declaring a dependency in that application.

Warning

This is advanced usage. Only applications that need functionality not found in the JDBC API should implement this procedure.

Important

This process is required when using the reauthentication mechanism, and accessing vendor-specific classes.

You can define a dependency for an application using either the MANIFEST.MF file or a jboss-deployment-structure.xml file.

If you have not yet done so, Install a JDBC Driver as a Core Module.

Using the `MANIFEST.MF` File

Edit the application’s META-INF/MANIFEST.MF file.
Add the Dependencies line and specify the module name.
For example, the below line declares the com.mysql module as a dependency.
```
Dependencies: com.mysql
```

Using a `jboss-deployment-structure.xml` File

Create a file called jboss-deployment-structure.xml in the META-INF/ or WEB-INF/ folder of the application.

Specify the module using the dependencies element.

For example, the following example jboss-deployment-structure.xml file declares the com.mysql module as a dependency.

<jboss-deployment-structure>
  <deployment>
    <dependencies>
      <module name="com.mysql"/>
    </dependencies>
  </deployment>
</jboss-deployment-structure>

The example code below accesses the MySQL API.

import java.sql.Connection;
import org.jboss.jca.adapters.jdbc.WrappedConnection;

...

Connection c = ds.getConnection();
WrappedConnection wc = (WrappedConnection)c;
com.mysql.jdbc.Connection mc = wc.getUnderlyingConnection();

Important

Follow the vendor-specific API guidelines closely, as the connection is being controlled by the IronJacamar container.

13.3. Creating Datasources

Datasources can be created using the management console or the management CLI.

JBoss EAP 7 allows you to use expressions in datasource attribute values, such as the enabled attribute. See the Property Replacement section for details on using expressions in the configuration.

13.3.1. Create a Non-XA Datasource

Non-XA datasources can be defined using the data-source add management CLI command. You can also define non-XA datasources using the management console by navigating to Configuration Subsystems Datasources Non-XA and clicking Add to open the Create Datasource wizard.

The below steps describe how to define a non-XA datasource using the management CLI.

If you have not yet done so, install and register the appropriate JDBC Driver as a Core Module.
Define the datasource using the data-source add command, specifying the appropriate argument values.
```
data-source add --name=DATASOURCE_NAME --jndi-name=JNDI_NAME --driver-name=DRIVER_NAME  --connection-url=CONNECTION_URL
```
Note
In a managed domain, you must specify the --profile=PROFILE_NAME argument.
See the Datasource Parameters section below for tips on these parameter values.

For detailed examples, see the Example Datasource Configurations for the supported databases.

Datasource Parameters

jndi-name

The JNDI name for the datasource must start with java:/ or java:jboss/. For example, java:jboss/datasources/ExampleDS.

driver-name

The driver name value depends on whether the JDBC driver was installed as a core module or a JAR deployment.

For a core module, the driver name value will be the name given to the JDBC driver when it was registered.
For a JAR deployment, the driver name is the name of the JAR if there is only one class listed in its /META-INF/services/java.sql.Driver file. If there are multiple classes listed, then the value is JAR_NAME + "_" + DRIVER_CLASS_NAME + "_" + MAJOR_VERSION + "_" + MINOR_VERSION (for example mysql-connector-java-5.1.36-bin.jar_com.mysql.jdbc.Driver_5_1).
You can also see the driver name listed in the JBoss EAP server log when the JDBC JAR is deployed.
```
WFLYJCA0018: Started Driver service with driver-name = mysql-connector-java-5.1.36-bin.jar_com.mysql.jdbc.Driver_5_1
```

connection-url

For details on the connection URL formats for the supported databases, see the list of Datasource Connection URLs.

For a complete listing of all available datasource parameters, see the Datasource Parameters section.

13.3.2. Create an XA Datasource

XA datasources can be defined using the xa-data-source add management CLI command. You can also define XA datasources using the management console by navigating to Configuration Subsystems Datasources XA and clicking Add to open the Create XA Datasource wizard.

The below steps describe how to define an XA datasource using the management CLI.

Note

In a managed domain, you will need to specify the profile to use. Depending on the format of the management CLI command, you will either precede the command with /profile=PROFILE_NAME or pass in the --profile=PROFILE_NAME argument.

If you have not yet done so, install and register the appropriate JDBC Driver as a Core Module.

Define the datasource using the xa-data-source add command, specifying the appropriate argument values.

xa-data-source add --name=XA_DATASOURCE_NAME --jndi-name=JNDI_NAME --driver-name=DRIVER_NAME --xa-datasource-class=XA_DATASOURCE_CLASS --xa-datasource-properties={"ServerName"=>"HOSTNAME","DatabaseName"=>"DATABASE_NAME"}

See the Datasource Parameters section below for tips on these parameter values.

Set XA datasource properties.
At least one XA datasource property is required when defining an XA datasource or you will receive an error when adding the datasource in the previous step. Any properties that were not set when defining the XA datasource can be set individually afterward.
1. Set the server name.
```
/subsystem=datasources/xa-data-source=XA_DATASOURCE_NAME/xa-datasource-properties=ServerName:add(value=HOSTNAME)
```
2. Set the database name.
```
/subsystem=datasources/xa-data-source=XA_DATASOURCE_NAME/xa-datasource-properties=DatabaseName:add(value=DATABASE_NAME)
```

For detailed examples, see the Example Datasource Configurations for the supported databases.

Datasource Parameters

jndi-name

The JNDI name for the datasource must start with java:/ or java:jboss/. For example, java:jboss/datasources/ExampleDS.

driver-name

The driver name value depends on whether the JDBC driver was installed as a core module or a JAR deployment.

For a core module, the driver name value will be the name given to the JDBC driver when it was registered.
For a JAR deployment, the driver name is the name of the JAR if there is only one class listed in its /META-INF/services/java.sql.Driver file. If there are multiple classes listed, then the value is JAR_NAME + "_" + DRIVER_CLASS_NAME + "_" + MAJOR_VERSION + "_" + MINOR_VERSION (for example mysql-connector-java-5.1.36-bin.jar_com.mysql.jdbc.Driver_5_1).
You can also see the driver name listed in the JBoss EAP server log when the JDBC JAR is deployed.
```
WFLYJCA0018: Started Driver service with driver-name = mysql-connector-java-5.1.36-bin.jar_com.mysql.jdbc.Driver_5_1
```

xa-datasource-class

Specify the XA datasource class for the JDBC driver’s implementation of the javax.sql.XADataSource class.

xa-datasource-properties

At least one XA datasource property is required when defining an XA datasource or you will receive an error when attempting to add it. Additional properties can also be added to the XA datasource after it has been defined.

For a complete listing of all available datasource parameters, see the Datasource Parameters section.

13.4. Modifying Datasources

Datasources settings can be configured using the management console or the management CLI.

JBoss EAP 7 allows you to use expressions in datasource attribute values, such as the enabled attribute. See the Property Replacement section for details on using expressions in the configuration.

13.4.1. Modify a Non-XA Datasource

Non-XA datasource settings can be updated using the data-source management CLI command. You can also update datasource attributes from the datasources subsystem in the management console.

Note

Non-XA datasources can be integrated with JTA transactions. To integrate the datasource with JTA, ensure that the jta parameter is set to true.

Settings for a datasource can be updated by using the following management CLI command.

data-source --name=DATASOURCE_NAME --ATTRIBUTE_NAME=ATTRIBUTE_VALUE

Note

In a managed domain, you must specify the --profile=PROFILE_NAME argument.

A server reload may be required for the changes to take effect.

13.4.2. Modify an XA Datasource

XA datasource settings can be updated using the xa-data-source management CLI command. You can also update datasource attributes from the datasources subsystem in the management console.

Settings for an XA datasource can be updated by using the following management CLI command.
```
xa-data-source --name=XA_DATASOURCE_NAME --ATTRIBUTE_NAME=ATTRIBUTE_VALUE
```
Note
In a managed domain, you must specify the --profile=PROFILE_NAME argument.
An XA datasource property can be added using the following management CLI command.
```
/subsystem=datasources/xa-data-source=XA_DATASOURCE_NAME/xa-datasource-properties=PROPERTY:add(value=VALUE)
```
Note
In a managed domain, you must precede this command with /profile=PROFILE_NAME.

A server reload may be required for the changes to take effect.

13.5. Removing Datasources

Datasources can be removed using the management console or the management CLI.

13.5.1. Remove a Non-XA Datasource

Non-XA datasources can be removed using the data-source remove management CLI command. You can also remove datasources from the datasources subsystem in the management console.

data-source remove --name=DATASOURCE_NAME

Note

In a managed domain, you must specify the --profile=PROFILE_NAME argument.

The server will require a reload after the datasource is removed.

13.5.2. Remove an XA Datasource

XA datasources can be removed using the xa-data-source remove management CLI command. You can also remove XA datasources from the datasources subsystem in the management console.

xa-data-source remove --name=XA_DATASOURCE_NAME

Note

In a managed domain, you must specify the --profile=PROFILE_NAME argument.

The server will require a reload after the XA datasource is removed.

13.6. Testing Datasource Connections

Once a datasource has been added to JBoss EAP, you can test the connection to verify that the settings are correct. Datasource connections can be tested either using a management CLI command or from the management console with the Test Connection button in the datasources subsystem.

The following management CLI command can be used to test a datasource’s connection.

/subsystem=datasources/data-source=DATASOURCE_NAME:test-connection-in-pool

Note

In a managed domain, you must precede this command with /host=HOSTNAME/server=SERVER_NAME.

13.7. XA Datasource Recovery

An XA datasource is a datasource that can participate in an XA global transaction, which is coordinated by the transaction manager and can span multiple resources in a single transaction. If one of the participants fails to commit its changes, the other participants abort the transaction and restore their state as it was before the transaction occurred. This is to maintain consistency and avoid potential data loss or corruption.

XA recovery is the process of ensuring that all resources affected by a transaction are updated or rolled back, even if any of the resources or transaction participants crash or become unavailable. XA recovery happens without user intervention.

Each XA resource needs to have a recovery module associated with its configuration. The recovery module is the code that is executed when recovery is being performed. JBoss EAP automatically registers recovery modules for JDBC XA resources. You can register a custom module with your XA datasource if you wish to implement custom recovery code. The recovery module must extend class com.arjuna.ats.jta.recovery.XAResourceRecovery.

13.7.1. Configuring XA Recovery

For most JDBC resources, the recovery module is automatically associated with the resource. In these cases, you only need to configure the options that allow the recovery module to connect to your resource to perform recovery.

The following table describes the XA datasource parameters specific to XA recovery. Each of these configuration attributes can be set during datasource creation or afterward. You can set them using either the management console or the management CLI. See Modify an XA Datasource for information on configuring XA datasources.

Table 13.2. Datasource Parameters for XA Recovery
Attribute	Description
recovery-username	The user name to use to connect to the resource for recovery. Note that if this is not specified, the datasource security settings will be used.
recovery-password	The password to use to connect to the resource for recovery. Note that if this is not specified, the datasource security settings will be used.
recovery-security-domain	The security domain to use to connect to the resource for recovery.
recovery-plugin-class-name	If you need to use a custom recovery module, set this attribute to the fully-qualified class name of the module. The module should extend class `com.arjuna.ats.jta.recovery.XAResourceRecovery`.
recovery-plugin-properties	If you use a custom recovery module which requires properties to be set, set this attribute to the list of comma-separated `KEY=VALUE` pairs for the properties.

Disable XA Recovery

If multiple XA datasources connect to the same physical database, then XA recovery typically needs to be configured for only one of them.

Use the following management CLI command to disable recovery for an XA datasource:

/subsystem=datasources/xa-data-source=XA_DATASOURCE_NAME:write-attribute(name=no-recovery,value=true)

13.7.2. Vendor-Specific XA Recovery

Vendor-Specific Configuration

Some databases require specific configurations in order to cooperate in XA transactions managed by the JBoss EAP transaction manager. For detailed and up-to-date information, see your database vendor’s documentation.

MySQL

No special configuration is required. For more information, see the MySQL documentation.

PostgreSQL and Postgres Plus Advanced Server

For PostgreSQL to be able to handle XA transactions, change the configuration parameter max_prepared_transactions to a value greater than 0 and equal to or greater than max_connections.

Oracle

Ensure that the Oracle user (USER) has access to the tables needed for recovery.

GRANT SELECT ON sys.dba_pending_transactions TO USER;
GRANT SELECT ON sys.pending_trans$ TO USER;
GRANT SELECT ON sys.dba_2pc_pending TO USER;
GRANT EXECUTE ON sys.dbms_xa TO USER;

If the Oracle user does not have the proper permissions, you may see an error such as the following:

WARN  [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.xarecovery1] Local XARecoveryModule.xaRecovery  got XA exception javax.transaction.xa.XAException, XAException.XAER_RMERR

Microsoft SQL Server

For more information, see the Microsoft SQL Server documentation, including http://msdn.microsoft.com/en-us/library/aa342335.aspx.

IBM DB2

No special configuration is required. For more information, see the IBM DB2 documentation.

Sybase

Sybase expects XA transactions to be enabled on the database. Without correct database configuration, XA transactions will not work. The enable xact coordination parameter enables or disables Adaptive Server transaction coordination services. When this parameter is enabled, Adaptive Server ensures that updates to remote Adaptive Server data commit or roll back with the original transaction.

To enable transaction coordination, use:

sp_configure 'enable xact coordination', 1

MariaDB

No special configuration is required. For more information, see the MariaDB documentation.

Known Issues

These known issues with handling XA transactions are for the specific database and JDBC driver versions supported with JBoss EAP 7. For up-to-date information on the supported databases, see JBoss EAP supported configurations.

MySQL

MySQL is not fully capable of handling XA transactions. If a client is disconnected from MySQL, then all the information about such transactions is lost. See this MySQL bug for more information. Note that this issue was fixed in MySQL 5.7.

PostgreSQL and Postgres Plus Advanced Server

The JDBC driver returns the XAER_RMERR XAException error code when a network failure occurs during the commit phase of two-phase commit (2PC). This error signals an unrecoverable catastrophic event to the transaction manager, but the transaction is left in the in-doubt state on the database side and could be easily corrected after network connection is established again. The correct return code should be XAER_RMFAIL or XAER_RETRY. The incorrect error code causes the transaction to be left in the Heuristic state on the JBoss EAP side and holding locks in the database which requires manual intervention. See this PostgreSQL bug for more information.

If a connection failure happens when the one-phase commit optimization is used, the JDBC driver returns XAER_RMERR, but the XAER_RMFAIL error code should be returned. This could lead to data inconsistency if the database commits data during one-phase commit and the connection goes down at that moment, then the client is informed that transaction was rolled back.

The Postgres Plus JDBC driver returns XIDs for all prepared transactions that exist in the Postgres Plus Server, so there is no way to determine the database to which the XID belongs. If you define more than one data source for the same database in JBoss EAP, an in-doubt transaction recovery attempt could be run under wrong account, which causes the recovery to fail.

Oracle

The JDBC driver returns XIDs belonging to all users on the database instance, when the Recovery Manager calls recovery using datasource configured with some user credentials. The JDBC driver throws the exception ORA-24774: cannot switch to specified transaction because it tries to recover XIDs belonging to other users.

The workaround for this issue is to grant FORCE ANY TRANSACTION privilege to the user whose credentials are used in recovery datasource configuration. More information about configuring the privilege can be found here: http://docs.oracle.com/database/121/ADMIN/ds_txnman.htm#ADMIN12259

Microsoft SQL Server

IBM DB2

If a connection failure happens during a one-phase commit, the JDBC driver returns XAER_RETRY, but the XAER_RMFAIL error code should be returned. This could lead to data inconsistency if the database commits data during one-phase commit and the connection goes down at that moment, then the client is informed that transaction was rolled back.

Sybase

MariaDB

MariaDB is not fully capable of handling XA transactions. If a client is disconnected from MariaDB, then all the information about such transactions is lost.

13.8. Database Connection Validation

Database maintenance, network problems, or other outage events may cause JBoss EAP to lose the connection to the database. In order to recover from these situations, you can enable database connection validation for your datasources.

To configure database connection validation, you specify the validation timing method (when the validation occurs), the validation mechanism (how the validation is performed), and the exception sorter (how exceptions are handled).

Choose one of the validation timing methods.
validate-on-match
When the validate-on-match option is set to true, the database connection is validated every time it is checked out from the connection pool using the validation mechanism specified in the next step.
If a connection is not valid, a warning is written to the log and the next connection in the pool is retrieved. This process continues until a valid connection is found. If you prefer not to cycle through every connection in the pool, you can use the use-fast-fail option. If a valid connection is not found in the pool, a new connection is created. If the connection creation fails, an exception is returned to the requesting application.
This setting results in the quickest recovery but creates the highest load on the database. However, this is the safest selection if the minimal performance hit is not a concern.
background-validation
When the background-validation option is set to true, connections are validated periodically in a background thread prior to use. The frequency of the validation is specified by the background-validation-millis property. The default value of background-validation-millis is 0, meaning that it is disabled.
When determining the value of the background-validation-millis property, consider the following:
This value should not be set to the same value as your idle-timeout-minutes setting.
The lower the value, the more frequently the pool is validated and the sooner invalid connections are removed from the pool.
Lower values take more database resources. Higher values result in less frequent connection validation checks and use less database resources, but dead connections are undetected for longer periods of time.
Note
These options are mutually exclusive. If validate-on-match is set to true, then background-validation must be set to false. If background-validation is set to true, then validate-on-match must be set to false.
Choose one of the validation mechanisms.
valid-connection-checker-class-name
Using valid-connection-checker-class-name is the preferred validation mechanism. This specifies a connection checker class that is used to validate connections for the particular database in use. JBoss EAP provides the following connection checkers:
org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLReplicationValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.novendor.NullValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker
org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker
check-valid-connection-sql
Using check-valid-connection-sql, you provide the SQL statement that will be used to validate the connection.
The following is an example SQL statement that you might use to validate Oracle connections.
```
select 1 from dual
```
The following is an example SQL statement that you might use to validate MySQL or PostgreSQL connections.
```
select 1
```
Set the exception sorter class name.
When an exception is marked as fatal, the connection is closed immediately, even if the connection is participating in a transaction. Use the exception sorter class option to properly detect and clean up after fatal connection exceptions. Choose the appropriate JBoss EAP exception sorter for your datasource type.
- org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.informix.InformixExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.novendor.NullExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter
- org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter

13.9. Datasource Security

Datasource security refers to encrypting or obscuring passwords for datasource connections. These passwords can be stored in plain text in configuration files, however this represents a security risk.

The preferred solution for datasource security is the use of either security domains or password vaults. Examples of each are included below.

Secure a Datasource Using a Security Domain

A security domain for the datasource is defined.

 <security-domain name="DsRealm" cache-type="default">
  <authentication>
    <login-module code="ConfiguredIdentity" flag="required">
      <module-option name="userName" value="sa"/>
      <module-option name="principal" value="sa"/>
      <module-option name="password" value="sa"/>
    </login-module>
  </authentication>
</security-domain>

Note

If a security domain will be used with multiple datasources, then caching should be disabled on the security domain. This can be accomplished by setting the value of the cache-type attribute to none or by removing the attribute altogether. However, if caching is desired, then a separate security domain should be used for each datasource.

The DsRealm security domain is then referenced by the datasource configuration.

<datasources>
  <datasource jndi-name="java:jboss/datasources/securityDs"
    pool-name="securityDs">
    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
      <driver>h2</driver>
      <new-connection-sql>select current_user()</new-connection-sql>
      <security>
        <security-domain>DsRealm</security-domain>
      </security>
    </datasource>
</datasources>

For more information on using Security Domains, see the How to Configure Identity Management guide.

Secure a Datasource Using a Password Vault

<security>
  <user-name>admin</user-name>
  <password>${VAULT::ds_ExampleDS::password::N2NhZDYzOTMtNWE0OS00ZGQ0LWE4MmEtMWNlMDMyNDdmNmI2TElORV9CUkVBS3ZhdWx0}</password>
</security>

For more information on using the Password Vault, see the How To Configure Server Security guide.

13.10. Datasource Statistics

You can view core pool and JDBC runtime statistics for defined datasources. See the Datasource Statistics for a detailed list of all available statistics.

Enable Datasource Statistics

By default, datasource statistics are not enabled. The following management CLI commands enable the collection of statistics for the ExampleDS datasource.

Note

In a managed domain, precede these commands with /profile=PROFILE_NAME.

/subsystem=datasources/data-source=ExampleDS/statistics=pool:write-attribute(name=statistics-enabled,value=true)

/subsystem=datasources/data-source=ExampleDS/statistics=jdbc:write-attribute(name=statistics-enabled,value=true)

View Datasource Statistics

All datasource statistics can be retrieved from the management CLI. A subset of these statistics can be viewed from the Runtime tab of the management console.

The following management CLI command retrieves the core pool statistics for the ExampleDS datasource.

Note

In a managed domain, precede these commands with /host=HOST_NAME/server=SERVER_NAME.

/subsystem=datasources/data-source=ExampleDS/statistics=pool:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => 1,
        "AvailableCount" => 20,
        "AverageBlockingTime" => 0L,
        "AverageCreationTime" => 122L,
        "AverageGetTime" => 128L,
        "AveragePoolTime" => 0L,
        "AverageUsageTime" => 0L,
        "BlockingFailureCount" => 0,
        "CreatedCount" => 1,
        "DestroyedCount" => 0,
        "IdleCount" => 1,
        ...
}

The following management CLI command retrieves the JDBC statistics for the ExampleDS datasource.

/subsystem=datasources/data-source=ExampleDS/statistics=jdbc:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "PreparedStatementCacheAccessCount" => 0L,
        "PreparedStatementCacheAddCount" => 0L,
        "PreparedStatementCacheCurrentSize" => 0,
        "PreparedStatementCacheDeleteCount" => 0L,
        "PreparedStatementCacheHitCount" => 0L,
        "PreparedStatementCacheMissCount" => 0L,
        "statistics-enabled" => true
    }
}

Note

Since statistics are runtime information, be sure to specify the include-runtime=true argument.

13.11. Capacity Policies

JBoss EAP supports defining capacity polices for JCA deployments, including datasources. Capacity policies define how physical connections for a pool are created (capacity incrementing) and destroyed (capacity decrementing). The default policies are set to create once connection per request for capacity incrementing, and destroy all connections when they time out when the idle timeout is scheduled for capacity decrementing.

To configure capacity polices, you need to specify a capacity incrementer and/or decrementer class:

Example commands

/subsystem=datasources/data-source=ExampleDS:write-attribute(name=capacity-incrementer-class, value="org.jboss.jca.core.connectionmanager.pool.capacity.SizeIncrementer")

/subsystem=datasources/data-source=ExampleDS:write-attribute(name=capacity-decrementer-class, value="org.jboss.jca.core.connectionmanager.pool.capacity.SizeDecrementer")

You can also configure properties on the specified capacity incrementer or decrementer class:

Example Commands

/subsystem=datasources/data-source=ExampleDS:write-attribute(name=capacity-incrementer-properties.size, value=2)

/subsystem=datasources/data-source=ExampleDS:write-attribute(name=capacity-decrementer-properties.size, value=2)

MaxPoolSize Incrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.MaxPoolSizeIncrementer

The MaxPoolSize incrementer policy will fill the pool to its max size for each request. This policy is useful when you want to keep the maximum number of connections available all the time.

Size Incrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.SizeIncrementer

The Size incrementer policy will fill the pool by the specified number of connections for each request. This policy is useful when you want to increment with an additional number of connections per request in anticipation that the next request will also need a connection.

Table 13.3. Size policy properties
Name	Description
Size	The number of connections that should be created

Note

This is the default increment policy with a size value of 1.

Watermark Incrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.WatermarkIncrementer

The Watermark incrementer policy will fill the pool to the specified number of connections for each request. This policy is useful when you want to keep a specified number of connections in the pool at all time.

Table 13.4. Watermark policy properties
Name	Description
Watermark	The watermark level for the number of connections

MinPoolSize Decrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.MinPoolSizeDecrementer

The MinPoolSize decrementer policy will decrement the pool to its min size for each request. This policy is useful when you want to limit the number of connections after each idle timeout request. The pool will operate in a First In First Out (FIFO) manner.

Size Decrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.SizeDecrementer

The Size decrementer policy will decrement the pool by the specified number of connections for each idle timeout request.

Table 13.5. Size policy properties
Name	Description
Size	The number of connections that should be destroyed

This policy is useful when you want to decrement an additional number of connections per idle timeout request in anticipation that the pool usage will lower over time.

The pool will operate in a First In First Out (FIFO) manner.

TimedOut Decrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.TimedOutDecrementer

The TimedOut decrementer policy will removed all connections that have timed out from the pool for each idle timeout request. The pool will operate in a First In Last Out (FILO) manner.

Note

This policy is the default decrement policy.

TimedOut/FIFO Decrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.TimedOutFIFODecrementer

The TimedOutFIFO decrementer policy will removed all connections that have timed out from the pool for each idle timeout request. The pool will operate in a First In First Out (FIFO) manner.

Watermark Decrementer Policy

Class name: org.jboss.jca.core.connectionmanager.pool.capacity.WatermarkDecrementer

The Watermark decrementer policy will decrement the pool to the specified number of connections for each idle timeout request. This policy is useful when you want to keep a specified number of connections in the pool at all time. The pool will operate in a First In First Out (FIFO) manner.

Table 13.6. Watermark policy properties
Name	Description
Watermark	The watermark level for the number of connections

13.12. Enlistment Tracing

Enlistment traces are recorded in order to help locate error situations that happens during enlistment of XAResource instances. This does have a performance overhead, so in certain situations you may want to disable these traces.

You can disable the recording of enlistment traces for a datasource using the management CLI by setting the enlistment-trace attribute to false.

Disable enlistment trace for a non-XA datasource.

data-source --name=DATASOURCE_NAME --enlistment-trace=false

Disable enlistment trace for an XA datasource.

xa-data-source --name=XA_DATASOURCE_NAME --enlistment-trace=false

Warning

Disabling the enlistment trace will make tracking down errors during transaction enlistment more difficult.

13.13. Example Datasource Configurations

13.13.1. Example MySQL Datasource

This is an example of a MySQL datasource configuration with connection information, basic security, and validation options.

Example MySQL Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/MySqlDS" pool-name="MySqlDS">
    <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
    <driver>mysql</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="mysql" module="com.mysql">
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example MySQL JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
  <resources>
    <resource-root path="mysql-connector-java-5.1.36-bin.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the MySQL JDBC driver as a core module.
```
module add --name=com.mysql --resources=/path/to/mysql-connector-java-5.1.36-bin.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource, driver-class-name=com.mysql.jdbc.Driver)

Add the MySQL datasource.

data-source add --name=MySqlDS --jndi-name=java:jboss/MySqlDS --driver-name=mysql --connection-url=jdbc:mysql://localhost:3306/jbossdb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter

13.13.2. Example MySQL XA Datasource

This is an example of a MySQL XA datasource configuration with XA datasource properties, basic security, and validation options.

Example MySQL XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/MySqlXADS" pool-name="MySqlXADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      mysqldb
    </xa-datasource-property>
    <driver>mysql</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="mysql" module="com.mysql">
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example MySQL JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
  <resources>
    <resource-root path="mysql-connector-java-5.1.36-bin.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the MySQL JDBC driver as a core module.
```
module add --name=com.mysql --resources=/path/to/mysql-connector-java-5.1.36-bin.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource, driver-class-name=com.mysql.jdbc.Driver)

Add the MySQL XA datasource.

xa-data-source add --name=MySqlXADS --jndi-name=java:jboss/MySqlXADS --driver-name=mysql --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter --xa-datasource-properties={"ServerName"=>"localhost","DatabaseName"=>"mysqldb"}

13.13.3. Example PostgreSQL Datasource

This is an example of a PostgreSQL datasource configuration with connection information, basic security, and validation options.

Example PostgreSQL Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/PostgresDS" pool-name="PostgresDS">
    <connection-url>jdbc:postgresql://localhost:5432/postgresdb</connection-url>
    <driver>postgresql</driver>
    <security>
        <user-name>admin</user-name>
        <password>admin</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
        <validate-on-match>true</validate-on-match>
        <background-validation>false</background-validation>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="postgresql" module="com.postgresql">
        <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example PostgreSQL JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.postgresql">
  <resources>
    <resource-root path="postgresql-9.3-1102.jdbc4.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the PostgreSQL JDBC driver as a core module.
```
module add --name=com.postgresql --resources=/path/to/postgresql-9.3-1102.jdbc4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=postgresql:add(driver-name=postgresql,driver-module-name=com.postgresql,driver-xa-datasource-class-name=org.postgresql.xa.PGXADataSource)

Add the PostgreSQL datasource.

data-source add --name=PostgresDS --jndi-name=java:jboss/PostgresDS --driver-name=postgresql --connection-url=jdbc:postgresql://localhost:5432/postgresdb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter

13.13.4. Example PostgreSQL XA Datasource

This is an example of a PostgreSQL XA datasource configuration with XA datasource properties, basic security, and validation options.

Example PostgreSQL XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/PostgresXADS" pool-name="PostgresXADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
      5432
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      postgresdb
    </xa-datasource-property>
    <driver>postgresql</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="postgresql" module="com.postgresql">
      <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example PostgreSQL JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.postgresql">
  <resources>
    <resource-root path="postgresql-9.3-1102.jdbc4.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the PostgreSQL JDBC driver as a core module.
```
module add --name=com.postgresql --resources=/path/to/postgresql-9.3-1102.jdbc4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=postgresql:add(driver-name=postgresql,driver-module-name=com.postgresql,driver-xa-datasource-class-name=org.postgresql.xa.PGXADataSource)

Add the PostgreSQL XA datasource.

xa-data-source add --name=PostgresXADS --jndi-name=java:jboss/PostgresXADS --driver-name=postgresql --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter --xa-datasource-properties={"ServerName"=>"localhost","PortNumber"=>"5432","DatabaseName"=>"postgresdb"}

13.13.5. Example Oracle Datasource

This is an example of an Oracle datasource configuration with connection information, basic security, and validation options.

Example Oracle Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/OracleDS" pool-name="OracleDS">
    <connection-url>jdbc:oracle:thin:@localhost:1521:XE</connection-url>
    <driver>oracle</driver>
    <security>
        <user-name>admin</user-name>
        <password>admin</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
        <validate-on-match>true</validate-on-match>
        <background-validation>false</background-validation>
        <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="oracle" module="com.oracle">
        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Oracle JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.oracle">
    <resources>
        <resource-root path="ojdbc7.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Oracle JDBC driver as a core module.
```
module add --name=com.oracle --resources=/path/to/misc/jdbc_drivers/oracle/ojdbc7.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=oracle:add(driver-name=oracle,driver-module-name=com.oracle,driver-xa-datasource-class-name=oracle.jdbc.xa.client.OracleXADataSource)

Add the Oracle datasource.

data-source add --name=OracleDS --jndi-name=java:jboss/OracleDS --driver-name=oracle --connection-url=jdbc:oracle:thin:@localhost:1521:XE --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker

13.13.6. Example Oracle XA Datasource

Important

The following settings must be applied for the user accessing an Oracle XA datasource in order for XA recovery to operate correctly. The value user is the user defined to connect from JBoss EAP to Oracle:

GRANT SELECT ON sys.dba_pending_transactions TO user;
GRANT SELECT ON sys.pending_trans$ TO user;
GRANT SELECT ON sys.dba_2pc_pending TO user;
GRANT EXECUTE ON sys.dbms_xa TO user;

This is an example of an Oracle XA datasource configuration with XA datasource properties, basic security, and validation options.

Example Oracle XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/OracleXADS" pool-name="OracleXADS">
    <xa-datasource-property name="URL">
      jdbc:oracle:thin:@oracleHostName:1521:orcl
    </xa-datasource-property>
    <driver>oracle</driver>
    <xa-pool>
      <is-same-rm-override>false</is-same-rm-override>
    </xa-pool>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="oracle" module="com.oracle">
      <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Oracle JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.oracle">
    <resources>
        <resource-root path="ojdbc7.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Oracle JDBC driver as a core module.
```
module add --name=com.oracle --resources=/path/to/misc/jdbc_drivers/oracle/ojdbc7.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=oracle:add(driver-name=oracle,driver-module-name=com.oracle,driver-xa-datasource-class-name=oracle.jdbc.xa.client.OracleXADataSource)

Add the Oracle XA datasource.

xa-data-source add --name=OracleXADS --jndi-name=java:jboss/OracleXADS --driver-name=oracle --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker --same-rm-override=false --xa-datasource-properties={"URL"=>"jdbc:oracle:thin:@oracleHostName:1521:orcl"}

13.13.7. Example Microsoft SQL Server Datasource

This is an example of a Microsoft SQL Server datasource configuration with connection information, basic security, and validation options.

Example Microsoft SQL Server Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/MSSQLDS" pool-name="MSSQLDS">
    <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=MyDatabase</connection-url>
    <driver>sqlserver</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="sqlserver" module="com.microsoft">
      <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Microsoft SQL Server JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.microsoft">
  <resources>
    <resource-root path="sqljdbc41.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Microsoft SQL Server JDBC driver as a core module.
```
module add --name=com.microsoft --resources=/path/to/sqljdbc41.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=sqlserver:add(driver-name=sqlserver,driver-module-name=com.microsoft,driver-xa-datasource-class-name=com.microsoft.sqlserver.jdbc.SQLServerXADataSource)

Add the Microsoft SQL Server datasource.

data-source add --name=MSSQLDS --jndi-name=java:jboss/MSSQLDS --driver-name=sqlserver --connection-url=jdbc:sqlserver://localhost:1433;DatabaseName=MyDatabase --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter

13.13.8. Example Microsoft SQL Server XA Datasource

This is an example of a Microsoft SQL Server XA datasource configuration with XA datasource properties, basic security, and validation options.

Example Microsoft SQL Server XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/MSSQLXADS" pool-name="MSSQLXADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      mssqldb
    </xa-datasource-property>
    <xa-datasource-property name="SelectMethod">
      cursor
    </xa-datasource-property>
    <driver>sqlserver</driver>
    <xa-pool>
      <is-same-rm-override>false</is-same-rm-override>
    </xa-pool>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="sqlserver" module="com.microsoft">
      <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Microsoft SQL Server JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.microsoft">
  <resources>
    <resource-root path="sqljdbc41.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Microsoft SQL Server JDBC driver as a core module.
```
module add --name=com.microsoft --resources=/path/to/sqljdbc41.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=sqlserver:add(driver-name=sqlserver,driver-module-name=com.microsoft,driver-xa-datasource-class-name=com.microsoft.sqlserver.jdbc.SQLServerXADataSource)

Add the Microsoft SQL Server XA datasource.

xa-data-source add --name=MSSQLXADS --jndi-name=java:jboss/MSSQLXADS --driver-name=sqlserver --user-name=admin --password=admin --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"localhost","DatabaseName"=>"mssqldb","SelectMethod"=>"cursor"}

13.13.9. Example IBM DB2 Datasource

This is an example of an IBM DB2 datasource configuration with connection information, basic security, and validation options.

Example IBM DB2 Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/DB2DS" pool-name="DB2DS">
    <connection-url>jdbc:db2://localhost:50000/ibmdb2db</connection-url>
    <driver>ibmdb2</driver>
    <pool>
      <min-pool-size>0</min-pool-size>
      <max-pool-size>50</max-pool-size>
    </pool>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2StaleConnectionChecker"/>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="ibmdb2" module="com.ibm">
        <xa-datasource-class>com.ibm.db2.jcc.DB2XADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example IBM DB2 JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.ibm">
  <resources>
    <resource-root path="db2jcc4.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the IBM DB2 JDBC driver as a core module.
```
module add --name=com.ibm --resources=/path/to/db2jcc4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=ibmdb2:add(driver-name=ibmdb2,driver-module-name=com.ibm,driver-xa-datasource-class-name=com.ibm.db2.jcc.DB2XADataSource)

Add the IBM DB2 datasource.

data-source add --name=DB2DS --jndi-name=java:jboss/DB2DS --driver-name=ibmdb2 --connection-url=jdbc:db2://localhost:50000/ibmdb2db --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2StaleConnectionChecker --min-pool-size=0 --max-pool-size=50

13.13.10. Example IBM DB2 XA Datasource

This is an example of an IBM DB2 XA datasource configuration with XA datasource properties, basic security, and validation options.

Example IBM DB2 XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/DB2XADS" pool-name="DB2XADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      ibmdb2db
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
      50000
    </xa-datasource-property>
    <xa-datasource-property name="DriverType">
      4
    </xa-datasource-property>
    <driver>ibmdb2</driver>
    <xa-pool>
      <is-same-rm-override>false</is-same-rm-override>
    </xa-pool>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <recovery>
      <recover-plugin class-name="org.jboss.jca.core.recovery.ConfigurableRecoveryPlugin">
        <config-property name="EnableIsValid">
          false
        </config-property>
        <config-property name="IsValidOverride">
          false
        </config-property>
        <config-property name="EnableClose">
          false
        </config-property>
      </recover-plugin>
    </recovery>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2StaleConnectionChecker"/>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="ibmdb2" module="com.ibm">
        <xa-datasource-class>com.ibm.db2.jcc.DB2XADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example IBM DB2 JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.ibm">
  <resources>
    <resource-root path="db2jcc4.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the IBM DB2 JDBC driver as a core module.
```
module add --name=com.ibm --resources=/path/to/db2jcc4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=ibmdb2:add(driver-name=ibmdb2,driver-module-name=com.ibm,driver-xa-datasource-class-name=com.ibm.db2.jcc.DB2XADataSource)

Add the IBM DB2 XA datasource.

xa-data-source add --name=DB2XADS --jndi-name=java:jboss/DB2XADS --driver-name=ibmdb2 --user-name=admin --password=admin --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2ValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2ExceptionSorter --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.db2.DB2StaleConnectionChecker --same-rm-override=false --recovery-plugin-class-name=org.jboss.jca.core.recovery.ConfigurableRecoveryPlugin --recovery-plugin-properties={"EnableIsValid"=>"false","IsValidOverride"=>"false","EnableClose"=>"false"} --xa-datasource-properties={"ServerName"=>"localhost","DatabaseName"=>"ibmdb2db","PortNumber"=>"50000","DriverType"=>"4"}

13.13.11. Example Sybase Datasource

This is an example of a Sybase datasource configuration with connection information, basic security, and validation options.

Example Sybase Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/SybaseDB" pool-name="SybaseDB">
    <connection-url>jdbc:sybase:Tds:localhost:5000/DATABASE?JCONNECT_VERSION=6</connection-url>
    <driver>sybase</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="sybase" module="com.sybase">
      <xa-datasource-class>com.sybase.jdbc4.jdbc.SybXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Sybase JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.sybase">
    <resources>
      <resource-root path="jconn4.jar"/>
    </resources>
    <dependencies>
      <module name="javax.api"/>
      <module name="javax.transaction.api"/>
    </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Sybase JDBC driver as a core module.
```
module add --name=com.sybase --resources=/path/to/jconn4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=sybase:add(driver-name=sybase,driver-module-name=com.sybase,driver-xa-datasource-class-name=com.sybase.jdbc4.jdbc.SybXADataSource)

Add the Sybase datasource.

data-source add --name=SybaseDB --jndi-name=java:jboss/SybaseDB --driver-name=sybase --connection-url=jdbc:sybase:Tds:localhost:5000/DATABASE?JCONNECT_VERSION=6 --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter

13.13.12. Example Sybase XA Datasource

This is an example of a Sybase XA datasource configuration with XA datasource properties, basic security, and validation options.

Example Sybase XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/SybaseXADS" pool-name="SybaseXADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      mydatabase
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
      4100
    </xa-datasource-property>
    <xa-datasource-property name="NetworkProtocol">
      Tds
    </xa-datasource-property>
    <driver>sybase</driver>
    <xa-pool>
      <is-same-rm-override>false</is-same-rm-override>
    </xa-pool>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="sybase" module="com.sybase">
      <xa-datasource-class>com.sybase.jdbc4.jdbc.SybXADataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example Sybase JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.sybase">
    <resources>
      <resource-root path="jconn4.jar"/>
    </resources>
    <dependencies>
      <module name="javax.api"/>
      <module name="javax.transaction.api"/>
    </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the Sybase JDBC driver as a core module.
```
module add --name=com.sybase --resources=/path/to/jconn4.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=sybase:add(driver-name=sybase,driver-module-name=com.sybase,driver-xa-datasource-class-name=com.sybase.jdbc4.jdbc.SybXADataSource)

Add the Sybase XA datasource.

xa-data-source add --name=SybaseXADS --jndi-name=java:jboss/SybaseXADS --driver-name=sybase --user-name=admin --password=admin --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.sybase.SybaseExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"localhost","DatabaseName"=>"mydatabase","PortNumber"=>"4100","NetworkProtocol"=>"Tds"}

13.13.13. Example MariaDB Datasource

This is an example of a MariaDB datasource configuration with connection information, basic security, and validation options.

Example MariaDB Datasource Configuration

<datasources>
  <datasource jndi-name="java:jboss/MariaDBDS" pool-name="MariaDBDS">
    <connection-url>jdbc:mariadb://localhost:3306/jbossdb</connection-url>
    <driver>mariadb</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </datasource>
  <drivers>
    <driver name="mariadb" module="org.mariadb">
      <driver-class>org.mariadb.jdbc.Driver</driver-class>
      <xa-datasource-class>org.mariadb.jdbc.MySQLDataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example MariaDB JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="org.mariadb">
  <resources>
    <resource-root path="mariadb-java-client-1.2.3.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the MariaDB JDBC driver as a core module.
```
module add --name=org.mariadb --resources=/path/to/mariadb-java-client-1.2.3.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=mariadb:add(driver-name=mariadb,driver-module-name=org.mariadb,driver-xa-datasource-class-name=org.mariadb.jdbc.MySQLDataSource, driver-class-name=org.mariadb.jdbc.Driver)

Add the MariaDB datasource.

data-source add --name=MariaDBDS --jndi-name=java:jboss/MariaDBDS --driver-name=mariadb --connection-url=jdbc:mariadb://localhost:3306/jbossdb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter

13.13.14. Example MariaDB XA Datasource

This is an example of a MariaDB XA datasource configuration with XA datasource properties, basic security, and validation options.

Example MariaDB XA Datasource Configuration

<datasources>
  <xa-datasource jndi-name="java:jboss/MariaDBXADS" pool-name="MariaDBXADS">
    <xa-datasource-property name="ServerName">
      localhost
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
      mariadbdb
    </xa-datasource-property>
    <driver>mariadb</driver>
    <security>
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </xa-datasource>
  <drivers>
    <driver name="mariadb" module="org.mariadb">
      <driver-class>org.mariadb.jdbc.Driver</driver-class>
      <xa-datasource-class>org.mariadb.jdbc.MySQLDataSource</xa-datasource-class>
    </driver>
  </drivers>
</datasources>

Example MariaDB JDBC Driver `module.xml` File

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="org.mariadb">
  <resources>
    <resource-root path="mariadb-java-client-1.2.3.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Example Management CLI Commands

This example configuration can be achieved by using the following management CLI commands.

Add the MariaDB JDBC driver as a core module.
```
module add --name=org.mariadb --resources=/path/to/mariadb-java-client-1.2.3.jar --dependencies=javax.api,javax.transaction.api
```
Important
Using the module management CLI command to add and remove modules is provided as technology preview only. This command is not appropriate for use in a managed domain or when connecting to the management CLI remotely. Modules should be added and removed manually in a production environment.

/subsystem=datasources/jdbc-driver=mariadb:add(driver-name=mariadb,driver-module-name=org.mariadb,driver-xa-datasource-class-name=org.mariadb.jdbc.MySQLDataSource, driver-class-name=org.mariadb.jdbc.Driver)

Add the MariaDB XA datasource.

xa-data-source add --name=MariaDBXADS --jndi-name=java:jboss/MariaDBXADS --driver-name=mariadb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter --xa-datasource-properties={"ServerName"=>"localhost","DatabaseName"=>"mariadbdb"}

Este conteúdo não está disponível no idioma selecionado.

Chapter 13. Datasource Management

13.1. About JBoss EAP Datasources

About JDBC

Supported Databases

Datasource Types

The ExampleDS datasource

13.2. JDBC Drivers

13.2.1. Install a JDBC Driver as a Core Module

13.2.2. Install a JDBC Driver as a JAR Deployment

Update a JDBC Driver JAR to be JDBC 4-Compliant

13.2.3. JDBC Driver Download Locations

13.2.4. Access Vendor-Specific Classes

Using the MANIFEST.MF File

Using a jboss-deployment-structure.xml File

13.3. Creating Datasources

13.3.1. Create a Non-XA Datasource

Datasource Parameters

13.3.2. Create an XA Datasource

Datasource Parameters

13.4. Modifying Datasources

13.4.1. Modify a Non-XA Datasource

13.4.2. Modify an XA Datasource

13.5. Removing Datasources

13.5.1. Remove a Non-XA Datasource

13.5.2. Remove an XA Datasource

13.6. Testing Datasource Connections

13.7. XA Datasource Recovery

13.7.1. Configuring XA Recovery

13.7.2. Vendor-Specific XA Recovery

Vendor-Specific Configuration

Known Issues

13.8. Database Connection Validation

13.9. Datasource Security

Secure a Datasource Using a Security Domain

Secure a Datasource Using a Password Vault

13.10. Datasource Statistics

Enable Datasource Statistics

View Datasource Statistics

13.11. Capacity Policies

MaxPoolSize Incrementer Policy

Size Incrementer Policy

Watermark Incrementer Policy

MinPoolSize Decrementer Policy

Size Decrementer Policy

TimedOut Decrementer Policy

TimedOut/FIFO Decrementer Policy

Watermark Decrementer Policy

13.12. Enlistment Tracing

13.13. Example Datasource Configurations

13.13.1. Example MySQL Datasource

Example MySQL Datasource Configuration

Example MySQL JDBC Driver module.xml File

Example Management CLI Commands

13.13.2. Example MySQL XA Datasource

Example MySQL XA Datasource Configuration

Example MySQL JDBC Driver module.xml File

Example Management CLI Commands

13.13.3. Example PostgreSQL Datasource

Example PostgreSQL Datasource Configuration

Example PostgreSQL JDBC Driver module.xml File

Example Management CLI Commands

13.13.4. Example PostgreSQL XA Datasource

Example PostgreSQL XA Datasource Configuration

Example PostgreSQL JDBC Driver module.xml File

Example Management CLI Commands

13.13.5. Example Oracle Datasource

Example Oracle Datasource Configuration

Example Oracle JDBC Driver module.xml File

Example Management CLI Commands

13.13.6. Example Oracle XA Datasource

Example Oracle XA Datasource Configuration

Example Oracle JDBC Driver module.xml File

Example Management CLI Commands

13.13.7. Example Microsoft SQL Server Datasource

Example Microsoft SQL Server Datasource Configuration

Example Microsoft SQL Server JDBC Driver module.xml File

Example Management CLI Commands

13.13.8. Example Microsoft SQL Server XA Datasource

Example Microsoft SQL Server XA Datasource Configuration

Using the `MANIFEST.MF` File

Using a `jboss-deployment-structure.xml` File

Example MySQL JDBC Driver `module.xml` File

Example MySQL JDBC Driver `module.xml` File

Example PostgreSQL JDBC Driver `module.xml` File

Example PostgreSQL JDBC Driver `module.xml` File

Example Oracle JDBC Driver `module.xml` File

Example Oracle JDBC Driver `module.xml` File

Example Microsoft SQL Server JDBC Driver `module.xml` File

Example Microsoft SQL Server JDBC Driver `module.xml` File

Example IBM DB2 JDBC Driver `module.xml` File

Example IBM DB2 JDBC Driver `module.xml` File

Example Sybase JDBC Driver `module.xml` File

Example Sybase JDBC Driver `module.xml` File

Example MariaDB JDBC Driver `module.xml` File

Example MariaDB JDBC Driver `module.xml` File