Previous | Home | Next


Online resources

The following websites are also relevant as further information sources:


Accessing relational databases from WebSphere

When an application or WebSphere component requires access to a database, that database must be defined to WebSphere as a data source. Two basic definitions are required:


JDBC resources

The JDBC API provides a programming interface for data access of relational databases from the Java programming language. WAS V8.5 supports the following JDBC APIs:

In the following sections, we explain how to create and configure data source objects for use by JDBC applications. This is the only method to connect to a database if you intend to use connection pooling and distributed transactions.

The following database platforms are supported for JDBC:


JDBC providers and data sources

A data source represents a real-world source of data, such as a relational database. When a data source object is registered with a JNDI naming service, an application can retrieve it from the naming service and use it to make a connection to the associated database.

Information about the data source and how to locate it, such as its name, the server on which it resides, its port number, and so on, is stored in the form of properties on the DataSource object. Storing this information in this manner makes an application more portable because it does not need to hard code a driver name, which often includes the name of a particular vendor. It also makes maintaining the code easier because if, for example, the data source is moved to a different server, all that needs to be done is to update the relevant property in the data source. None of the code using that data source needs to be touched.

To increase application performance and reduce workload on the database, connections to it are typical pooled. In other words, when the application closes the connection, the connection is returned to a connection pool, rather than being destroyed.

Data source classes and JDBC drivers are implemented by the data source vendor. By configuring a JDBC provider, you provide information about the set of classes used to implement the data source and the database driver. Also, you provide the environment settings for the DataSource object. A driver can be written purely in the Java programming language or in a mixture of the Java programming language and the Java Native Interface (JNI) native methods.

In the next sections, we describe how to create and configure data source objects and how to configure the connection pools used to serve connections from the data source.


WebSphere support for data sources

The programming model for accessing a data source is:

  1. An application retrieves a DataSource object from the JNDI naming space.

  2. After the DataSource object is obtained, the application code calls the getConnection() method on the data source to get a Connection object. The connection is obtained from a pool of connections.

  3. After the connection is acquired, the application sends SQL queries or updates to the database.

    In addition to the data source support for Java EE6, Java EE 5, J2EE 1.4, and J2EE 1.3 applications, support is also provided for J2EE 1.2 data sources. The two types of support differ in how connections are handled. However, from an application point of view, they look the same.

    The @Resource annotation can be used to declare a reference to a datasource.The container injects the data source referred to by @Resource into the component either at runtime or when the component is initialized, depending on whether field/method injection or class injection is used.


Data source support

The primary data source support is intended for J2EE 1.3 and J2EE 1.4 and Java EE 5 and Java EE6 applications. Connection pooling is provided by two components: a JCA Connection Manager and a relational resource adapter.

The JCA Connection Manager provides connection pooling, local transactions, and security support.

The relational resource adapter provides JDBC wrappers and the JCA common client interface (CCI) implementation that allows Bean Managed Persistence (BMP), JDBC applications, and Container Managed Persistence (CMP) beans to access the database.

WAS has a Persistence Resource Adapter that provides relational persistence services to EJB beans and provides database access to BMP and JDBC applications. The Persistence Resource Adapter has two components:

The Persistence Resource Adapter code is included in the following Java packages:

The Relational Resource Adapter is the Persistence Manager's vehicle to handle data access to and from the back-end store, providing relational persistence services to EJB beans. The implementation is based on the J2EE Connector Architecture (JCA) specification and implements the JCA CCI and SPI interfaces.

When an application uses a data source, the data source uses the JCA connector architecture to get to the relational database.

For an EJB, the sequence is:

  1. An EJB performs a JNDI lookup of a data source connection factory and issues a getConnection() request.

  2. The connection factory delegates the request to a connection manager.

  3. The connection manager looks for an instance of a connection pool in the application server. If no connection pool is available, the manager uses the ManagedConnectionFactory to create a physical, or nonpooled, connection.


Version 4 data source

WAS V4 provided its own JDBC connection manager to handle connection pooling and JDBC access. This support is included with WAS V8.5 to provide support for J2EE 1.2 applications. If an application chooses to use a Version 4 data source, the WAS V8.5 application has the same connection behavior as in Version 4 of the application server.

Use the Version 4 data source for the following purposes:


Steps to define access to a database

