Configuring relational database connectivity in Liberty

You can configure a data source and JDBC provider for database connectivity. The JDBC provider supplies the driver implementation classes that are required for JDBC connectivity with your specific vendor database.

About this task

Kerberos authentication of data sources is supported for DB2®, Oracle Database, Microsoft SQL Server, and PostgreSQL.

Documentation for Kerberos authentication for JDBC data sources is available on the Open Liberty website.

Avoid trouble:

Before version 20.0.0.11, Kerberos authentication for data sources is not supported for any JDBC drivers or databases in Liberty.

The Oracle JDBC thin driver, version 21c and earlier, supports only the Oracle JDK or OpenJDK Kerberos implementations. Therefore, these versions cannot be used with the IBM SDK, Java Technology Edition, Version 8. Update to version 21c or newer of the Oracle JDBC thin driver for support of Kerberos authentication for data sources with IBM Java™ 8.

To access a database from your application, application code must use the javax.sql.DataSource interface. The application server provides a managed implementation of this javax.sql.DataSource interface, which is backed by one of the various data source or driver implementations that JDBC drivers provide. These data source or driver implementations come in the following varieties:

javax.sql.DataSource
This type of data source is the basic form. It does not provide interoperability that enhances connection pooling, and cannot participate as a two-phase capable resource in transactions that involve multiple resources.
javax.sql.ConnectionPoolDataSource
This type of data source is enabled for connection pooling. It cannot participate as a two-phase capable resource in transactions that involve multiple resources.
javax.sql.XADataSource
This type of data source is both enabled for connection pooling and is able to participate as a two-phase capable resource in transactions that involve multiple resources.
java.sql.Driver
This is a basic way to connect that requires a URL and is typically used in Java SE. Like javax.sql.DataSource, it does not provide interoperability that enhances connection pooling, and cannot participate as a two-phase capable resource in transactions that involve multiple resources.

To be usable in Liberty, your JDBC driver must provide at least one of these types of data sources or must provide a java.sql.Driver with the ServiceLoader facility. For the commonly used JDBC drivers, Liberty is already aware of the implementation class names for the various data source types. Liberty uses the ServiceLoader facility to discover JDBC driver implementations for a given URL. Based on the JDBC driver implementation class, Liberty is often able to infer the corresponding data source implementation class names. In most cases, you need to tell Liberty only where to find the JDBC driver.

Procedure

In the server.xml file, define a shared library that points to the location of your JDBC driver JAR or compressed files.
For example:
```
<library id="DB2JCCLib">
    <fileset dir="C:/DB2/java" includes="db2jccx.jar db2jcc_license_cisuz.jar"/>
</library>
```
Define one or more data sources that use the JDBC driver. If you don't specify the type of data source or a URL, Liberty chooses the data source type for you, depending on which is available.
An example that accepts the default for data source type:
```
<dataSource id="db2" jndiName="jdbc/db2">
    <jdbcDriver libraryRef="DB2JCCLib"/>
    <properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="50000"/>
</dataSource>
```
An example that uses the javax.sql.XADataSource type:
```
<dataSource id="db2xa" jndiName="jdbc/db2xa" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="DB2JCCLib"/>
    <properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="50000"/>
</dataSource>
```
A default data source is available when at least one Java EE 7 or later feature is enabled. This data source is available as java:comp/DefaultDataSource. A jndiName does not need to be specified for it. To configure the default data source, specify a data source with id set to DefaultDataSource. The following example configures the default data source to point at a DB2 database:
```
<dataSource id="DefaultDataSource">
	<jdbcDriver libraryRef="DB2JCCLib"/>
	<properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="50000"/>
</dataSource>
```
When the data source type is omitted, Liberty chooses the data source type in the following order, depending on which is available, if you are using the jdbc-4.3 feature or higher or if it is the default data source,
- javax.sql.XADataSource
- javax.sql.ConnectionPoolDataSource
- javax.sql.DataSource
If you are using the jdbc-4.2 feature, the jdbc-4.1 feature, or the jdbc-4.0 feature and it is not the default data source, then Liberty chooses the data source type in the following order, depending on which is available,
- javax.sql.ConnectionPoolDataSource
- javax.sql.DataSource
- javax.sql.XADataSource
It should be noted that the capability that is provided by XADataSource is generally a superset of the capability that is provided by the other data source types, although some JDBC vendors might have subtle differences in behavior or introduce different limitations between the various data source types that are not spelled out in the JDBC specification.

Optional: Configure attributes for the data source, such as JDBC vendor properties and connection pooling properties.

For example:

