Investigate JDBC Problems (Continued)

Stay Fresh
This problem has been addressed in later WebLogic Server versions (6.1 SP7, 7.0 SP3, and 8.1 SP1 or later). If JMS is idle, the database is pinged every five minutes to keep the connection fresh and prevent the firewall from closing these connections.

If getVendorConnection() is used to receive the underlying physical connection for a JDBC pool connection, the RemoveInfectedConnectionsEnabled property's setting needs to be checked.

Some advanced JDBC commands require the physical connection as an argument. To be able to do this, getVendorConnection() can be called to get the physical connection used by the connection pool. However, the application code that uses the physical connection needs to be implemented carefully to avoid follow-up problems. As the JDBC connection pool is implemented to ensure as much security and availability as possible, any connection for which getVendorConnection() was called is refreshed automatically after the application's usage (RemoveInfectedConnectionsEnabled="true").

Thus, connection pooling loses its effect, that is, every connection is closed and reopened after usage. Consider carefully if the application code changes or destroys something on the physical connection that makes a reopen necessary. If and only if this effect is not the case, set the RemoveInfectedConnectionsEnabled property to false (see Resources for more information).

As type-2 JDBC drivers use native code, problems in these drivers may lead to a crash of the JVM and WebLogic Server. If your server crashes, check information provided in the BEA support pattern, "Binary Core File Analysis Pattern" (see Resources). This information will help you to track down the native library that causes the crash and provides tips on how to solve the problem.

A JDBC connection uses one execute thread from WebLogic Server to perform its work, which means that a hanging request to a database blocks one thread in WebLogic Server. Problems with the JDBC connection or the database infrastructure can lead to a hang in WebLogic Server or the application.

Information on analyzing this kind of problem is available in a BEA support pattern, "JDBC Causes Server Hang" (see Resources). General information on how to track down a hang situation in WebLogic Server is already available in the BEA support pattern, "Generic Server Hang Pattern."

A memory leak of JDBC objects leads to an OutOfMemoryError or growing process size. JDBC objects used by connections from the JDBC connection pool or in application code—that connect directly to a database—are part of the heap or native memory of the process. If these objects are not closed and freed correctly, a memory leak is the consequence. It will cause increased heap usage or growing process size, and it will lead to an OutOfMemoryError after the JVM or the operating system cannot provide any free memory anymore.

If you see OutOfMemoryErrors in your system and suspect JDBC objects to be the cause, please check the "Investigating Out Of Memory/Memory Leak Problems" pattern, which provides general information on how to analyze memory leak problems. If you detected connections-leak error messages in your WebLogic Server log file or through JDBC pool monitoring in your administration console, the BEA support pattern "Investigating JDBC Connection Leaks" provides troubleshooting for this issue. This pattern is being developed at the time of this writing. A link will be provided on the BEA support patterns page as soon as it is available.

Flagging Issues
JDBC debugging and tracing sometimes is key for finding out what is going on and analyzing the SQL statements that are sent to the database. However, JDBC is a multilayered subsystem, of which only parts are inside WebLogic Server. Debugging and tracing of the JDBC driver layer is highly driver-dependent. Information regarding debug and trace flags for the drivers is available from the driver vendors. At the WebLogic Server side, different JDBC debug flags are available.

jDriver JDBC tracing can be switched on through the WebLogic Server administration console, or in the config.xml file directly by setting <JDBCLoggingEnabled> in the Server tag (see Resources for more information). The ServerDebugMBean has some JDBC-related flags that can be turned on in the config.xml file. Put a new <ServerDebug> tag into the <Server> tag from the WebLogic Server instance you would like to debug:

<Server Name="myserver" >
  <ServerDebug Name="myserver" 
    JDBCConn="true" JDBCSQL="true" 
    JTAJDBC="true" />
</Server>

Alternatively, these debug flags can also be set as system properties during WebLogic Server startup:

-Dweblogic.Debug=weblogic.
  JDBCConn,weblogic.JDBCSQL,
  weblogic.JTAJDBC

These debug flags and tracing can be very verbose, so consider very carefully where you turn on those flags. They will create a lot of output and also possibly have a performance impact on your system. They should not be turned on in production systems.

For drivers that do not allow turning on JDBC debugging or do not print enough information, installing a spy driver can help to debug all SQL statements between the JDBC driver and database. This driver implements an additional layer that prints debug information into a log file. You can download the P6Spy driver at its Web site, where you will also find related documentation (see Resources).

WebLogic Server multipools can be used for high availability or load balancing purposes. Depending on the algorithm, the multipool behavior is different. The "Investigating JDBC Multipool Issues" pattern assists with this topic (see Resources).

You can tune JDBC connection pools for production environments. Configuration of JDBC connection pools for production systems is a critical and important task to ensure stability and performance. The general recommendations in Table 1 may help as a starting point for administrators.

About the Author
Maria Salzberger is employed in customer support with BEA where she supports WebLogic Server and WebLogic Integration, primarily JDBC and transactions, EJBs, core server performance, and stability. Maria is also a contributor to the BEA Support Pattern initiative.