®

IBM Software Group

Troubleshooting Database and

Connection Pooling Problems

Thu-Giang Pham

WebSphere® Support Technical Exchange

 IBM Software Group

Agenda

ƒ WebSphere Relational Resource Adapters (RRA)

ƒ Common Problems with RRA
ƒ WebSphere Connection Pooling
ƒ Common Problems with Connection Pooling

WebSphere® Support Technical Exchange 2

 IBM Software Group

Relational Resource Adapters

WebSphere® Support Technical Exchange 3

 IBM Software Group

Resource Adapter (RA)

ƒ System-level software driver that a Java

application uses to connect to an enterprise
information system (EIS)
ƒ Provides connectivity between the EIS, the
application server, and the enterprise application.

WebSphere® Support Technical Exchange 4

 IBM Software Group

Relational Resource Adapters (RRA)

ƒ Is JCA (J2EE Connector Architecture) v1.5 compliant and

shipped as a built-in of WebSphere Application Server
ƒ Implements the CCI (Common Connection Interface)
contracts for an outbound Resource Adapter as defined in the
JCA 1.0 or 1.5 specification
ƒ Supports the configuration and use of data sources
implemented as either JDBC data sources (JDBC 2.0 or
JDBC 2.0 optional package API or the JDBC 3.0 API) or
J2EE Connector Architecture connection factories

WebSphere® Support Technical Exchange 5

 IBM Software Group

Relational Resource Adapters (RRA)

ƒ Preinstalled to provide access to relational

databases
ƒ From Administrative Console:
Resources > Resource Adapters > WebSphere
Relational Resource Adapter

Note: Although the default relational resource

adapter settings are viewable, they cannot be
changed.

WebSphere® Support Technical Exchange 6

 IBM Software Group

JDBC Providers
ƒ Installed applications use JDBC providers to interact with
databases.
ƒ The JDBC provider object supplies the specific JDBC driver
implementation class for access to a specific vendor
database. To create a pool of connections to that database,
you associate a data source with the JDBC provider.
ƒ JDBC provider and the data source objects are functionally
equivalent to the J2EE Connector Architecture (JCA)
connection factory, which provides connectivity with a non-
relational database.

WebSphere® Support Technical Exchange 7

 IBM Software Group

Supported Database and JDBC Drivers

ƒ List current list of supported databases and JDBC

drivers for specific version of WebSphere can be
found at:
http://www-
1.ibm.com/support/docview.wss?rs=180&uid=swg2
7006921

WebSphere® Support Technical Exchange 8

 IBM Software Group

JDBC Driver’s Configuration

ƒ From Administrative Console:
Resources > JDBC Providers
ƒ Class path - list of paths or JAR file names which together
form the location for the resource provider classes.
ƒ Native Library Path - list of paths that form the location for the
resource provider native libraries.
For type 2 JDBC drivers only (e.g. Oracle OCI, DB2
Universal JDBC Type 2, DB2 Legacy CLI-based).
ƒ Implementation class name
Java class name of the JDBC driver implementation.
This class is available in the driver file mentioned in the
class path description above.

WebSphere® Support Technical Exchange 9

 IBM Software Group

Unsupported JDBC Driver

ƒ The JDBC Driver must implement two data source interfaces
defined by Sun Microsystems for working with connection
pooling
ConnectionPoolDataSource - a data source that
supports application participation in local and global
transactions, excepting two-phase commit transactions
XADataSource - a data source that supports application
participation in any single-phase or two-phase
transaction environment. When this data source is
involved in a global transaction, the WebSphere
Application Server transaction manager provides
transaction recovery.

WebSphere® Support Technical Exchange 10

 IBM Software Group

Unsupported JDBC Driver

ƒ Use User-defined JDBC Provider

ƒ The Implementation class name is the class that
implements the interface
ConnectionPoolDataSource
For example: The implementation class name
for i-net OPTA JDBC Driver
is com.inet.tds.XDataSource.

WebSphere® Support Technical Exchange 11

 IBM Software Group

Data Source
ƒ Is used to obtain connections to a relational
database.
ƒ Is analogous to the J2EE Connector Architecture
(JCA) connection factory, which provides
connectivity to other types of enterprise information
systems (EIS).
ƒ Is associated with a JDBC provider, which supplies
the driver implementation classes that are required
for JDBC connectivity with specific database.
ƒ Multiple data sources with different settings can
associate them with the same JDBC provider

