1. Administering Oracle WebLogic Server with Fusion Middleware Control
  2. WebLogic Server JDBC Data Sources

12 WebLogic Server JDBC Data Sources

This chapter describes how to create, monitor, control, and configure data sources deployed to the current domain. Java Database Connectivity (JDBC) enables you to configure database connectivity through JDBC data sources in your WebLogic Server domain. A data source is a Java EE standard method of configuring connectivity to a database. Each WebLogic Server data source contains a pool of database connections.

Applications look up the data source on the JNDI tree or in the local application context and then reserve a database connection with the getConnection method. Data sources and their connection pools provide connection management processes that help keep your system running efficiently.

This chapter includes the following sections:

Create JDBC data sources

You must create a data source for each database to which you want to connect. If you need more than one set of configuration options for a database, then you can create more than one data source that includes connections to the same database. This section includes the following tasks:

Parent topic: WebLogic Server JDBC Data Sources

Create JDBC generic data sources

Before you begin

Make sure that the JDBC drivers that you want to use to create database connections are installed on all server instances on which you want to deploy the data source. Some JDBC drivers are installed with WebLogic Server, including Oracle Type 4 JDBC drivers for DB2, Informix, MS SQL Server, and Sybase.

For more information about working with JDBC drivers, see Using JDBC Drivers with WebLogic Server.

To create a JDBC generic data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.

    The JDBC Data Sources page is displayed.

  2. Click Create, then select Generic Data Source.
  3. Define the configuration options for your JDBC data source on each page of the following sections.
  4. Then, click Create to save the JDBC data source configuration and deploy the data source to the targets that you selected.

Parent topic: Create JDBC data sources

Data Source Properties

On the Data Sources Properties page, define the general configuration options for this JDBC data source.

  • Data Source Name: enter a name for this JDBC data source. This name is used in the configuration file (config.xml) and whenever referring to this data source.

  • Scope: select the scope in which you want to create this JDBC data source.

  • Driver Class Name: select the JDBC driver you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS.

    Note:

    You must install JDBC drivers before you can use them to create database connections. Some JDBC drivers are installed with WebLogic Server, but many are not installed.

  • JNDI Name: enter the JNDI path to where this JDBC data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

  • Row Prefetch Enabled: select to enable multiple rows to be "prefetched" (that is, sent from the server to the client) in one server access.

  • Row Prefetch Size: if you enabled row prefetching, specify the number of result set rows to prefetch for a client.

  • Stream Chunk Size: specify the data chunk size for steaming data types.

See Configuration Options.

Connection Properties

On the Connection Properties page:

  1. On the Connection Properties page, enter values for the following properties in the Database Connection Information section:

    • Database URL: enter the URL of the database to connect to. The format of the URL varies by JDBC driver.

    • Password: enter the database account password to use to create database connections.

    • Test Table Name or SQL Statement: enter the name of the database table or SQL statement to use to test physical database connections.

      This name is required when you specify a Test Frequency and enable Test Reserved Connections. The default SQL code used to test a connection is select count(*) from TestTableName. If the Test Table Name begins with SQL, then the rest of the string following that leading token will be taken as a literal SQL statement that will be used to test connections instead of the standard query. For example: SELECT 1 FROM DUAL.

  2. In the Properties section, click Add and enter the properties that are required by the data source class. For example: server=dbserver1.
  3. In the System Properties section, click Add and enter the system properties that are required by the data source class. For example: server=dbserver1.
  4. In the Encrypted Properties section, click Add and enter the encrypted properties for this data source. For example: password=value.
  5. In the Connection Pool Properties section, enter values for the following properties:

    • Initial Capacity: enter the number of physical connections to create when creating the connection pool.

    • Maximum Capacity: enter the maximum number of physical connections that this connection pool can contain.

    • Minimum Capacity: enter the minimum number of physical connections that this connection pool can contain.

    • Statement Cache Type: select the algorithm used for maintaining the prepared statements stored in the statement cache.

    • Statement Cache Size: enter the number of prepared and callable statements stored in the cache. (This may increase server performance.)

  6. In the Advanced section, enter values for the following properties:

    • Test Connections On Reserve: select whether to enable WebLogic Server to test a connection before giving it to a client. (Requires that you specify a Test Table Name.)

    • Test Frequency (seconds): enter the number of seconds between when WebLogic Server tests unused connections. (Requires that you specify a Test Table Name.) Connections that fail the test are closed and reopened to re-establish a valid physical connection. If the test fails again, then the connection is closed.

    • Seconds to Trust an Idle Pool Connection: enter the number of seconds within a connection use that WebLogic Server trusts that the connection is still viable and will skip the connection test, either before delivering it to an application or during the periodic connection testing process.

    • Shrink Frequency (seconds): enter the number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand.

    • Init SQL: enter the SQL statement to execute that will initialize newly created physical database connections. Start the statement with SQL followed by a space.

    • Login Delay (seconds): enter the number of seconds to delay before creating each physical database connection. This delay supports database servers that cannot handle multiple connection requests in rapid succession.

    • Connection Creation Retry Frequency (seconds): enter the number of seconds between attempts to establish connections to the database.

    • Inactive Connection Timeout (seconds): enter the number of inactive seconds on a reserved connection before WebLogic Server reclaims the connection and releases it back into the connection pool.

    • Maximum Waiting for Connection: enter the maximum number of connection requests that can concurrently block threads while waiting to reserve a connection from the data source's connection pool.

    • Connection Reserve Timeout (seconds): enter the number of seconds after which a call to reserve a connection from the connection pool will timeout.

    • Statement Timeout: enter the time after which a statement currently being executed will time out.

    • Ignore In-Use Connections: enables the data source to be shutdown even if connections obtained from the pool are still in use.

    • Pinned-To-Thread: can improve performance by enabling execute threads to keep a pooled database connection even after the application closes the logical connection.

    • Remove Infected Connections Enabled: specifies whether a connection will be removed from the connection pool after the application uses the underlying vendor connection object.

    • Wrap Data Types: specifies whether wrapping is enabled.

    • Fatal Error Codes: specifies a comma-separated list of error codes that are treated as fatal errors.

    • Connection Labeling Callback: enter the class name of the connection labeling callback.

    • Connection Harvest Max Count: enter the maximum number of connections that may be harvested when the connection harvesting occurs.

    • Connection Harvest Trigger Count: specifies the number of available connections (trigger value) used to determine when connection harvesting occurs.

    • Connection Count of Refresh Failures Till Disable: specifies the number of reconnect failures allowed before WebLogic Server disables a connection pool to minimize the delay in handling the connection request caused by a database failure.

    • Count of Test Failures Till Flush: specifies the number of test failures allowed before WebLogic Server closes all unused connections in a connection pool to minimize the delay caused by further database testing.