<dataSource id="DefaultDataSource" jndiName="jdbc/db2" connectionSharing="MatchCurrentState" 
            isolationLevel="TRANSACTION_READ_COMMITTED" statementCacheSize="20">
    <connectionManager maxPoolSize="20" minPoolSize="5" 
                       connectionTimeout="10s" agedTimeout="30m"/>
    <jdbcDriver libraryRef="DB2JCCLib"/>
    <properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="50000" 
                        currentLockTimeout="30s" user="user1" password="pwd1"/>
</dataSource>

For a full list of configuration attributes for the dataSource element, connectionManager element and some commonly used JDBC vendors, see Data Source (dataSource).

Optional: Configure data sources for commonly used databases according to the following examples.

For DB2

<dataSource id="DefaultDataSource" jndiName="jdbc/db2">
    <jdbcDriver libraryRef="DB2JCCLib"/>
    <properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="50000"/>
</dataSource>

<library id="DB2JCCLib">
    <fileset dir="C:/DB2/java" includes="db2jccx.jar db2jcc_license_cisuz.jar"/>
</library>

For DB2 on iSeries (Native)

<dataSource id="DefaultDataSource" jndiName="jdbc/db2iNative">
    <jdbcDriver libraryRef="DB2iNativeLib"/>
    <properties.db2.i.native databaseName="*LOCAL"/>
</dataSource>

<library id="DB2iNativeLib">
    <fileset dir="/QIBM/Proddata/OS400/Java400/ext" includes="db2_classesxx.jar"/>
</library>

For DB2 on iSeries (Toolbox)

<dataSource id="DefaultDataSource" jndiName="jdbc/db2iToolbox">
    <jdbcDriver libraryRef="DB2iToolboxLib"/>
    <properties.db2.i.toolbox databaseName="SAMPLEDB" serverName="localhost"/>
</dataSource>

<library id="DB2iToolboxLib">
    <fileset dir="/QIBM/ProdData/Http/Public/jt400/lib" includes="jt400.jar"/>
</library>

For Derby Embedded

<dataSource id="DefaultDataSource" jndiName="jdbc/derbyEmbedded">
    <jdbcDriver libraryRef="DerbyLib"/>
    <properties.derby.embedded databaseName="C:/databases/SAMPLEDB" createDatabase="create"/>
</dataSource>

<library id="DerbyLib">
    <fileset dir="C:/db-derby-x.x.x.x-bin/lib"/>
</library>

For Derby Network Client

<dataSource id="DefaultDataSource" jndiName="jdbc/derbyClient">
    <jdbcDriver libraryRef="DerbyLib"/>
    <properties.derby.client databaseName="C:/databases/SAMPLEDB" createDatabase="create" 
                             serverName="localhost" portNumber="1527"/>
</dataSource>

<library id="DerbyLib">
    <fileset dir="C:/db-derby-x.x.x.x-bin/lib"/>
</library>

For Informix® JCC

<dataSource id="DefaultDataSource" jndiName="jdbc/informixjcc">
    <jdbcDriver libraryRef="DB2JCCLib"/>
    <properties.informix.jcc databaseName="SAMPLEDB" serverName="localhost" portNumber="1526"/>
</dataSource>

<library id="DB2JCCLib">
    <fileset dir="C:/Drivers/jcc/x.x" includes="db2jccx.jar db2jcc_license_cisuz.jar"/>
</library>

For Informix JDBC

<dataSource id="DefaultDataSource" jndiName="jdbc/informix">
    <jdbcDriver libraryRef="InformixLib"/>
    <properties.informix databaseName="SAMPLEDB" ifxIFXHOST="localhost" 
                         serverName="ol_machinename" portNumber="1526"/>
</dataSource>

<library id="InformixLib">
    <fileset dir="C:/Drivers/informix" includes="ifxjdbc.jar ifxjdbcx.jar"/>
</library>

For Microsoft SQL Server (Microsoft JDBC driver)

<dataSource id="DefaultDataSource" jndiName="jdbc/mssqlserver">
    <jdbcDriver libraryRef="MSJDBCLib"/>
    <properties.microsoft.sqlserver databaseName="SAMPLEDB" 
                                    serverName="localhost" portNumber="1433"/>
</dataSource>

<library id="MSJDBCLib">
    <file name="C:/sqljdbc_x.x/enu/sqljdbcxx.jar"/>
</library>

For Microsoft SQL Server (DataDirect Connect for JDBC driver)

<dataSource id="DefaultDataSource" jndiName="jdbc/ddsqlserver">
    <jdbcDriver libraryRef="DataDirectLib"/>
    <properties.datadirect.sqlserver databaseName="SAMPLEDB" 
                                     serverName="localhost" portNumber="1433"/>