WebSphere® Support Technical Exchange 12

 IBM Software Group

Connection Manager Architectures

ƒ In WSAS releases prior to version 5.0, the function of data
access was provided by a single connection manager (CM)
architecture. This connection manager architecture remains
available to support J2EE 1.2 applications.
ƒ New connection manager architecture is provided, based on
the JCA architecture supporting the new J2EE 1.3 application
style (also for J2EE 1.4 applications).
ƒ These two separate architectures are represented by two
types of data sources. To choose the right data source,
administrators must understand the nature of their
applications, EJB modules, and enterprise beans.

WebSphere® Support Technical Exchange 13

 IBM Software Group

Choice of Data Source

ƒ J2EE 1.2 application - all EJB 1.1 enterprise beans, JDBC
applications, or Servlet 2.2 components must use the 4.0
data source.
ƒ J2EE 1.3 (and subsequent releases) application -
EJB 1.1 Module - all EJB 1.x beans must use the 4.0
data source.
EJB 2.0 (and subsequent releases) Module - enterprise
beans that include container-managed persistence
(CMP) Version 1.x, 2.0, and beyond must use the new
data source.
JDBC applications and Servlet 2.3+ components - must
use the new data source.

WebSphere® Support Technical Exchange 14

 IBM Software Group

Creating Data Source

ƒ From Administrative Console:

Resources > JDBC Providers
ƒ Choose the JDBC Provider
ƒ Under Additional Properties, click the Data
Sources link that is appropriate for your application
ƒ Customer properties can be defined or added to
the data source

WebSphere® Support Technical Exchange 15

 IBM Software Group

Configuration Requirement

ƒ Check Information Center section “Data source

minimum required settings, by vendor” at URL:

http://publib.boulder.ibm.com/infocenter/wasinfo/v6
r0/topic/com.ibm.websphere.base.doc/info/aes/ae/r
dat_minreq.html

and click on the link Detailed Requirements for

creating data sources.

WebSphere® Support Technical Exchange 16

 IBM Software Group

Invalid Custom Property

ƒ Check SystemOut.log for DSRA message. For example:

DSRA8020E: Warning: The property

'SetBigStringTryClob' does not exist on the
DataSource class
oracle.jdbc.pool.OracleConnectionPoolDataSour
ce

This error indicates that custom property

‘SetBigStringTryClob’ is not valid to Oracle JDBC Driver.

WebSphere® Support Technical Exchange 17

 IBM Software Group

Tip for Using Oracle JDBC Driver

ƒ To specify connection properties for Oracle data
source, create custom property with Name/Value
pair:
Name: connectionProperties
Value: oracle.jdbc.V8Compatible=true

ƒ Multiple connection properties can by specified

using “;” (semicolon) as delimiter. For example:
Value:
oracle.jdbc.V8Compatible=true;oracle.jdbc.RetainV
9LongBindBehavior=true

WebSphere® Support Technical Exchange 18

 IBM Software Group

Using Type 2 JDBC Driver

• Must set all required environment variables

required by the JDBC Driver.
• For Oracle
• ORACLE_HOME
• LD_LIBRARY_PATH (Native library path)
• For DB2
• DB2INSTANCE
• LD_LIBRARY_PATH (Native library path)

WebSphere® Support Technical Exchange 19

 IBM Software Group

Common Configuration Problems

WebSphere® Support Technical Exchange 20

 IBM Software Group

Common Class Loading Exceptions

ƒ ClassCastException
ƒ ClassNotFoundException
ƒ NoClassDefFoundException
ƒ UnsatisfiedLinkError
ƒ See Information Center, section Class loading
exceptions for explanations and troubleshooting
tips.

WebSphere® Support Technical Exchange 21

 IBM Software Group

ClassNotFoundException

ƒ Verify that the class path is correct and the JAR

file(s) for the JDBC provider exist and have access
permission on the server where the application with
the associated data source is running.
ƒ If the definition of the data source includes a
WebSphere variable, make sure that the variable is
defined and set at a correct scope. From the
Administrative console:
Environment > WebSphere Variables

WebSphere® Support Technical Exchange 22

 IBM Software Group

ClassCastException

ƒ Check to make sure that there is only one copy of

each JAR file that is required for the JDBC
provider. Search the file system starting from the
<WAS_install_root> directory for duplication.
ƒ Make sure the application’s EAR file does not
include the JDBC driver’s JAR files.