The following steps are involved in creating a data source:

  1. Verify that connection to the database server is supported by WAS.

  2. Ensure the database is created and can be accessed by the systems that will use it.

  3. Ensure the JDBC provider classes are available on the systems that will access the database.

    If you are not sure which classes are required, consult the documentation for the provider.

  4. Create an authentication alias containing the user ID and password that will be used to access the database.

  5. Create a JDBC provider.

    The JDBC provider gives the class path of the data source implementation class and the supporting classes for database connectivity. This is vendor-specific.

    To determine if your provider is supported, refer to the JDBC Provider Summary article

  6. Create a data source. The JDBC data source encapsulates the database-specific connection settings. We can create many data sources that use the same JDBC provider.

  7. Save the changes to the master repository and, in case it is a ND environment, synchronize with the nodes involved.

  8. Test the connection to the data source.

  9. Review and adjust the connection pool settings (do this on a periodic basis). z/OS note: Always run a test connection with server scope when using the DB2 Universal JDBC Driver Provider Type 2 in WAS ND for z/OS environment. The runtime resource manager does not run in node agent. The test will therefore display a failure to load the type 2 native driver library.


Creating an authentication alias

An authentication alias is a feature that encrypts the password used by the adapter to access the database. After an authentication alias is created, we can use it when you configure the adapter (instead of directly typing the user ID and password). Using an authentication alias eliminates the need to store the password in clear text in an adapter configuration property, where it might be visible to others.

The examples in this chapter assume the database is password protected and the user ID and password are defined at run time.

To create a J2C authentication alias containing the user ID and password required to access the database:

  1. Open the Administration Console for WAS.

  2. Logon to Administration Console, and click Security | Global security.

  3. In the Authentication area, expand Java Authentication and Authorization Service, and click J2C authentication data.

  4. Click New.

  5. Enter an alias name, user ID, and password. The alias name is used later as the unique identifier when creating an application resource reference. The user ID and password must have authority to access the database.

  6. Click OK.

To create a JDBC provider from the dmgr console:

  1. Ensure the implementation classes for the provider are available to the system. The class files will need to be located on each system where the application servers will run.

  2. In the dmgr console, expand Resources | JDBC in the navigation tree.

  3. Click JDBC Providers.

  4. Select the scope.

    Although we can click All scopes to view all resources, select a specific scope to create a resource.

    JDBC resources are created at a specific scope level. The data source scope level is inherited from the JDBC provider. For example, if we create a JDBC provider at the node level and then create a data source using that JDBC provider, the data source inherits:

  5. The JDBC provider settings, such as class path, implementation class, and so on

  6. The JDBC provider scope level

    In this example, if the scope were set to node-level, all application servers running on that node register the data source in their name space.

    The dmgr console now shows all of the JDBC providers created at that scope level.

  7. Click New to start the wizard and to create a new JDBC provider.

  8. In Step 1 of the wizard, define the type of provider you will use.

    Specify the following information:

    • Database type

      Select the vendor-specific database type. If the database type you need is not in the list, click User-defined, and consult the vendor documentation for the specific properties required.

    • Provider type

      Select from a predefined list of supported provider types, based on the database type that you select.

    • Implementation type

      Select from the implementation types for the provider type selected.

    • Name

      Specify a name for this driver.

  9. The settings window for the JDBC database class path opens. shows the configuration window for the DB2 Universal JDBC Provider.

    Enter the JDBC provider properties:

    • Class path

      List of paths or JAR file names that together form the location for the resource provider classes. Pre-set using variable names specific to each type of provider. If you are creating a user-defined provider, specify the entries by pressing Enter between each entry.

      The remaining properties are dependent on the type of provider. They represent the variables used in the class path and their value. If you enter a value for a variable on this window, the corresponding variables are populated automatically with these values. Conversely, if the variables are already defined, these fields are populated with the variables.

      We can view or modify the variables by clicking...

        Environment | WebSphere Variables

      Because this example is for DB2, the following fields are available:

      • Path to db2jcc.jar, db2jcc_license_cisuz.jar

        This field specifies the values for the global variable DB2UNIVERSAL_JDBC_DRIVER_PATH, which indicates the class path jar’s location.

      • Native Library Path

        Optional path to any native libraries. Entries are required if the JBDC provider chosen uses non-Java, or native, libraries. The global variable for this is...

          UNIVERSAL_JDBC_DRIVER_NATIVEPATH

  10. After verifying the settings, click Finish.

  11. After the JDBC providers collection window is displayed, click the name of the just created JDBC provider and then click Data Sources under the Additional Properties section.

