Configure relational database connectivity in Liberty

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

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

Update to version 21c or newer of the Oracle JDBC thin driver for support of Kerberos authentication for data sources with IBM Java 8.


Steps

  1. In server.xml, define a shared library that points to the location of the JDBC driver JAR or compressed files. 

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

  2. Define one or more data sources that use the JDBC driver.

    If not specified, Liberty chooses the data source type, depending on which is available. This example accepts the default: 

      <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 we 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 we 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 provided by XADataSource is generally a superset of the capability 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.

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

      <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).

  4. 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 the operating system.

      • SHLIBPATH

      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 Liberty environment variables.

      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.


Access a database from an application

To access a database from the 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, the 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, we need to tell Liberty only where to find the JDBC driver.


Subtopics


Parent topic: Administer data access applications on Liberty


Related information