See Configuration Options.

Transaction Properties

On the Transaction Properties page, follow these steps. Depending on the driver you selected on the JDBC Data Source Properties page, you may not need to specify any of these options.

Supports Global Transactions: select this check box (the default) to enable global transaction support in this data source. Clear this check box to disable (ignore) global transactions in this data source. In most cases, you should leave the option selected.

If you selected Supports Global Transactions, then select an option for transaction processing (available options vary depending on whether you select an XA driver or a non-XA driver):

  • One-Phase Commit: select this option to enable the non-XA connection to participate in a global transaction as the only transaction participant. This option is only available when you select a non-XA JDBC driver to make database connections.

  • Emulate Two-Phase Commit: enables a non-XA JDBC connection to emulate participation in distributed transactions using JTA. Select this option only if your application can tolerate heuristic conditions. This option is only available when you select a non-XA JDBC driver to make database connections.

  • Logging Last Resource: select this option to enable a non-XA JDBC connection to participate in global transactions using the Logging Last Resource (LLR) transaction optimization. Recommended in place of Emulate Two-Phase Commit. This option is only available when you select a non-XA JDBC driver to make database connections.

See Configuration Options.

Select Targets

On the Select Targets page, select the server instances and clusters on which you want to deploy the data source.

See Configuration Options.

Review

On the Review page, review the configuration for this JDBC data source.

See Configuration Options.

Create JDBC GridLink data sources

Configure database connectivity with your Oracle RAC installation by adding a JDBC GridLink data source to your WebLogic Server domain. Data sources and their connection pools provide connection management processes that help keep your system running efficiently.

To create a JDBC GridLink data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.

    The JDBC Data Sources page is displayed.

  2. Click Create, then select Active GridLink Data Source.
  3. Define the configuration options for your JDBC GridLink data source on each page of the following sections.
  4. Then, click Create to save the JDBC GridLink data source configuration and deploy the Active GridLink data source to the targets that you selected.

Parent topic: Create JDBC data sources

Data Source Properties

On the Data Sources Properties page, define the general configuration options for this JDBC data source.

  • Data Source Name: enter a name for this JDBC data source. This name is used in the configuration file (config.xml) and whenever referring to this data source.

  • Scope: select the scope in which you want to create this JDBC data source.

  • Driver Class Name: select the JDBC driver you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS.

    Note:

    You must install JDBC drivers before you can use them to create database connections. Some JDBC drivers are installed with WebLogic Server, but many are not installed.

  • JNDI Name: enter the JNDI path to where this JDBC data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

  • Row Prefetch Enabled: select to enable multiple rows to be "prefetched" (that is, sent from the server to the client) in one server access.

  • Row Prefetch Size: if you enabled row prefetching, specify the number of result set rows to prefetch for a client.

  • Stream Chunk Size: specify the data chunk size for steaming data types.

See Configuration Options.

Connection Properties