</dataSource>

<library id="DataDirectLib">
    <file name="C:/DataDirect/Connect-x.x/lib/sqlserver.jar"/>
</library>

For MySQL

<dataSource id="DefaultDataSource" jndiName="jdbc/mySQL">
    <jdbcDriver libraryRef="MySQLLib"/>
    <properties databaseName="SAMPLEDB" serverName="localhost" portNumber="3306"/>
</dataSource>

<library id="MySQLLib">
    <file name="C:/mysql-connector-java-x.x.xx/mysql-connector-java-x.x.xx.jar"/>
</library>

For PostgreSQL

<dataSource id="DefaultDataSource" jndiName="jdbc/postgres">
    <jdbcDriver libraryRef="PostgresLib"/>
    <properties.postgresql databaseName="SAMPLEDB" serverName="localhost" portNumber="5432"/>
</dataSource>

<library id="PostgresLib">
    <file name="C:/postgresql-x.x.xx/postgresql-x.x.xx.jar"/>
</library>

For Oracle

<dataSource id="DefaultDataSource" jndiName="jdbc/oracle">
    <jdbcDriver libraryRef="OracleLib"/>
    <properties.oracle URL="jdbc:oracle:thin:@//localhost:1521/SAMPLEDB"/>
</dataSource>

<library id="OracleLib">
    <file name="C:/Oracle/lib/ojdbcx.jar"/>
</library>

For Oracle Call Interface (OCI)

<dataSource id="DefaultDataSource" jndiName="jdbc/oracleOCI">
  <jdbcDriver libraryRef="OracleOciLib"/>
  <properties.oracle URL="jdbc:oracle:oci:@//localhost:1521/SAMPLEDB"/>                     
</dataSource>

<library id="OracleOciLib">
  <fileset dir="C:/Oracle/OCI/instantclient"/>
</library>

Configure the Liberty environment to recognize the OCI native client libraries by setting the path to the libraries in the Liberty server.env file on the path environment variable for your operating system.

LIBPATH

Also, set the library path in the Liberty jvm.options file.

-Djava.library.path=C:\\Oracle\\OCI\\instantclient

For more information about using the server.env and jvm.options configuration files, see Customizing the Liberty environment.

For Oracle Universal Connection Pool (UCP)

<dataSource id="DefaultDataSource" jndiName="jdbc/oracleUCP">
    <jdbcDriver libraryRef="OracleUCPLib" />
    <properties.oracle.ucp URL="jdbc:oracle:thin:@//localhost:1521/SAMPLEDB"/>
</dataSource>

<library id="OracleUCPLib">
    <fileset dir="C:/Oracle/Drivers" includes="ojdbcx.jar ucp.jar"/>
</library>

When using Oracle UCP the following data source properties are ignored: statementCacheSize and validationTimeout. The following connection manager properties are also ignored: agedTimeout, connectionTimeout, maxIdleTime, maxPoolSize, minPoolSize, purgePolicy, reapTime, maxConnectionsPerThread, and maxConnectionsPerThreadLocal. Use the equivalent Oracle UCP functionality.

For Sybase

<dataSource id="DefaultDataSource" jndiName="jdbc/sybase">
    <jdbcDriver libraryRef="SybaseLib"/>
    <properties.sybase databaseName="SAMPLEDB" serverName="localhost" portNumber="5000"/>
</dataSource>

<library id="SybaseLib">
    <file name="C:/Drivers/sybase/jconnx.jar"/>
</library>

For solidDB

<dataSource id="DefaultDataSource" jndiName="jdbc/solidDB">
    <jdbcDriver libraryRef="solidLib"/>
    <properties databaseName="SAMPLEDB" URL="jdbc:solid://localhost:2315/"/>
</dataSource>

<library id="solidLib">
    <file name="C:/Drivers/solidDB/SolidDriverx.x.jar"/>
</library>

For a JDBC driver that is not known to Liberty

<dataSource id="DefaultDataSource" jndiName="jdbc/sample" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="SampleJDBCLib" 
                javax.sql.XADataSource="com.ibm.sample.SampleXADataSource"/>
    <properties databaseName="SAMPLEDB" hostName="localhost" port="12345"/>
</dataSource>

<library id="SampleJDBCLib">
    <file name="C:/Drivers/SampleJDBC/sampleDriver.jar"/>
</library>

In the example, the JDBC driver is at C:/Drivers/SampleJDBC/sampleDriver.jar and provides an implementation of javax.sql.XADataSource named com.ibm.sample.SampleXADataSource. The JDBC driver also provides vendor-specific data source properties such as databaseName, hostName, and port.