WebSphere® Support Technical Exchange 23

 IBM Software Group

UnsatisfiedLinkError

ƒ The JVM throws UnsatisfiedLinkError when it

cannot find the native library or when the JVM has
already loaded the native library.
ƒ This exception only occurs when you are using
type 2 JDBC drivers (for example, DB2 CLI
Legacy, DB2 Universal Type 2, and Oracle OCI
drivers).
ƒ Note: Type 2 JDBC drivers require that some
binary code is loaded on each client machine
(WebSphere Application Server machine).

WebSphere® Support Technical Exchange 24

 IBM Software Group

UnsatisfiedLinkError
ƒ If there is no value specified for the native library path in your JDBC
Provider’s configuration, you might get the UnsatisfiedLinkError:
 On Windows platforms, set the system environment PATH to include
the path where the *.dll files can be loaded.
 On UNIX platforms, you might need to set up the user’s profile to set
the environment variables before starting the WebSphere Application
Server processes.
ƒ 32-bit versus 64-bit native libraries
 32-bit WebSphere Application Server, must use 32-bit JDBC Driver
 64-bit WebSphere Application Server, must use 64-bit JDBC Driver

ƒ Examine SystemOut.log for the UnsatisfiedLink error, then review the

message text for native library file name (see speaker note for example)

WebSphere® Support Technical Exchange 25

 IBM Software Group

Limitation and Known Problem

ƒ Currently, only one version of JDBC Driver can be

used within an application server.
ƒ Do not bundle the JDBC driver’s jar files within
applications
ƒ Oracle 10g is supported officially in WebSphere
Application Server V6.
ƒ If using the Oracle 10g JDBC driver, data source
must use the data store helper class
com.ibm.websphere.rsadapter.Oracle10g
DataStoreHelper

WebSphere® Support Technical Exchange 26

 IBM Software Group

Checking JDBC Driver Version

ƒ Some JDBC Driver provides a main class which can be
executed from java command to get the version. For example
DB2 Universal JDBC Driver
java com.ibm.db2.jcc.DB2Jcc –version

WebSphere embed Connect JDBC

java com.ibm.websphere.jdbc.sqlserver.SQLServerDriver
ƒ Can use the JDBC DatabaseMetaData object to get the
version information. For example:
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
Connection con =
DriverManager.getConnection(url, user, password);
DatabaseMetaData dmd = con.getMetaData();
System.out.println("JDBC Driver version: " +
dmd.getDriverVersion());

WebSphere® Support Technical Exchange 27

 IBM Software Group

Tool to Use for Class Loader Problem

ƒ Class Loader Viewer
From Administrative Console
Troubleshooting > Class Loader Viewer
Use the “Search” function to search class loaders for the
following:
• Specific strings
• Specific .jar files
• The names of files in a specific directory
• The names of files loaded by a specific class loader

WebSphere® Support Technical Exchange 28

 IBM Software Group

Connection Pooling

WebSphere® Support Technical Exchange 29

 IBM Software Group

J2C Component
ƒ Is the WSAS implementation of JCA 1.5.
ƒ JCA 1.5
provides a standardized way (Outbound Contracts) for a
J2EE container to provide Connection Management
services, such as Connection pooling, Transaction
Management, and Security Management. Thus any JCA
1.5 compliant Resource Adapter should be able to
interoperate with WSAS.
defines system level contracts (Inbound Contracts) for a
message provider to connect to endpoints (i.e. message-
driven bean applications) in WSAS via a resource
adapter.
defines a contract for the application server to provide
thread management services for resource adapters.

WebSphere® Support Technical Exchange 30

 IBM Software Group

Interaction with other WSAS Components

ƒ J2C calls to:
 Transaction Manager
 Security
 Resource Adapters
ƒ J2C is called by:
 Resource Adapters
 System Management (startRA, etc…)
 JNDI (resource lookup)
ƒ Dependencies on security component
 Main Security dependency is on getSubject method which is called
during allocateConnection to obtain credentials for Container-managed
authentication case.
 J2C also calls utility methods to extract credential information from a
Subject for Connection matching.

WebSphere® Support Technical Exchange 31

 IBM Software Group

Connection Management (CM)

