|
|
|
|
|
|
Using JDBC Connection Pooling
This topic includes the following sections:
Using the WebLogic Enterprise JDBC/XA Drivers, describes the JDBC/XA drivers provided with the WebLogic Enterprise software and introduces the use of connection pooling with those XA drivers for Oracle 8.0.5 and 8.1.5. This chapter explains how to use connection pooling with any JDBC driver supported by the WebLogic Enterprise software. This includes the WebLogic Enterprise JDBC/XA drivers and the jdbcKona drivers that are documented in Using the jdbcKona/Oracle Drivers.
If you use the jdbcKona drivers, it is not mandatory that you use JDBC connection pooling to obtain database connections. However, BEA recommends that you use the connection pooling feature with Java applications. If you use a JDBC/XA driver, you must use the JDBC connection pooling to obtain database connections.
About JDBC Connection Pooling
To conserve system resources and to improve the performance of transactional BEA WebLogic Enterprise (WLE) applications, WebLogic Enterprise allows you to define a pool of JDBC database connections. You can use the JDBC connection pooling features in WebLogic Enterprise CORBA Java and WebLogic Enterprise EJB applications.
JDBC connections are expensive resources. Opening and closing them are expensive operations. The JDBC connection pooling feature in WebLogic Enterprise provides efficient use of database connections. Creating a pool of JDBC connections gives WebLogic Enterprise applications ready access to connections that are already open. It removes the overhead of opening a new connection for each database user.
WebLogic Enterprise application developers or system administrators configure the connection pool by using a new section in the application's UBBCONFIG file: JDBCCONNPOOLS. WebLogic Enterprise applications use the connection pool at runtime to obtain JDBC connections.
The WebLogic Enterprise software provides connection pooling in its Java infrastructure, to be used on top of different JDBC drivers that integrates with the WebLogic Enterprise administration features.
About the JDBC Drivers and Connection Pooling
The WebLogic Enterprise software provides the following JDBC drivers:
When you use the jdbcKona drivers, you can optionally use the connection pooling feature described in this topic. It is not mandatory that you use connection pooling with the jdbcKona drivers. The jdbcKona drivers do not support distributed transactions (also called global, or XA transactions). A local transaction involves updates to a single resource manager, such as a database. A distributed transaction involves updates across multiple resource managers.
WebLogic Enterprise applications that use the JDBC/XA drivers, for local or distributed transactions, must use connection pooling and Oracle 8.0.5 or 8.1.5.
Table 2-1 summarizes the JDBC connection pooling configuration options and requirements.
|
JDBC Driver Category |
Without JDBC Connection Pooling |
With JDBC Connection Pooling |
|---|---|---|
|
JDBC drivers supporting the JDBC 1.x API. This includes the jdbc/Kona drivers that are included with WebLogic Enterprise:
|
Obtain JDBC connections from DriverManager. |
Obtain the WebLogic Enterprise provided DataSource (which wraps around the driver vendor's DriverManager) from the WebLogic Enterprise JNDI service provider. Then obtain JDBC connections from DataSource. |
|
JDBC drivers supporting JDBC 2.0 Extension API pertaining to Connection Pooling. |
Obtain the JDBC driver vendor's DataSource from the WebLogic Enterprise JNDI service provider. Then obtain JDBC connections from DataSource. |
Obtain the WebLogic Enterprise provided DataSource (which wraps around the JDBC driver vendor's ConnectionPoolDataSource) from the WebLogic Enterprise JNDI service provider. Then obtain JDBC connections from DataSource. |
|
Not applicable. (The WebLogic Enterprise JDBC/XA drivers for Oracle 8.0.5 or 8.1.5 driver must be used in conjunction with WebLogic Enterprise JDBC connection pooling.) |
Obtain the WebLogic Enterprise provided DataSource from the WebLogic Enterprise JNDI service provider. Then obtain JDBC connections from DataSource. |
UBBCONFIG Parameters for Connection Pooling
This section describes the application's UBBCONFIG file parameters that are related to JDBC connection pooling.
The JDBCCONNPOOLS section must be placed after the SERVERS section in the configuration file.
The JDBCCONNPOOLS section has the following characteristics:
Note: In the SERVERS section, you must also specify SRVTYPE=JAVA for the JavaServer or JavaServerXA to use JDBC connection pooling.
Some attributes are dependent on the version of the JDBC driver.
Sample UBBCONFIG File for Connection Pooling
Listing 2-1 shows a UBBCONFIG file for a sample multithreaded application that uses the WebLogic Enterprise JDBC/XA driver and connection pooling. Subsequent sections in this topic describe the parameters that are related to JDBC configuration. Bolded text is used in the listing to highlight UBBCONFIG section names and parameters that are discussed following the example.
Listing 2-1 Sample UBBCONFIG for JDBC/XA Bankapp
*RESOURCES
IPCKEY = 39211
DOMAINID = simple
MASTER = SITE1
MODEL = SHM
LDBAL = N
*MACHINES
trixie
LMID = SITE1
APPDIR = "/myapps/banking"
TUXC0NFIG = "/myapps/banking/tuxconfig"
TUXDIR "/wledir"
ULOGPFX "/usr/appdir/logs/ULOG"
MAXACCESSERS = 50
*GROUPS
SYS_GRP
LMID = SITE1
GRPNO = 1
BANK_GROUP1
LMID = SITE1
GRPNO = 2
OPENINFO = "ORACLE_XA:Oracle_XA+Acc=P/scott/tiger+SesTm=100+LogDir=.+DbgFl=0x7+MaxCur=15+Threads=true"
TMSNAME = TMS_ORA
TMSCOUNT = 2
*SERVERS
DEFAULT:
RESTART = Y
MAXGEN = 5
.
.
.
JavaServerXA
SRVGRP = BANK_GROUP1
SRVID = 2
SRVTYPE = JAVA
CLOPT = "-A -- -M 10"
RESTART = N
.
.
.
*MODULES
BankApp
SRVGRP = BANK_GROUP1
SRVID = 2
FILE = "BankApp.jar"
ARGS = "TellerFactory_1 bank_pool"
*JDBCCONNPOOLS
bank_pool
SRVGRP = BANK_GROUP1
SRVID = 2
DRIVER = "weblogic.jdbc20.oci815.Driver"
URL = "jdbc:weblogic:oracle:Beq-Local"
PROPS = "user=scott;password=tiger;server=Beq-Local"
ENABLEXA = Y
INITCAPACITY = 2
MAXCAPACITY = 10
CAPACITYINCR = 1
CREATEONSTARTUP = Y
JDBCCONNPOOLS Parameter Values
The following list describes the JDBCCONNPOOLS parameters shown in Listing 2-1, "Sample UBBCONFIG for JDBC/XA Bankapp," on page 2-5. Also described are additional JDBCCONNPOOLS parameters that are not shown in the listing.
Note: In the SERVERS section, you must also specify SRVTYPE=JAVA for the JavaServer or JavaServerXA to use JDBC connection pooling.
The weblogic.jdbc20.oci815.Driver value is the driver name for both:
The weblogic.jdbc20.oci805.Driver value is the driver name for both:
The WebLogic Enterprise JDBC/XA driver is described in Using the WebLogic Enterprise JDBC/XA Drivers. For this driver, you must use connection pooling and therefore must have a JDBCCONNPOOLS section in the application's UBBCONFIG file.
The jdbcKona/Oracle driver is described in Using the jdbcKona/Oracle Drivers. It is not mandatory that you use JDBC connection pooling with the jdbcKona/Oracle driver. However, if your application uses connection pooling, you must include a DRIVER parameter in the application's UBBCONFIG file.
The query select count(*) from TESTTABLE is used to test a connection. The table must exist and be accessible to the database user for the connection. This TESTTABLE parameter is required if the REFRESH parameter is specified, or if the parameter TESTONRELEASE or TESTONRESERVE is set to Y.
Encrypting DBPASSWORD and PROPS
The DBPASSWORD and PROPS parameters in the JDBCCONNPOOLS section specify sensitive data that you may want to encrypt. Values for these attributes can be encrypted in the UBBCONFIG file using the tmloadcf and tmunloadcf utilities.
To store a value for DBPASSWORD or PROPS in encrypted form, you initially use a text editor to enter a string of five or more continuous asterisks in the parameter value in place of the password in the UBBCONFIG file. This string of asterisks is a placeholder for the password. The following is a sample DBPASSWORD statement illustrating this:
DBPASSWORD="*******"
When tmloadcf encounters this string of asterisks, it prompts the user to select a password. For example:
>tmloadcf -y e:/wle5/samples/atmi/bankapp/xx
DBPASSWORD ("pool2" SRVGRP=GROUP1 SRVID=5):
After entering the password, tmloadcf stores the password in the TUXCONFIG file in encrypted form. If you use tmunloadcf to generate a UBBCONFIG file, the encrypted password entered is written into the DBPASSWORD statement in the UBBCONFIG file with @@ as delimiters. The following is a sample DBPASSWORD statement generated by tmunloadcf:
DBPASSWORD="@@A0986F7733D4@@"
When tmloadcf encounters an encrypted password in a UBBCONFIG file generated by using tmunloadcf, it does not prompt the user to create a password. Instead, the tmloadcf command uploads the encrypted password back into the system.
Note: The UBBCONFIG file with the encrypted form of the password may be uploaded back into the system only once; subsequent attempts will fail.
Use of encrypted passwords is only recommended for production environments. Clear-text passwords can be used during application development.
Displaying Information About JDBC Connection Pools
You can use the tmadmin printjdbcconnpool command to report statistics on JDBC connection pools. The data includes the maximum number of connections per pool, the number of connections in use, the number of clients waiting for a connection, and the high-water mark (HWM) or highest number of connections used for a pool.
Listing 2-2 shows the output produced by running the printjdbcconnpool command in terse and verbose modes. In terse mode the maximum pool size, the current pool size, and the number of connections currently in use are shown. In verbose mode the number of clients waiting and the high-water mark are also shown.
Listing 2-2 Sample Output from tmadmin printjdbcconnpool Command
>printjdbcconnpool
Pool Name Grp Name Srv Id Size Max Size Used
----------- ---------- ------ ---- -------- ----
ejbPool J_SRVGRP 101 1 15 0
Pool J_SRVGRP 102 10 30 3
The following is the verbose mode output for a single connection pool:
Pool Name: Pool2
Group ID: J_SRVGRP
Server ID: 102
Driver: (none)
URL: (none)
Database Name: Db
User: leia
Host: SITE1
Password: mypwd
Net Protocol: odbc
Port: 120
Props: (none)
Enable XA: No
Create On Startup: Yes
Pool Size: 10
Maximum Size: 30
Capacity increment: 3
Allow shrinking: Yes
Shrink interval: 10 min(s)
Login delay: 1 sec(s)
Connections in use: 3
Connections awaiting: 0
HWM connections in use: 5
Test table: testtable
Refresh interval: 20 sec(s)
Test conn OnReserve: Yes
Test conn OnRelease: No
For example if the high-water mark (HWM) of connections in use is at or close to the maximum size, or connections in use is close to the maximum size and clients are waiting for connections, then you may want to expand the maximum size of the pool. To do this, you must:
Note: Currently, the WebLogic Enterprise software does not support runtime changes to connection pools in running applications.
T_JDBCCONNPOOLS MIB Class
The BEA Tuxedo infrastructure supports WebLogic Enterprise features by providing new or enhanced TMIB classes. For JDBC connection pooling, this includes a new T_JDBCCONNPOOLS TMIB class. The values that you supply in the a WebLogic Enterprise application's UBBCONFIG file are stored in the TMIB classes. The properties defined in these classes are read by the WebLogic Enterprise Java server infrastructure (at boot time) to determine the defined behavior of the application, including the behavior of any connection pools.
System programmers can access the T_JDBCCONNPOOL class directly to administer WebLogic Enterprise applications, by using the currently supported TMIB access means. The T_JDBCCONNPOOL TMIB class is documented in Section 5 of the BEA Tuxedo Reference Manual. This document has been updated and is included in the WebLogic Enterprise online documentation.
API Characteristics
The WebLogic Enterprise connection pooling feature supports the full JDBC 2.0 Optional Package connection pooling subset, which consists of an application level API and a system level API for interacting with a JNDI Service Provider or other JDBC drivers.
Note: The JDBC 2.0 Optional Package was formerly known as the JDBC 2.0 Standard Extension API.
Application Level API
The JDBC 2.0 application level API provides interfaces for an application to obtain JDBC connections. In the JDBC 2.0 Optional Package, JDBC data sources are implemented by the application server. The data sources serve as JDBC connection factories, through which application users obtain JDBC connections.
The application level API consists of the following interfaces:
For JDBC drivers that are compliant with the JDBC 2.0 Optional Package API, the connection is obtained from the driver (which is a reference to the actual PooledConnection) and returned to the application directly. However, for a JDBC 1.x driver, the connection object returned to the application is implemented by the WebLogic Enterprise connection pooling module; the connection object is only a reference to the actual database connection returned by the underlying driver.
System Level API for the JNDI Service Provider
WebLogic Enterprise data sources also implement the following interfaces as an external contract to the WebLogic Enterprise local JNDI Service Provider so that the JNDI Service Provider can interact with it in a standard way:
System Level API for JDBC drivers
For JDBC 1.x drivers that do not directly support the JDBC 2.0 Optional Package API for connection pooling, the WebLogic Enterprise connection pooling facility provides JDBC 2.0 interface wrappers. Therefore, from the connection pooling module's perspective, it interacts with all drivers with the JDBC 2.0 Optional Package API protocol.
The interfaces supported on behalf of the JDBC 1.x drivers are:
The WebLogic Enterprise JDBC connection pooling module also supports the following interface as an external contract to the pooled connections of the JDBC drivers: javax.sql.ConnectionEventListener.
Obtaining Connections from a WebLogic Enterprise Connection Pool
A WebLogic Enterprise application completes the following steps to obtain a JDBC connection from the WebLogic Enterprise connection pool:
WebLogic Enterprise provides a local JNDI implementation for use within a WebLogic Enterprise JavaServer. Users specify the WebLogic Enterprise initial context factory as the initialization parameter when they get the JNDI initial context, as follows:
Context ctx = new InitialContext();
For the local WebLogic Enterprise JNDI service provider, you do not have to specify the initial context factory.
Data sources are registered in the JNDI namespace by WebLogic Enterprise JavaServers. The name by which it is registered is specified as one of the data source properties in the application's UBBCONFIG file. All JDBC data sources are registered in the "jdbc" JNDI naming subcontext of the JNDI root naming context. For example, a data source with the name "EmployeeDB" will be registered with the JNDI name "jdbc/EmployeeDB".
Assume an application needs to obtain a well-known data source called "jdbc/EmployeeDB" from JNDI. The application can get the JDBC connection from the data source, as shown in the following code fragment:
/*
* Assume that it has already obtained JNDI context as in
* previous step
*/
DataSource ds = (DataSource)ctx.lookup("jdbc/EmployeeDB");
Connection con = ds.getConnection();An Application's View of the Connection Lifecycle
The Connection object returned to the application is only a reference to the underlying database connection. The Connection object has the following lifecycle:
The DataSource implementation in WebLogic Enterprise has the following semantics:
public java.sql.Connection getConnection()
throws java.sql.SQLException
Application users use this getConnection method to obtain JDBC connections from the data source. Unlike the JDBC 1.0 DriverManager.getConnection API, you do not need to supply the username, password, and URL arguments. The relevant information is made available to the data source via the JDBC data source properties. Applications are responsible for ensuring that the sign-on information is available through appropriate data source properties. That is:
You can decide whether to wait for the connection if none is available, and how long to wait for it via the two optional JDBC data source properties:
If the property is not specified, by default getConnection will block until a connection is available. If no connection is available after the wait period is specified, an SQLException with be thrown, with a message indicating no connection is available.
public java.sql.Connection getConnection(
java.lang.String username,
java.lang.String password)
throws java.sql.SQLException
If the application uses this method to get a connection, the username and password specified in the arguments will be checked against the values specified in the corresponding JDBC data source properties (which are required for WebLogic Enterprise but not for standard JDBC). If the values match, it behaves the same as the previous method. Otherwise, a SQLException will be thrown.
public java.io.PrintWriter getLogWriter()
throws java.sql.SQLException
Returns the log writer for the data source.
public void setLogWriter(java.io.PrintWriter out)
throws java.sql.SQLException
The application sets the log writer for the data source using this API.
The WebLogic Enterprise connection pooling facility will intercept the log writer and write the logging information to ULOG as well if the Java server CLOPT option includes the following parameter: -jdbclog.
public void setLoginTimeout(int seconds) throws
java.sql.SQLException
Sets the maximum time in seconds that this data source will wait while attempting to connect to a database.
public int getLoginTimeout() throws java.sql.SQLException
Gets the maximum time in seconds that this data source can wait while attempting to connect to a database.
|
|
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|