On the Connection Properties page:

  1. On the Connection Properties page, enter values for the following properties in the Database Connection Information section:

    • Database URL: enter the URL of the database to connect to. The format of the URL varies by JDBC driver.

    • Password: enter the database account password to use to create database connections.

    • Test Table Name or SQL Statement: enter the name of the database table or SQL statement to use to test physical database connections.

      This name is required when you specify a Test Frequency and enable Test Reserved Connections. The default SQL code used to test a connection is select count(*) from TestTableName. If the Test Table Name begins with SQL, then the rest of the string following that leading token will be taken as a literal SQL statement that will be used to test connections instead of the standard query. For example: SELECT 1 FROM DUAL.

  2. In the Properties section, click Add and enter the properties that are required by the data source class. For example: server=dbserver1.
  3. In the System Properties section, click Add and enter the system properties that are required by the data source class. For example: server=dbserver1.
  4. In the Encrypted Properties section, click Add and enter the encrypted properties for this data source. For example: password=value.
  5. In the Connection Pool Properties section, enter values for the following properties:

    • Initial Capacity: enter the number of physical connections to create when creating the connection pool.

    • Maximum Capacity: enter the maximum number of physical connections that this connection pool can contain.

    • Minimum Capacity: enter the minimum number of physical connections that this connection pool can contain.

    • Statement Cache Type: select the algorithm used for maintaining the prepared statements stored in the statement cache.

    • Statement Cache Size: enter the number of prepared and callable statements stored in the cache. (This may increase server performance.)

  6. In the Advanced section, enter values for the following properties:

    • Test Connections On Reserve: select whether to enable WebLogic Server to test a connection before giving it to a client. (Requires that you specify a Test Table Name.)

    • Test Frequency (seconds): enter the number of seconds between when WebLogic Server tests unused connections. (Requires that you specify a Test Table Name.) Connections that fail the test are closed and reopened to re-establish a valid physical connection. If the test fails again, then the connection is closed.

    • Seconds to Trust an Idle Pool Connection: enter the number of seconds within a connection use that WebLogic Server trusts that the connection is still viable and will skip the connection test, either before delivering it to an application or during the periodic connection testing process.

    • Shrink Frequency (seconds): enter the number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand.

    • Init SQL: enter the SQL statement to execute that will initialize newly created physical database connections. Start the statement with SQL followed by a space.

    • Connection Creation Retry Frequency (seconds): enter the number of seconds between attempts to establish connections to the database.

    • Login Delay (seconds): enter the number of seconds to delay before creating each physical database connection. This delay supports database servers that cannot handle multiple connection requests in rapid succession.

    • Inactive Connection Timeout (seconds): enter the number of inactive seconds on a reserved connection before WebLogic Server reclaims the connection and releases it back into the connection pool.

    • Maximum Waiting for Connection (seconds): enter the maximum number of connection requests that can concurrently block threads while waiting to reserve a connection from the data source's connection pool.

    • Connection Reserve Timeout (seconds): enter the number of seconds after which a call to reserve a connection from the connection pool will timeout.

    • Statement Timeout: enter the time after which a statement currently being executed will time out.

    • Ignore In-Use Connections: enables the data source to be shutdown even if connections obtained from the pool are still in use.

    • Pinned-To-Thread: can improve performance by enabling execute threads to keep a pooled database connection even after the application closes the logical connection.

    • Remove Infected Connections Enabled: specifies whether a connection will be removed from the connection pool after the application uses the underlying vendor connection object.

    • Wrap Data Types: specifies whether wrapping is enabled.

    • Fatal Error Codes: specifies a comma-separated list of error codes that are treated as fatal errors.

    • Connection Labeling Callback: enter the class name of the connection labeling callback.

    • Connection Harvest Max Count: enter the maximum number of connections that may be harvested when the connection harvesting occurs.

    • Connection Harvest Trigger Count: specifies the number of available connections (trigger value) used to determine when connection harvesting occurs.

    • Connection Count of Refresh Failures Till Disable: specifies the number of reconnect failures allowed before WebLogic Server disables a connection pool to minimize the delay in handling the connection request caused by a database failure.

    • Count of Test Failures Till Flush: specifies the number of test failures allowed before WebLogic Server closes all unused connections in a connection pool to minimize the delay caused by further database testing.

See Configuration Options.

Transaction Properties

On the Transaction Properties page, follow these steps. Depending on the driver you selected on the JDBC Data Source Properties page, you may not need to specify any of these options.

Supports Global Transactions: select this check box (the default) to enable global transaction support in this data source. Clear this check box to disable (ignore) global transactions in this data source. In most cases, you should leave the option selected.

If you selected Supports Global Transactions, then select an option for transaction processing (available options vary depending on whether you select an XA driver or a non-XA driver):

  • One-Phase Commit: select this option to enable the non-XA connection to participate in a global transaction as the only transaction participant. This option is only available when you select a non-XA JDBC driver to make database connections.

  • Emulate Two-Phase Commit: enables a non-XA JDBC connection to emulate participation in distributed transactions using JTA. Select this option only if your application can tolerate heuristic conditions. This option is only available when you select a non-XA JDBC driver to make database connections.

  • Logging Last Resource: select this option to enable a non-XA JDBC connection to participate in global transactions using the Logging Last Resource (LLR) transaction optimization. Recommended in place of Emulate Two-Phase Commit. This option is only available when you select a non-XA JDBC driver to make database connections.

See Configuration Options.

ONS Properties

On the ONS Properties page, enter values for the following properties:

  • Select Fan Enabled to subscribe to Oracle Fan Events.

  • Under ONS Nodes, click Add and enter the ONS host and port for each ONS node.

  • To test individual nodes, click Test ONS Node for an ONS host and port.

  • Optionally, configure an ONS wallet file if you want ONS to use SSL protocol.

See Configuration Options.

Select Targets

On the Select Targets page, select the server instances and clusters on which you want to deploy the data source.

See Configuration Options.

Review

On the Review page, review the configuration for this JDBC GridLink data source.

See Configuration Options.

Create JDBC multi data sources

Multi data sources provide failover and load balancing for connection requests between two or more data sources. Before you create a multi data source, you should create the data sources that the multi data source will manage, and deploy them to the same targets to which you want to deploy the multi data source.

To create a JDBC multi data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.

    The JDBC Data Sources page is displayed.

  2. Click Create, then select Multi Data Source.
  3. Define the configuration options for your JDBC multi data source on each page of the following sections.
  4. Then, click Create to save the JDBC multi data source configuration and deploy the multi data source to the targets that you selected.