To make a data source available on multiple nodes using different directory structures using the dmgr console:

  1. Define the JDBC provider and data source at the cell scope.

    Use WebSphere environment variables for the class path and native path.

  2. Define the variables at the node scope for each node to specify the driver location for the node.

    For example, ${DRIVER_PATH} can be used for the class path in the provider definition. We can then define a variable called ${DRIVER_PATH} at the cell scope to act as a default driver location. Next, we can override that variable on any node by defining ${DRIVER_PATH} at the node scope. The node-level definition takes precedence over the cell-level definition.


Create the data source

Data sources are associated with a specific JDBC provider and can be viewed or created from the JDBC provider configuration window. You have two options when creating a data source, depending on the J2EE support of the application:

EJB 1.1 module All EJB 1.x beans must use the 4.0 data source.
EJB 2.0 module Enterprise beans that include container-managed persistence v1.x, 2.0 and beyond must use the new data source.
JDBC applications and Servlet 2.3+ components Must use the new data source.

In this section, we provide information about creating or modifying data sources for Java EE6, Java EE5, J2EE 1.4, and J2EE 1.3 applications.

The dmgr console provides a wizard that helps you create a data source. Keep in mind, however, that although the wizard provides a good way to establish connections quickly, it also establishes default-sized connection pool settings needed to tune properly before production.

To create a data source:

  1. Expand...

      Resources | JDBC in the navigation tree, and click Data sources.

  2. Select the scope.

    Although we can select All to view all resources, select a specific scope to create a resource.

    The scope determines which applications can use this data source. Select the narrowest scope required, while also ensuring the applications that require the resource can access it.

  3. Click New to start the wizard and create a new data source.

    Specify the following information:

    Data source name name by which to administer the data source. Use a name suggestive of the database name or function.
    JNDI name Data source's name as registered in the application server's name space.

    When installing an application containing modules with JDBC resource references, the resources need to be bound to the JNDI name of the resources, for example...

      jdbc/<database_name>

  4. Select an existing JDBC provider or create a new one.

    We can select a JDBC provider or create a new one.

    If you create a new JDBC provider, you are routed through the windows. If you select an existing JDBC provider, continue with the next step.

    In this case, select an existing JDBC provider, and click Next.

    Specify the following information:

    Driver type Type of JDBC Driver (2 or 4) used to access the database. To determine the best type of driver to use for the circumstances, consult the documentation for the specific driver that you use. In general, use type 2 for databases on the same system as the application server and type 4 for remote databases.
    Database Name Name of the database (or the cataloged alias).
    Server name and port Database server name and its listening port (the default for DB2 is 50000).
    Container managed persistence (CMP) Specifies if the data source is to be used for container managed persistence of EJB beans.
    Deep-dive Selecting Use this data source in container managed persistence (CMP) option causes a CMP connection factory that corresponds to this data source to be created for the relational resource adapter. The name of the connector factory created is...

      <datasourcename>_CF

    ...and the connector factory is registered in JNDI under the entry...

      eis/<jndi_name>_CMP

    To view the properties of the just created connection factory, click...

      Resources | Resource Adapters | Resource Adapters | Show built-in resources | WebSphere Relational Resource Adapter | CMP Connection Factories

    Be sure to set the scope so that it is the same scope as that for the data source.

  5. Select or define a new J2C authentication alias for the database. The authentication alias simply contains the user ID and password required to access the database. This window allows you to select an already created authentication value or create a new one. If you select an existing authentication alias, continue with the next step.

    The page provides the following options:

    Component-managed authentication alias Used for database authentication at run time. If the database is not secured, setting database authentication is not required. This is not recommended for a production environment.
    Container-managed authentication alias Specifies authentication data, which is a JAAS - J2C authentication data entry for container-managed sign on to the resource. Depending on the value selected for the Mapping-configuration alias setting, we can disable this setting.
    Mapping-configuration alias Specifies the authentication alias for the JAAS mapping configuration used by this connection factory. The DefaultPrincipalMapping JAAS configuration maps the authentication alias to the user ID and password.

    Below, the already existing authentication alias, 't60Node01Cell/samples', is selected.

  6. A summary of the options that you chose displays. Click Next to create the data source.

    The new data source is listed in the table of resources. We can test the new connection by selecting the check box to the left of the data source and clicking Test Connection. We can view or modify settings for the new data source by clicking the name in the resources list.


Connect to an Oracle database

Ensure the implementation classes for the provider are available to the system. The class files need to be located on each system where the application servers will run.


Create the JDBC provider