ƒ The CM architecture for both relational and procedural
access to enterprise information systems (EIS)
ƒ Based on the J2EE Connector Architecture (JCA)
specification.
ƒ Pools and manages connections within an application server
ƒ Manages connections obtained through both resource
adapters (RAs) defined by the JCA specification, and data
sources defined by the Java Database Connectivity (JDBC)
2.0 (and later) Extensions specification.
ƒ To make data source connections manageable by the CM,
the WSAS provides a relational resource adapter
(WebSphere RRA) that enables JDBC data sources to be
managed by the same CM that manages JCA connections.

WebSphere® Support Technical Exchange 32

 IBM Software Group

Common Problems

WebSphere® Support Technical Exchange 33

 IBM Software Group

ConnectionWaitTimeoutException

ƒ Can occur due to the pool over use

ƒ Review connection pool:
The maximum number of connections for a
given pool is set too low.
Connection timeout is set too low.
Application does not close some connections
(connection leak), or it returns connections
back to the pool at a very slow rate.

WebSphere® Support Technical Exchange 34

 IBM Software Group

ConnectionWaitTimeoutException
ƒ Can occur when servlets are migrated from
v2.2 to 2.3
See technote “Servlet connection to
database fails after migrating from
WebSphere Application Server V4 to V5 and
later versions” for explanation:
http://www-
1.ibm.com/support/docview.wss?&uid=swg
21192666

WebSphere® Support Technical Exchange 35

 IBM Software Group

Connection Leak
ƒ A connection leak occurs when the application uses a
connection but it never explicitly calls the close() method on
the connection object to return the connection back to the
connection pool for reuse.
ƒ You might have a connection leak if your application receives
exceptions such as
com.ibm.websphere.ce.cm.ConnectionWaitTimeoutExce
ption or
com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutExce
ption when attempting to access a data source or JCA-
compliant resource adapter, respectively.

WebSphere® Support Technical Exchange 36

 IBM Software Group

Troubleshoot Connection Leak

ƒ Trace Specification
ConnLeakLogic=all
ƒ Traces details such as the getConnection call stack
for connections that have been in use for a long
time (10 seconds).
ƒ Included in ‘WAS.j2c’ trace.
ƒ ConnLeakLogic may be used alone, but will only
generate output during FFDC processing on a
ConnectionWaitTimeout scenario

WebSphere® Support Technical Exchange 37

 IBM Software Group

Many Connections to Backend Resource

ƒ A separate connection pool exists for each configured data
source or connection factory.
ƒ Application Server creates a separate instance of the
connection pool in each application server that uses the data
source or connection factory.
For example: In three server cluster in which all of the
servers use myDS, and myDS has a max connections
setting of 10, then you would generate up to 30
connections to the backend resource.
ƒ Each entity bean transaction requires an additional database
connection, dedicated to handling the transaction.

WebSphere® Support Technical Exchange 38

 IBM Software Group

Deadlock
ƒ Application requires more than one concurrent connection
per thread, and the database connection pool is not large
enough for the number of threads.
ƒ Troubleshooting
Collect Javacores/Javadumps (see MustGather
document for the corresponding version and operating
system)
Use IBM Thread and Monitor Dump Analyzer for Java
Java Technology
ƒ Solution
Increase the Maximum Connections value for the
database connection pool by at least one.

WebSphere® Support Technical Exchange 39

 IBM Software Group

High Number of Connection Handles

ƒ Unshareable means that there is a 1:1 relationship
between the connection handle and physical
connection
ƒ Shareable allows for greater scalability. There is a
n:1 relationship between connection handle and
physical connection (managed connection)
The maximum number of connection handles
can’t be defined. That means it is unlimited
depending on system resource. The JVM
heapsize may run out

WebSphere® Support Technical Exchange 40

 IBM Software Group

Authentication data configuration

ƒ Missing J2C Authentication Alias on the data source
ƒ Application versus Container res-auth
ƒ APAR PK03287 "Dynamically update J2C authentication data
at run time”
WebSphere security mBean provide a method for user to
dynamically push new authentication data to run time.
So new authentication data takes effect without
restarting servers.
The wsadmin command:
$AdminControl invoke $SecurityAdmin
updateAuthDataCfg null

WebSphere® Support Technical Exchange 41

 IBM Software Group

Programming model
ƒ Improper caching of connection handles
ƒ Direct JNDI lookup of connection factory or data source
J2CA0122I: Resource reference {0} could not be
located, so the following default values are used: {1}
ƒ Spinning threads from a resource adapter
ƒ LTC scope issues
Multiple connections
Connections reserved for long periods
See technote 1217062 and developerWorks article
Default behavior of managed connections in WebSphere
Application Server (See link on References slide)