Parent topic: Create JDBC data sources

Configure Data Source Properties

On the Configure Data Sources Properties page, define the general configuration options for this JDBC multi data source.

  • Data Source Name: enter a name for this JDBC multi data source. This name is used in the configuration files (config.xml and the JDBC module) and whenever referring to this multi data source.

  • Scope: select the scope in which you want to create this JDBC data source.

  • JNDI Name: enter the JNDI path to where this JDBC multi data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

  • Algorithm Type: select an algorithm option:

    • Failover: The multi data source routes connection requests to the first data source in the list; if the request fails, the request is sent to the next data source in the list, and so forth.

    • Load-Balancing: The multi data source distributes connection requests evenly to its member data sources.

See Configuration Options.

Select Targets

On the Select Targets page, select the server instances or clusters on which you want to deploy this JDBC multi data source.

The targets you select will limit the data sources that you can select as part of the multi data source. You can only select data sources that are deployed to the same targets as the multi data source.

See Configuration Options.

Select Data Source Type

On the Select Data Source Type page, select one of the following options:

  • XA Driver: The multi data source will only use data sources that use an XA JDBC driver to create database connections.

  • Non-XA Driver: The multi data source will only use data sources that use a non-XA JDBC driver to create database connections.

The option you select limits the data sources that you can select as part of the multi data source in a later step. Limiting data sources by JDBC driver type enables the WebLogic Server transaction manager to properly complete or recover global transactions that use a database connection from a multi data source.

See Configuration Options.

Add Data Sources

On the Add Data Sources page, select the data sources that you want the multi data source to use to satisfy connection requests.

See Configuration Options.

Review

On the Review page, review the configuration for this JDBC multi data source.

See Configuration Options.

Create JDBC UCP data sources

To create a JDBC UCP data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.

    The JDBC Data Sources page is displayed.

  2. Click Create, then select UCP Data Source.
  3. Define the configuration options for your UCP GridLink data source on each page of the following sections.
  4. Then, click Create to save the JDBC UCP data source configuration and deploy the UCP data source to the targets that you selected.

Parent topic: Create JDBC data sources

Data Source Properties

On the Data Sources Properties page, define the general configuration options for this UCP data source.

  • Data Source Name: enter a name for this JDBC data source. This name is used in the configuration file (config.xml) and whenever referring to this data source.

  • Scope: select the scope in which you want to create this JDBC data source.

    Note:

    The scope defaults to Global for the domain level.

  • Driver Class Name: select the JDBC driver you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS.

    Note:

    You must install JDBC drivers before you can use them to create database connections. Some JDBC drivers are installed with WebLogic Server, but many are not installed.

  • JNDI Name: enter the JNDI path to where this JDBC data source will be bound. Applications look up the data source on the JNDI tree by this name when reserving a connection.

For more information on these fields, see Configuration Options.

Connection Properties

On the Connection Properties page:

  1. On the Connection Properties page, enter values for the following properties in the Database Connection Information section:

    • Database URL: enter the URL of the database to connect to. The format of the URL varies by JDBC driver.

    • Password: enter the database account password to use to create database connections.

    • Test Table Name or SQL Statement: enter the name of the database table or SQL statement to use to test physical database connections.

      This name is required when you specify a Test Frequency and enable Test Reserved Connections. The default SQL code used to test a connection is select count(*) from TestTableName. If the Test Table Name begins with SQL, then the rest of the string following that leading token will be taken as a literal SQL statement that will be used to test connections instead of the standard query. For example: SELECT 1 FROM DUAL.

  2. In the Properties section, click Add and enter the properties that are required by the data source class. For example: server=dbserver1.
  3. In the System Properties section, click Add and enter the system properties that are required by the data source class. For example: server=dbserver1.
  4. In the Encrypted Properties section, click Add and enter the encrypted properties for this data source. For example: password=value.

For more information on these fields, see Configuration Options.

Select Targets

On the Select Targets page, select the server instances and clusters on which you want to deploy the data source.

See Configuration Options.

Review

On the Review page, review the configuration for this JDBC UCP data source.

See Configuration Options.

Create a JDBC data source from an existing data source

You can create a new JDBC data source to have the same configuration settings as an existing JDBC data source.

To create a new JDBC data source from an existing JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the row of the data source instance you want to use to create your new data source.

    The Create Like option is displayed above the table.

  3. Click Create Like.

    The Creating New JDBC Data Source assistant is displayed with the same configuration settings as the existing JDBC data source you modeled.

  4. Review each page of the Creating New JDBC Data Source assistant to ensure the property values match the desired configuration for your new JDBC data source.
  5. Click Create to save the JDBC data source configuration and deploy the new data source to the targets that you selected.

Parent topic: Create JDBC data sources

Monitor JDBC data sources

After you create a JDBC data source, you can monitor it to look for unusual activity, such as an abnormal number of requests waiting for a connection. You can also test the connection between a data source and the database. This section includes the following tasks:

Parent topic: WebLogic Server JDBC Data Sources

Monitor JDBC data sources

You can monitor a variety of statistics for each data source instance in your domain, such as the current number of database connections in the connection pool, current number of connections in use, the longest wait time for a database connection, and so forth.