To create the JDBC provider:

  1. In the dmgr console, expand Resources | JDBC in the navigation tree.

  2. Click JDBC Providers.

  3. Select the scope.

    Although we can select All scopes to view all resources, select a specific scope to create a resource.

  4. Click New to start the wizard and to create a new JDBC provider.

  5. In step 1 of the wizard, define the type of provider that you will use.

    The database type is Oracle and the provider type is Oracle JDBC driver.

    Options of implementation type are XA data source or connection pool data source. XA data source types support two-phase commit transactions.

  6. In the next window, enter the directory location for the Oracle JDBC drivers.

    In this example, the ojdbc6.jar is selected by the wizard.

    If you defined a variable named ORACLE_JDBC_DRIVER_PATH and set its value, that value is displayed in the directory location field. If you enter a value here, it is saved in the variable.

  7. Review the summary of the settings, and click Finish. The new JDBC provider displays in the list of providers.


Create the data source

To create a data source:

  1. Expand...

  2. Select the scope.

    Although we can select All scopes to view all resources, select a specific scope to create a resource.

  3. Click New to create a new data source and to start a wizard.

    Enter a name for the new data source. This is used for administrative purposes. Enter the JNDI name that will be used to access the data source, and click Next.

  4. Select the Oracle JDBC driver, and click Next.

  5. Enter the properties for the database.

    Specify the following information:

    • The URL for the connection to the XA database is in the following format: jdbc:oracle:thin:@host_name:port:service

      In this case:

        jdbc:oracle:thin:@sys2.itso.ral.ibm.com:1521:XA

    • Select the data store helper class name.

    • Select the authentication alias that will provide the user ID and password required to access the database.

      This window allows you to select an already created authentication value or create a new one. If you select an existing authentication alias, continue with the next step. Here, the existing authentication alias, 'sys2CellManager/oracle_user', is selected.

    The page provides the following options:

    • Authentication alias for XA Recovery

      Used to specify the authentication alias that use during XA recovery processing. If this alias name is changed after a server failure, the subsequent XA recovery processing uses the original setting that was in effect before the failure. The database identity for the XA recovery authentication alias on a data source must have authorization to do XA recovery. If the resource adapter does not support XA transactions, this field does not display. The default value for this field is derived from the selected alias for application authentication, if one is specified.

    • Component-managed authentication alias

      This alias is used for database authentication at run time. If the database is not secured, setting database authentication is not required. This is not recommended for a production environment.

    • Container-managed authentication alias

      Specifies authentication data, which is a JAAS - J2C authentication data entry for container-managed sign on to the resource. Depending on the value selected for the Mapping-configuration alias setting, we can disable this setting.

    • Mapping-configuration alias

      Specifies the authentication alias for the Java Authentication and Authorization Service (JAAS) mapping configuration used by this connection factory. The DefaultPrincipalMapping JAAS configuration maps the authentication alias to the user ID and password.

    If the database does not support user ID and password, like Cloudscape, do not set the alias in the component-managed authentication alias or container-managed authentication alias fields. Otherwise, a warning message is written in the system log to indicate the user and password are not valid properties. This message is only a warning message because the data source is still created successfully.

  6. Review the summary of your selections, and click Finish.

  7. When the data source creation is complete, save the configuration and synchronize the changes with the nodes.

  8. Test the new connection by selecting the new data source and clicking Test connection.


Connect to an SQL Server Enterprise Edition 2008 database

Ensure the implementation classes for the provider are available to the system. The class files need to be located on each system where the application servers will run.

In general, JDBC drivers are provided by the database vendor. Information about the location and features of the JDBC provider is provided by the database vendor, not the WebSphere documentation.


Create the JDBC provider

To create a JDBC provider:

  1. In the dmgr console, expand Resources | JDBC | JDBC Providers.

  2. Select the scope.

    Although we can select All scopes to view all resources, select a specific scope to create a resource.

  3. Click New to start the wizard to create a new JDBC provider.

  4. In Step 1 of the wizard, define the type of provider that you will use.

    The database type is SQL Server, and the provider type is Microsoft SQL Server JDBC driver.

    The options of implementation type are XA data source or connection pool data source. XA data source types support two-phase commit transactions.

  5. Enter the directory location for the SQL Server JDBC drivers.

    If you defined either of the variables listed in the dialog and set their values, those values are displayed in the directory location and native library path fields.

  6. Review the summary of the settings, and click Finish. The new JDBC provider displays in the list of providers.

  7. Click the JDBC provider name to open the configuration window. Verify the correct driver is used in the class path as advised by the vendor.