WebSphere® Support Technical Exchange 42

 IBM Software Group

StaleConnectionExceptions

ƒ Indicates that the connection currently held is no

longer valid.
ƒ See Stale connections section in the WebSphere
Information Center for information about how to
detect stale connections and how to recover from
stale connections
ƒ Check timeout setting on the connection pool
ƒ Determine if a specific query is getting the
exception
ƒ Check for potential network problem

WebSphere® Support Technical Exchange 43

 IBM Software Group

Statement Cache
ƒ Application calls prepareStatement on its
Connection handle. RRA in this example finds the
statement in the statement cache. A wrapper is
created for the PreparedStatement object, attached
to the Connection wrapper, and the new wrapper is
returned to the app
ƒ If same prepared statement works the first time
and fails on subsequent time, there is a chance
that the statement cache is corrupt – try disabling
the cache by setting the statement cache size to
zero

WebSphere® Support Technical Exchange 44

 IBM Software Group

Debug Procedure for CM

ƒ Start by searching the SystemOut.log files for J2CAxxxx
messages.
ƒ If traces are needed, enable them on the affected
server and recreate the problem.
ƒ If RA initialization is important to the problem
Make sure the trace includes the startRA
timeframe.
ƒ If ConnectionFactory initialization is important to the
problem
Make sure the trace includes the getObjectInstance
timeframe.

WebSphere® Support Technical Exchange 45

 IBM Software Group

Debug Procedure for CM

ƒ Use collector tool to gather important files
ƒ Configuration files of interest:
resources.xml – for ConnectionFactory/DataSource
configuration. Make sure to get the one for the
corresponding scope
ra.xml – for RA configuration
ibm-ejb-bnd.xmi or ibm-web-bnd.xmi – for resource
reference data
ejb-jar.xml or web.xml – for res-auth value

WebSphere® Support Technical Exchange 46

 IBM Software Group

Trace Specifications

ƒ J2C/Connection Pooling problem

WAS.j2c=all
ƒ RRA problem
RRA=all
ƒ JDBC Driver
WAS.database=all
Note: Need the debug jar file (e.g.
ojdbc14_g.jar for Oracle JDBC Driver)

WebSphere® Support Technical Exchange 47

 IBM Software Group

Trace Analysis
ƒ Find primary error (J2CAxxxx or RA-specific message).
ƒ Identify J2C, RA, and/or Transaction objects and the WSAS
thread involved.
ƒ Follow progress of thread leading up to the problem.
ƒ Follow history of significant J2C, RA, and/or Transaction
objects leading up to the problem.
ƒ Identify abnormal events or configuration data that would lead
to the problem.
ƒ Decide whether the cause lies with configuration, application,
WSAS, or RA.

WebSphere® Support Technical Exchange 48

 IBM Software Group

Summary

ƒ Common problems and troubleshooting

configurations of JDBC Provider and data sources
ƒ Common problems and troubleshooting connection
pooling

WebSphere® Support Technical Exchange 49

 IBM Software Group

References

ƒ Sharing connections in WebSphere Application

Server V5
ƒ Database authentication in WebSphere Application
Server V5
ƒ Default behavior of managed connections in
WebSphere Application Server
ƒ WebSphere Application Server V6: JCA
Connection Problem Determination

WebSphere® Support Technical Exchange 50

 IBM Software Group

Additional WebSphere Product Resources

ƒ Discover the latest trends in WebSphere Technology and implementation,
participate in technically-focused briefings, webcasts and podcasts at:
www.ibm.com/developerworks/websphere/community/
ƒ Learn about other upcoming webcasts, conferences and events:
www.ibm.com/software/websphere/events_1.html
ƒ Join the Global WebSphere User Group Community: www.websphere.org
ƒ Access key product show-me demos and tutorials by visiting IBM Education
Assistant: www.ibm.com/software/info/education/assistant
ƒ Learn about the Electronic Service Request (ESR) tool for submitting
problems electronically:
www.ibm.com/software/support/viewlet/ESR_Overview_viewlet_swf.html
ƒ Sign up to receive weekly technical My support emails:
www.ibm.com/software/support/einfo.html

WebSphere® Support Technical Exchange 51

 IBM Software Group

Questions and Answers

WebSphere® Support Technical Exchange 52