To monitor the activity of the JDBC data source instances deployed to the current domain:

  1. From the WebLogic Domain menu, select Monitoring, then select JDBC Data Sources.

    The JDBC Data Sources (Monitoring) table displays statistics about the JDBC data source instances deployed to the current domain, such as:

    • Name

    • Type

    • Resource

    • Server Name

    • State

    For more information about these fields, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

  2. In the table, select the name of the JDBC data source for which you want to view configuration information.

Parent topic: Monitor JDBC data sources

Monitor a JDBC data source

To monitor the activity of a specific JDBC data source instance deployed to the current domain:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to monitor.
  3. Select Monitoring.
  4. The Monitor JDBC Data Source page displays statistics about this JDBC data source instance, such as:

    • Type

    • Resource

    • Scope

    • Server Name

    • State

    For more information about these fields, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

Parent topic: Monitor JDBC data sources

Monitor a JDBC GridLink data source

To monitor the activity of a specific JDBC GridLink data source instance deployed to the current domain:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC GridLink data source you want to monitor.
  3. Select Monitoring.

    The Monitoring page displays statistics about this JDBC GridLink data source.

  4. Optionally, in the Monitoring table, select the Active GridLink data source instance you want to monitor.

    The Instances page displays statistics about this Active GridLink data source instance, such as:

    • Instance Name

    • State

    • Current Capacity

    • Number Available

    • Active Connections Current Count

    For more information about these fields, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

  5. Optionally, in the Monitoring table, select the Active GridLink data source ONS client you want to monitor.

    The ONS page displays statistics about this Active GridLink data source ONS client, such as:

    • Host

    • Port

    • Status

    For more information about these fields, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

Parent topic: Monitor JDBC data sources

Monitor a JDBC multi data source

To monitor the activity of a specific JDBC multi data source instance deployed to the current domain:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC multi data source you want to monitor.
  3. Select Monitoring.
  4. The Monitor JDBC Multi Data Source page displays statistics about all the JDBC data sources participating in this JDBC multi data source, such as:

    • Name

    • Type

    • Resource

    • Server Name

    • State

    For more information about these fields, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

Parent topic: Monitor JDBC data sources

Test JDBC data sources

You can manually test individual instances of a data source. When you test a data source, WebLogic Server reserves a connection from the data source, tests it using the standard testing query or the query specified in Test Table Name, and then returns the database connection to the pool of connections. Test results are displayed at the top of the page.

The manual connection test relies on the Test Reserved Connections and Test Table Name attributes of the data source. Those attributes are set by default. However, if you changed either attribute, the changes will affect the database connection test.

To test a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to monitor.
  3. Select Monitoring.
  4. Click Test Data Source. Test results are displayed.

Parent topic: Monitor JDBC data sources

Control JDBC data sources

After you create a JDBC data source, you can perform administrative tasks on instances of the data source, including resetting all database connections, suspending use of the data source, and shutting down the data source. This section includes the following tasks:

Parent topic: WebLogic Server JDBC Data Sources

Start JDBC data sources

You can manually start data source instances that have a health state of Shutdown.

To start a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance you want to start.

    The control options are displayed above the table.

  3. Click Start, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Stop JDBC data sources

You can manually stop individual instances of a data source. When you stop a data source, behavior depends on the type of stop that you select:

  • Stop: shuts down a data source that has a health state of Running. If any connections from the data source are currently in use, then the Shutdown operation fails and the health state remains Running.

  • Force Stop: shuts down a data source that has a health state of Running, including forcing the disconnection of all current connection users.

To stop a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance you want to stop.

    The control options are displayed above the table.

  3. Click Stop or Force Stop, and then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Resume suspended JDBC data sources

You can manually resume data source instances that are in a Suspended state.

To resume a suspended JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance you want to resume.

    The control options are displayed above the table.

  3. Click Resume, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Suspend JDBC data sources

You can manually suspend individual instances of a data source. When you suspend a data source, applications can no longer get a database connection from the data source. For connections that are already reserved by an application, behavior depends on the type of suspension that you select:

  • Suspend: marks the data source as disabled and blocks any new connection requests. If there are any reserved connections, then the operation will wait for InactiveTimeout seconds, if configured. Otherwise, the operation waits 60 seconds before suspending all connections. If successful, then the health state is set to Suspended.

  • Force Suspend: marks the data source as disabled, blocks any new requests for a connection from the connection pool, and closes and recreates connections currently in use.

Most connections in a suspended data source remain intact. The connections are not recreated when you resume the data source, except for the connections in use when the data source is Force Suspended.

To suspend a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance you want to suspend.

    The control options are displayed above the table.

  3. Click Suspend or Force Suspend, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Shrink JDBC data source connection pools

You can manually shrink the pool of database connections in individual instances of a data source to the initial capacity or the current number of connections in use, whichever is greater.

To shrink the connection pool in a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance for which you want to shrink the connection pool.

    The control options are displayed above the table.

  3. Click Shrink, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Reset JDBC data source connections

When you reset the database connections in a JDBC data source, WebLogic Server closes and recreates all available database connections in the pool of connections in the data source.

To reset database connections in a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance for which you want to reset connections.

    The control options are displayed above the table.

  3. Click Reset, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Clear JDBC data source statement caches

If statement caching is enabled for a data source, then WebLogic Server caches are prepared and callable statements that are used in each connection in the data source. Each connection has its own cache, but the caches for each connection are configured and managed as a group. When you clear the statement cache for a data source, you clear the statement cache for all connections in the instance of the data source you select.