Create the data source

To create a data source:

  1. Expand...

  2. Select the scope.

    Although we can select All scopes to view all resources, select a specific scope to create a resource.

  3. Click New to create a new data source and to start a wizard.

    Enter a name for the new data source. This name is used for administrative purposes. Enter the JNDI name that will be used to access the data source, and click Next.

  4. Select the Microsoft SQL Server JDBC driver, and click Next.

  5. Enter the properties for the database.

    Specify the following information:

    • Enter the database name.

    • Enter the port number on which the database server listens.

    • Enter the host name of the SQL Server installation.

  6. Select the authentication alias that provides the user ID and password required to access the database.This window allows you to select an already created authentication value or create a new one. If you select an existing authentication alias, continue with the next step. The existing authentication alias, sys2CellManager01/trade-app-alias, is selected.

    The page provides the following options:

    • Authentication alias for XA Recovery

      Used to specify the authentication alias that use during XA recovery processing. If this alias name is changed after a server failure, the subsequent XA recovery processing uses the original setting that was in effect before the failure. The database identity for the XA recovery authentication alias on a data source must have authorization to do XA recovery. If the resource adapter does not support XA transactions, this field does not display. The default value for this field is derived from the selected alias for application authentication, if one is specified.

    • Component-managed authentication alias

      This alias is used for database authentication at run time. If the database is not secured, setting database authentication is not required. This is not recommended for a production environment.

    • Container-managed authentication alias

      Specifies authentication data, which is a JAAS - J2C authentication data entry, for container-managed sign-on to the resource. Depending on the value selected for the Mapping-configuration alias setting, we can disable this setting.

    • Mapping-configuration alias

      Specifies the authentication alias for the JAAS mapping configuration used by this connection factory. The DefaultPrincipalMapping JAAS configuration maps the authentication alias to the user ID and password.

    Create a data source: Step 4

  7. Review the summary of your selections, and click Finish.

  8. When the data source creation is complete, save the configuration, and synchronize the changes with the nodes when using ND environment.

  9. Test the new connection by selecting the new data source and clicking Test connection.


Configure connection pooling properties

Performance of an application that connects to a database can be greatly affected by the availability of connections to the database and how those connections affect the performance of the database itself. There are no simple rules that tell you how to configure the connection pool properties. Your configuration is highly dependent on application, network, and database characteristics. You must coordinate the values specified in WebSphere closely with the database administrator.

Remember to include all resources in capacity planning. If 10 applications all connect to a database using separate connection pools of 10 maximum connections, this means there is a theoretical possibility of 100 concurrent connections to the database. Verify the database server has sufficient memory and processing capacity to support this requirement.