See Increasing Performance with the Statement Cache.

To clear the statement cache in a JDBC data source:

  1. From the WebLogic Domain menu, select Control, then select JDBC Data Sources.
  2. In the JDBC Data Sources (Control) table, select the row of the data source instance for which you want to clear the statement cache.

    The control options are displayed above the table.

  3. Click Clear Statement Cache, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Delete JDBC data sources

Before you begin

Ensure that the data source you want to delete is not used by a multi data source. If the data source you want to delete is used by a multi data source, then you must remove the data source from the multi data source before the data source can be deleted. The delete operation will fail if the data source you are attempting to delete is used by a multi data source.

To delete a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the row of the JDBC data source you want to delete.

    The Delete option is displayed above the table.

  3. Click Delete, then select Yes to confirm the action.

Parent topic: Control JDBC data sources

Control a JDBC data source

To control a specific JDBC data source instance deployed to the current domain:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to control.
  3. Select Control.
  4. From the Control JDBC Data Source page you can perform the following actions:

    • Start

    • Stop

    • Force Stop

    • Resume

    • Suspend

    • Force Suspend

    • Shrink Connection Pools

    • Reset

    • Clear Statement Cache

    For more information about these actions, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

Parent topic: Control JDBC data sources

Control a JDBC multi data source

To control a specific JDBC multi data source instance deployed to the current domain:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC multi data source you want to control.
  3. Select Control.
  4. From the Control JDBC Multi Data Source page you can perform the following actions:

    • Start

    • Stop

    • Force Stop

    • Resume

    • Suspend

    • Force Suspend

    • Shrink Connection Pools

    • Reset

    • Clear Statement Cache

    For more information about these actions, see Configuration Options.

    Optionally, select View to access the following table options:

    • Columns: add or remove the columns displayed in the table

    • Detach: detach the table (viewing option)

    • Sort: sort the columns in ascending or descending order

    • Reorder: change the order of the columns displayed

    • Query by Example

Parent topic: Control JDBC data sources

Configure JDBC data sources

When you create a JDBC data source, most data source attributes are configured so that the data source will work in your environment. However, you may need to modify a data source configuration to enable or disable specific features or to tune performance.

This section includes the following tasks:

Parent topic: WebLogic Server JDBC Data Sources

Define general configuration settings

Applications connect to databases from a data source by looking up the data source on the Java Naming and Directory Interface (JNDI) tree and then requesting a connection. The data source provides the connection to the application from its pool of data base connections.

To define general configuration settings for a specific JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Source table, select the JDBC data source you want to configure.
  3. Select Configuration, then select General.
  4. From the General Configuration page, you can define configuration settings for this JDBC data source, such as:

    • Data Source Name

    • Scope

    • Type

    • Database Type

    • Driver Class Name

    • JNDI Name

    • Row Prefetch Enabled

    • Row Prefetch Size

    • Stream Chunk Size

    For more information about these fields, see Configuration Options.

  5. Click Save.

After you finish

After you activate your changes, you will need to redeploy the data source or restart your server before the changes will take effect.

Parent topic: Configure JDBC data sources

Bind a JDBC data source to the JNDI tree with multiple names

Before you begin

You can configure a data source so that it binds to the JNDI tree with multiple names. You can use a multi-JNDI-named data source in place of legacy configurations that included multiple data sources that pointed to a single JDBC connection pool. You must either restart the system after making your change or undeploy the data source before making the change, and then redeploy it after making the change.

To add JNDI names to an existing JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select General.
  4. In the JNDI Name field, enter the names you want to use to bind the data source to the JNDI tree with each name on a separate line.

    For more information about these fields, see Configuration Options.

  5. Click Save.

After you finish

After you activate your changes, you will need to redeploy the data source or restart your server before the changes will take effect.

Configure connection pool properties

The connection pool within a JDBC data source contains a group of JDBC connections that applications reserve, use, and then return to the pool. The connection pool and the connections within it are created when the connection pool is registered, usually when starting WebLogic Server or when deploying the data source to a new target.

To configure the connection pool for a specific JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Connection Pool.
  4. From the Connection Pool page, you can define connection properties for this JDBC data source, such as:

    • Driver Class Name

    • Database URL

    • Password

    • Properties

    • System Properties

    • Encrypted Properties

    • Initial Capacity

    • Maximum Capacity

    • Minimum Capacity

    • Statement Cache Type

    • Statement Cache Size

    For more information about these fields, see Configuration Options.

  5. Optionally, expand Advanced to define advanced connection properties for this JDBC data source.
  6. Click Save.

Parent topic: Configure JDBC data sources

Configure JDBC data source testing options

You can set database connection testing options in a data source to make sure that the database connections remain healthy, which helps keep your applications running properly.

To configure testing options for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Connection Pool.
  4. Expand Advanced to display the advanced connection pool options.
  5. Select one or more of the following options:

    • Test Connections on Reserve: select this check box to test the database connection before giving it to your application when your application requests a connection from the data source.

    • Test Frequency: enable periodic background connection testing by entering the number of seconds between periodic tests.

    You can use these options to achieve the right mix of performance and fault tolerance for your system.

  6. In the Test Table Name or SQL Statement field, enter the name of a small table to use in a query to test database connections. The standard query is select 1 from table_name. If you prefer to use a different query as a connection test, then enter SQL followed by a space and the SQL code you want to use to test database connections.
  7. Optionally, in the Seconds to Trust an Idle Pool Connection field, enter the number of seconds within which, if the database connection has been used or tested, WebLogic Server will skip the connection test. This option can help reduce the overhead of connection testing and improve application performance.

    For more information about these fields, see Configuration Options.

  8. Click Save.
Configure statement cache

To improve performance, WebLogic Server can cache prepared and callable statements used in your applications (enabled by default). When an application or EJB calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Each database connection in a data source has its own statement cache.

To configure the statement cache for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Connection Pool.
  4. In the Statement Cache Type field, select one of the following options:

    • LRU: after the Statement Cache Size is met, the Least Recently Used statement is removed when a new statement is used.

    • Fixed: the first Statement Cache Size number of statements is stored and stay fixed in the cache. No new statements are cached unless the cache is manually cleared or the cache size is increased.

  5. In the Statement Cache Size field, enter the number of statements to cache per connection per data source instance.

    For more information about these fields, see Configuration Options.

  6. Click Save.
Configure connection pool capacity

You can configure the initial and maximum capacity for a JDBC connection pool.

To configure the connection capacity for a JDBC connection pool:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Connection Pool.
  4. In the Initial Capacity field, enter the number of physical connections to create when creating the connection pool.
  5. In the Maximum Capacity field, enter the maximum number of physical connections that this connection pool can contain.

    Note:

    An easy way to boost performance of JDBC in WebLogic Server applications is to set the value of Initial Capacity equal to the value for Maximum Capacity when configuring connection pools in your data source.

    For more information about these fields, see Configuration Options.

  6. Click Save.
Enable connection requests to wait for a connection

To enable connection requests to wait for a connection from a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Connection Pool.
  4. Expand Advanced to display the advanced connection pool options.
  5. In the Maximum Waiting for a Connection field, enter the maximum number of connection requests that can wait for a connection from the connection pool while blocking threads.
  6. In the Connection Reserve Timeout field, enter the number of seconds that connection requests can wait for a connection.

    For more information about these fields, see Configuration Options.

  7. Click Save.

Configure Oracle parameters

Before you begin

Additional configuration may be required to support Oracle parameters.

See Using Active GridLink Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server.

To configure Oracle parameters:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC GridLink data source you want to configure.
  3. Select Configuration, then select Oracle.
  4. From the Oracle Parameters page, you can define Oracle parameters for this JDBC GridLink data source, such as:

    • Oracle Optimize UTF8 Conversion

    • Replay Initiation Timeout

    • Connection Initialization Callback

    • Oracle Proxy Session

    • Use Database Credentials

    • Active GridLink Data Source

    • Affinity Policy

    For more information about these fields, see Configuration Options.

  5. Click Save.

Parent topic: Configure JDBC data sources

Configure ONS client parameters

Before you begin

Additional configuration may be required to support ONS client parameters.

See Using Active GridLink Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server.

To configure ONS client parameters:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC GridLink data source you want to configure.
  3. Select Configuration, then select ONS Client.
  4. From the ONS Client Configuration page, you can define ONS configuration options, such as:

    • Fan Enabled

    • ONS Nodes

    • ONS Wallet File Directory

    • ONS Wallet Password

    • Confirm ONS Wallet Password

    For more information about these fields, see Configuration Options.

  5. Click Save.

Parent topic: Configure JDBC data sources

Configure SSL for the ONS client using a Oracle wallet file

Before you begin

A wallet file is only required when the ONS client is configured to communicate with ONS daemons using SSL. Additional configuration is required to support this feature.

See Using GridLink Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server.

To configure an Oracle wallet file when using SSL:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC GridLink data source you want to configure.
  3. Select Configuration, then select ONS Client.
  4. On the ONS Client Configuration page, configure the following attributes:

    • ONS Wallet File: enter the location of the Oracle wallet file in which the SSL certificates are stored.

    • ONS Wallet Password: enter and confirm the ONS wallet password.

    For more information about these fields, see Configuration Options.

  5. Click Save.

Configure global transaction options

The transaction protocol for a JDBC data source determines how connections from the data source are handled during transaction processing.

See JDBC Data Source Transaction Options in Administering JDBC Data Sources for Oracle WebLogic Server.

Note:

If the data source uses an XA JDBC driver to create database connections, then connections from the data source will support the two-phase commit transaction protocol only. No other transaction options are available for data sources that use an XA JDBC driver.

To configure transaction options for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Transaction.
  4. From the Transaction Options page, select the Supports Global Transactions checkbox to enable global transaction support in this data source. Clear this checkbox to disable (ignore) global transactions in this data source. In most cases, you should select this option.

    If you select Supports Global Transactions, then select an option for transaction processing.

    • One-Phase Commit: Select this option to enable the non-XA connection to participate in a global transaction as the only transaction participant.

    • Emulate Two-Phase Commit: Enables a non-XA JDBC connection to emulate participation in distributed transactions using JTA. Select this option only if your application can tolerate heuristic conditions.

    • Logging Last Resource: Select this option to enable a non-XA JDBC connection to participate in global transactions using the Logging Last Resource (LLR) transaction optimization. Recommended in place of Emulate Two-Phase Commit.

    For more information about these fields, see Configuration Options.

  5. Click Save.

Parent topic: Configure JDBC data sources

Configure JDBC data source diagnostic profiling