To access the connection pool properties:

  1. Navigate to Resources | JDBC | Data sources, and click the data source name.

  2. In the Additional Properties section, click Connection pool properties.

    Specify the following information:

    • Connection Timeout

      Interval, in seconds, after which a connection request times out and a ConnectionWaitTimeoutException is thrown. This action can occur when the pool is at its maximum (Max Connections) and all of the connections are in use by other applications for the duration of the wait. For example, if Connection Timeout is set to 300 and the maximum number of connections is reached, the Pool Manager waits for 300 seconds for an available physical connection. If a physical connection is not available within this time, the Pool Manager throws a ConnectionWaitTimeoutException.

      If Connection Timeout is set to 0, the pool manager waits as long as necessary until a connection is allocated.

    • Max Connections

      Specify the maximum number of physical connections that can be created in this pool.

      These connections are the physical connections to the database. After this number is reached, no new physical connections are created and the requester waits until a physical connection currently in use is returned to the pool or a ConnectionWaitTimeoutException is thrown. For example, if Max Connections is set to 5, and there are five physical connections in use, the Pool Manager waits for the amount of time specified in Connection Timeout for a physical connection to become free. If, after that time, there are still no free connections, the Pool Manager throws a ConnectionWaitTimeoutException to the application.

    • Min Connections

      Specify the minimum number of physical connections to be maintained. Until this number is reached, the pool maintenance thread does not discard any physical connections. However, no attempt is made to bring the number of connections up to this number. For example, if Min Connections is set to 3, and one physical connection is created, that connection is not discarded by the Unused Timeout thread. By the same token, the thread does not automatically create two additional physical connections to reach the Min Connections setting.

      Set Min Connections to zero (0) if the following conditions are true:

      • You have a firewall between the application server and database server.
      • Your systems are not busy 24x7.

    • Reap Time

      Interval, in seconds, between runs of the pool maintenance thread. For example, if Reap Time is set to 60, the pool maintenance thread runs every 60 seconds. The Reap Time interval affects the accuracy of the Unused Timeout and Aged Timeout settings. The smaller the interval you set, the greater the accuracy.

      When the pool maintenance thread runs, it discards any connections that are unused for longer than the time value specified in Unused Timeout, until it reaches the number of connections specified in Min Connections. The pool maintenance thread also discards any connections that remain active longer than the time value specified in Aged Timeout.

      If the pool maintenance thread is enabled, set the Reap Time value less than the values of Unused Timeout and Aged Timeout.

      The Reap Time interval also affects performance. Smaller intervals mean the pool maintenance thread runs more often and degrades performance.

    • Unused Timeout

      Interval in seconds after which an unused or idle connection is discarded.

      Set the Unused Timeout value higher than the Reap Timeout value for optimal performance. Unused physical connections are only discarded if the current number of connections not in use exceeds the Min Connections setting.

      Verify the database server's timeout for connections exceeds the Unused timeout property specified here. Long lived connections are normal and desirable for performance.

      For example, if the unused timeout value is set to 120, and the pool maintenance thread is enabled (Reap Time is not 0), any physical connection that remains unused for two minutes is discarded. Note that accuracy of this timeout and performance are affected by the Reap Time value.

    • Aged Timeout

      Interval in seconds before a physical connection is discarded, regardless of recent usage activity.

      Setting Aged Timeout to 0 allows active physical connections to remain in the pool indefinitely. For example, if the Aged Timeout value is set to 1200 and the Reap Time value is not 0, any physical connection that remains in existence for 1200 seconds (20 minutes) is discarded from the pool. Note that accuracy of this timeout and performance are affected by the Reap Time value.

      Set the Aged Timeout value higher than the Reap Timeout value for optimal performance.

    • Purge Policy

      Specify how to purge connections when a stale connection or fatal connection error is detected.

      Valid values are EntirePool and FailingConnectionOnly. If you choose EntirePool, all physical connections in the pool are destroyed when a stale connection is detected. If you choose FailingConnectionOnly, the pool attempts to destroy only the stale connection. The other connections remain in the pool. Final destruction of connections that are in use at the time of the error might be delayed. However, those connections are never returned to the pool.

      Many applications do not handle a StaleConnectionException in the code. Test and ensure the applications can handle them.

      Clicking the Advanced connection pool properties link allows you to modify the additional connection pool properties. These properties require advanced knowledge of how connection pooling works and how the system performs.


WAS data source properties

We can set the properties that apply to the WAS connection, rather than to the database connection. To access the connection pool properties, navigate to Resources | JDBC | Data sources, and click the data source name. Click WAS data source properties in the Additional Properties section.

Specify the following information:


Shared and unshared connections

The WAS V8.5 connection manager supports both unshareable and shareable connections. It also provides local transaction containment (LTC) in an unspecified transaction context:


Factors that determine sharing

This section explains the factors that determine sharing, and the listing here is not an exhaustive one. The product might or might not share connections under different circumstances. The factors are:

There are two transaction scopes in WAS:


Configure Shared and Unshared Connections

This section provides information about configuring shared and unshared connections:


Configuring data sources in the Liberty profile

A data source associated with different JDBC providers can be configured for database connectivity in the Liberty Profile. The JDBC providers supply the driver implementation classes required for connecting to your vendor database.

Data sources are provided by JDBC drivers and come in the following varieties:

Procedure to create the data source:

  1. The Liberty profile needs to be told where to find the JDBC driver. In server.xml, define a shared library pointing to the location of your JDBC driver JAR or compressed files.

    Shared library for DB2 

    <library id="DB2JCC4Lib">

 <fileset dir="C:/DB2/java" includes="db2jcc4.jar b2jcc_license_cisuz.jar"/>
</library>

  2. Define a data source using the JDBC driver.

    The application server configuration is described in a series of elements in the server.xml configuration file. Each element has one or more attributes or sub-elements.

    The <dataSource> configuration element defines a data source configuration. The <jdbcDriver> element identifies a JDBC driver and its attribute libraryRef identifies JDBC driver JARs and native files. The <properties.db2.jcc> is the data source properties for the IBM Data Server Driver for JDBC and SQLJ for DB2. It is the child of the complex type "dataSource".

    Data source definition for DB2 JDBC driver 

    <dataSource id="db2" jndiName="jdbc/db2">

 <jdbcDriver libraryRef="DB2JCC4Lib"/>

 <properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost"