If the monitoring statistics indicate that there is a problem in your WebLogic Server domain, then you can configure any data source to collect profile information to help you pinpoint the source of the problem. The collected profile information is stored in records in the WLDF Archive.

To configure diagnostic profiling for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Diagnostics.
  4. From the Diagnostic Profiling Options page, you can configure diagnostic profiling options, such as:

    • Profile Connection Usage

    • Profile Connection Reservation Wait

    • Profile Connection Leak

    • Profile Connection Reservation Failed

    • Profile Statement Cache Entry

    • Profile Statement Usage

    • Profile Connection Last Usage

    • Profile Connection Multithreaded Usage

    • Profile Connection Unwrap

    • Profile Harvest Frequency Seconds

    • Driver Interceptor

    For more information about these fields, see Configuration Options.

  5. Click Save.

Parent topic: Configure JDBC data sources

Configure JDBC data source identity options

You can choose the security option you want to use when mapping WebLogic Server user credentials to database user credentials. This section includes the following tasks:

Parent topic: Configure JDBC data sources

Enable credential mapping

When an application requests a database connection from the data source, WebLogic Server determines the current WebLogic Server user ID and then sets the mapped database ID as a lightweight client ID on the database connection.

Note:

This feature relies on features in the JDBC driver and DBMS. It is only supported for use with Oracle and DB2 databases and with the Oracle Thin and DB2 UDB JDBC drivers, respectively.

To enable credential mapping for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Identity Options.
  4. Select the Set Client ID On Connection checkbox.

    Note:

    Set Client ID On Connection and Enable Identity Based Connection Pooling are mutually exclusive. If you think you need both mechanisms to pass security credentials in your application environment, then create separate data sources—one for use with Set Client ID On Connection and one for use with Enable Identity Based Connection Pooling.

    For more information about this field, see Configuration Options.

  5. Click Save.
Enable identity-based connection pooling

Identity-based connection pooling allows applications to use a JDBC connection with a specific DBMS credential based on the end user application by pooling physical connections.

To enable identity-based connection pooling for a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to configure.
  3. Select Configuration, then select Identity Options.
  4. Select the Enable Identity Based Connection Pooling checkbox.

    Note:

    Set Client ID On Connection and Enable Identity Based Connection Pooling are mutually exclusive. If you think you need both mechanisms to pass security credentials in your application environment, then create separate data sources—one for use with Set Client ID On Connection and one for use with Enable Identity Based Connection Pooling.

    For more information about this field, see Configuration Options.

  5. Click Save.

Target JDBC data sources

Before you begin

Ensure that the JDBC drivers you want to use to create database connections are installed on all server instances on which you want to deploy the data source. Some JDBC drivers are installed with WebLogic Server, including WebLogic Type 4 JDBC drivers for DB2, Informix, MS SQL Server, and Sybase.

For more information about working with JDBC drivers, see Using JDBC Drivers with WebLogic Server.

When you target a JDBC data source, a new instance of the data source is created on the target. When you select a server as a target, an instance of the data source is created on the server. When you select a cluster as a target, an instance of the data source is created on all member server instances in the cluster.

To target a JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source you want to target.
  3. Select Targets.
  4. On the Targets page, select the server instances or clusters on which you want to deploy the data source.
  5. Click Save to save the JDBC data source configuration and deploy the data source to the targets that you selected.

See Configuration Options.

Parent topic: Configure JDBC data sources

Configure tags for a JDBC data source

To configure tags for a specific JDBC data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Source table, select the name of the JDBC data source you want to configure.
  3. Select Tags.
  4. On the Tags page, you can configure the tags associated with this JDBC data source:

    • In Add new tag, create a new tag by entering the tag name.

    • In Tags, move existing tags from the Available column to the Chosen column to associate these tags with this Coherence archive.

    See Configuration Options.

  5. Click Apply.

Parent topic: Configure JDBC data sources

Create JDBC data sources notes

To create notes for JDBC data source configuration:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC data source for which you want to create notes.
  3. Select Notes.
  4. On the Notes page, enter your notes.
  5. Click Save.

See Configuration Options.

Parent topic: Configure JDBC data sources

Configure a JDBC multi data source

Multi data sources provide failover and load balancing for connection requests between two or more data sources. Before you create a multi data source, you should create the data sources that the multi data source will manage, and deploy them to same targets on which you want to deploy the multi data source. Note that the underlying databases must have some kind of data synchronization or replication. WebLogic Server does not handle that replication.

To configure a specific JDBC multi data source:

  1. From the WebLogic Domain menu, select JDBC Data Sources.
  2. In the JDBC Data Sources table, select the JDBC multi data source you want to configure.
  3. To configure general settings, select Configuration, then select General.

    From the General Configuration page, you can define configuration options for this JDBC multi data source, such as:

    • Name

    • JNDI Name

    • Algorithm Name

    • Failover Request if Busy

    • Failover Callback Handler

    • Test Frequency Seconds

    For more information about these fields, see Configuration Options.

  4. To select the JDBC data sources that you would like to include as part of this JDBC multi data source, select Configuration, then select Data Sources.
  5. To deploy this JDBC multi data source to targeted server instances, select Targets.
  6. To monitor this JDBC multi data source, see Monitor a JDBC multi data source.
  7. To control this JDBC multi data source, see Control a JDBC multi data source.
  8. To create notes that describe the configuration of this JDBC data source, select Notes.
  9. Click Save.

Parent topic: Configure JDBC data sources