portNumber="50000"/>
</dataSource>

    Data source for DB2 JDBC driver with XADataSource type... 

    <dataSource id="db2xa" jndiName="jdbc/db2xa" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="DB2JCC4Lib"/>
    <properties.db2.jcc databaseName="SAMPLEDB" 
                        serverName="localhost"
                        portNumber="50000"/>
</dataSource>


Configuring third-party data sources

This section provides examples of configuring data source elements for commonly used databases.

Data source for Oracle database... 

<dataSource id="oracle" jndiName="jdbc/oracle">
    <jdbcDriver libraryRef="OracleLib"/>
    <properties.oracle URL="jdbc:oracle:thin:@//localhost:1521/SAMPLEDB"/>
</dataSource>
<library id="OracleLib">
    <fileset dir="C:/Oracle/lib" includes="ojdbc6.jar"/>
</library>

Data source for Derby database 

<dataSource jndiName="jdbc/derbyEmbedded">

    <jdbcDriver libraryRef="DerbyLib"/>

    <properties.derby.embedded databaseName="/DB/SAMPLEDB"
                               createDatabase="create"/>
</dataSource>

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

Data source for a JDBC driver unknown to Liberty profile

The JDBC driver is located at...

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

<dataSource id="sample" 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">
    <fileset dir="/Drivers/SampleJDBC/" includes="sampleDriver.jar"/>
</library>


Adding a data source using developer tools

We can use IBM WAS V8.5 Liberty Profile Developer Tools to develop applications for Liberty profile, and these tools are available at no cost. Data sources for the application can be added using the developer tools. The developer tools can also be used to download and install the application-serving environment.


Application-defined data sources in the Liberty profile

In the Liberty profile, data sources to databases can be defined within the application, through annotations, or in the deployment descriptor configuration file (web.xml). Configure a shared library in the server.xml configuration file pointing to the location of the JDBC driver jars. Then using either annotations or in web.xml, the data source can be defined in the application.

Define datasouce using application annotations... 

@DataSourceDefinition(
name = "java:comp/env/jdbc/db2",
className = "com.ibm.db2.jcc.DB2DataSource",
databaseName = "SAMPLEDB",
serverName = "localhost",
portNumber = 50000,
properties = { "driverType=4" },
user = "user1"
password = "pwd1"
) 
public class MyServlet extends HttpServlet {

 @Resource(lookup="java:comp/env/jdbc/db2")
DataSource ds;
}

Define datasouce using deployment descriptor (web.xml) 

<data-source>
<name>java:comp/env/jdbc/db2</name>
<class-name>com.ibm.db2.jcc.DB2DataSource</class-name>
<server-name>localhost</server-name>
<port-number>50000</port-number>
<database-name>SAMPLEDB</database-name>
<user>user1</user>
<password>pwd1</password>
<property><name>driverType</name><value>4</value></property>

 </data-source>

This capability is limited to names in java:comp. Other name spaces, such as 

java:module, java:app, and java:global, are not available.


Runtime data source configuration update in the Liberty profile

In the Liberty profile, a data source is configured by specifying the attributes of the dataSource element in the server.xml configuration file. Many attributes for data source are updated dynamically at runtime, when changed.

The update is effective immediately.

beginTranForVendorAPIs The update is effective immediately.
commitOrRollbackOnCleanup The update is effective immediately.
connectionManagerRef All connections and the connection pool are destroyed. The data source is then managed by the new connection manager.
connectionSharing The update is applied with each first connection handle in a transaction.
isolationLevel The update is applied with new connection requests. Current connections retain their isolation level.
jdbcDriverRef All connections and the connection pool are destroyed. The new JDBC driver is then used.
jndiName All connections and the connection pool are destroyed. The new JNDI name is then used.
propertiesRef If the data source is Derby Embedded, all connections and the connection pool are destroyed before new properties go into effect. For other JDBC drivers, the new properties go into effect with new connection requests.
queryTimeout The update is effective immediately.
statementCacheSize The statement cache is resized upon next use.
supplementalJDBCTrace All connections and the connection pool are destroyed. The new setting is then used.
syncQueryTimeoutWithTransactionTimeout The update is effective immediately.
Attribute name How the configuration update is applied transactional The update is applied to new connections and existing connections not in use from the connection pool. type All connections and the connection pool are destroyed. The new setting is then used.


Configuring connection pooling properties in the Liberty profile

Connection pooling for the data sources in the Liberty profile are configured by defining a connection manager for the data source.

Defines a connectionManager element in server.xml to define the connection pool properties for a data source.

Defines a connectionManager element 

<dataSource id="db2" jndiName="jdbc/db2" connectionSharing="MatchCurrentState"
isolationLevel="TRANSACTION_READ_COMMITTED" statementCacheSize="20">

<connectionManager maxPoolSize="20" minPoolSize="5" connectionTimeout="10s"
agedTimeout="30m"/>

<jdbcDriver libraryRef="DB2JCC4Lib"/>

<properties.db2.jcc databaseName="SAMPLEDB" serverName="localhost"
portNumber="50000" currentLockTimeout="30s" user="user1" password="pwd1"/>

 </dataSource>

The server uses default values for any connection management settings that are not defined on the connection manager element. If a connection manager is not defined at all for a data source, the server uses default values for all of the settings.


Updating connection pooling configuration at runtime in Liberty profile

Runtime updates can be made to the data source connection pooling configuration without restarting the Liberty profile server.

Runtime attributes and how configuration update is applied

Attribute name How the configuration update is applied
agedTimeout The update is effective immediately.
connectionTimeout The update is effective immediately.
maxIdleTime The update is effective immediately.
maxNumberOfMCsAllowableInThread The update is effective immediately.
maxPoolSize The update is effective immediately.
minPoolSize The update is effective immediately.
numConnectionsPerThreadLocal The update is effective immediately.
reapTime The update is effective immediately.
purgePolicy The update is effective immediately.
numConnectionsPerThreadLocal The update is effective immediately.


Troubleshooting database access problems

This section describes ways to troubleshoot database access problems with WebSphere Application server. The following topics are covered:


Enabling JDBC tracing for database problems

If the problem can be reproduced are, enable a trace. To enable the tracing:

  1. Click Troubleshooting | Logs and Trace in the Application Server dmgr console.

  2. In the Logging and Tracing, select your Server | Diagnostic Trace.

  3. Go to Trace Output | File. Accept the defaults.

  4. Click OK.

  5. Select change the Log Detail Levels.

  6. Enter the following strings depending on your connection type:

    • Connecting to a database enter:

        *info:
        WAS.j2c=all:
        RRA=all:
        Transaction=all

    • Connecting to an enterprise information system enter:

        *info:
        WAS.j2c=all:
        com.ibm.connector2.*all:
        Transaction=all

    • Connecting to a messaging system enter:

        *info:
        WAS.j2c=all:
        Messaging=all:
        JMSApi=all:
        Transaction=all

    • Save your configuration and then click OK.

    • Restart the Application Server.

    • Reproduce the problem.

    • Send the resulting trace output file to IBM support for further analysis.


Enabling ConnLeakLogic

Connection pools get exhausted due to a variety of reasons, and ConnLeakLogic can be enabled to identify the application code holding the connections for long durations. It is recommended to enable a "Runtime" trace instead of a "Configuration" trace, which allows the trace to be disabled after the diagnostic data is retrieved from the server. To enable the ConnLeakLogic:

  1. Start the application server.

  2. Enable a Runtime trace immediately after starting the server:

    1. Click Troubleshooting | Logs and Trace in the WAS dmgr console.

    2. Select the link for the server.

    3. Click Diagnostic Trace.

    4. Click Runtime tab.

    5. Click Change Log Level Details.

    6. Click the Runtime tab.

    7. In the Trace Specification field, enter ConnLeakLogic=all.

    8. Click OK.

    Ensure that you enabled the trace immediately after the server is started before any connections are obtained from the connection pool.

    A trace.log is created containing similar content as the SystemOut.log. It does not log ongoing messages, such as a WAS.j2c trace, and it causes only slight performance impact.


Dumping connection pool information using wsadmin

For collecting diagnostic data we can look at the SystemOut.log while the system is running.

If you see the J2CA0045E error message in SystemOut.log, invoke wsadmin to dump the pool contents of the data source. Use one of the following commands to dump the content of the connection pool:


Tool to debug Database Access problems

For debugging database access problems, we can use the IBM Database Connection Pool Analyzer. This tool finds JDBC connection leaks and helps to resolve JDBC connection pool problems. The tool performs the following functions:

We can download the IBM Database Connection Pool Analyzer.