Hi; this is my first post, and i apprecciate a lot your help. We have a Tomcat Application Server 4.1.30, Sun JVM 1.4.2_05 in W2K server (512 ram) connected to a DB2 6.X resident in a IBM/390.
The web application performs several access to the DB2 for each user. We have normally
200 connections to the database. The application acesses lots of BLOB registries in those connections.
We use a connection pool of 400 connections at maximum.
It performs well most of the time. But sometimes when the load is heavy (it is happening more or less each week),
like 20 users at a time, Tomcat does not show any error but leaves a lock in the BLOB table without commit for
hours, we realize there is a problem when we see time out errors in the logs of tomcat.
What happens next is that there is no way to release that lock on the BLOB table, even
if we shutdown the w2k server the lock remains in the DB2.
The only way is to kill the DB2 thread in the DB2 server in the IBM/390 or bring down DDF. After that, the web application
can access the blob registry that was unaccessible without restarting tomcat.
We have been unable to repeat the problem in the development site. Even on the same machine.
The server is not full in cpu processor, nor the database has any problem (apparently). Some
other applications use the database without problem.
I would greatly appreciatte somebody here can give me a hint about any of this. This is a critical application for us.
My manager is even thinking of replacing Tomcat with WAS 5.0 from IBM.
But i dont think that would solve the problem.
this is my server.xml: (PSDRS7B is production, DB2TEST is development)
thank you for reading, i know is too long.
-------------------------------------------------------------------------------------------------------------------------
<?xml version='1.0' encoding='utf-8'?>
<Server className="org.apache.catalina.core.StandardServer" debug="0" port="8005" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" debug="0" jsr77Names="false"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" debug="0"/>
<GlobalNamingResources>
<Resource auth="Container" description="User database that can be updated and saved" name="UserDatabase" scope="Shareable" type="org.apache.catalina.UserDatabase"/>
<Resource name="PSDRS7B" scope="Shareable" type="javax.sql.DataSource"/>
<Resource name="DB2TEST" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="UserDatabase">
<parameter>
<name>factory</name>
<value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
</parameter>
<parameter>
<name>pathname</name>
<value>conf/tomcat-users.xml</value>
</parameter>
</ResourceParams>
<ResourceParams name="PSDRS7B">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:odbc:PSDRS7B</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>SELECT CODIGOSOLUCION FROM PSDRS7B.RESPUEST</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>200</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>500</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>sun.jdbc.odbc.JdbcOdbcDriver</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>username</name>
<value>VM6GSR2</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>10</value>
</parameter>
<parameter>
<name>password</name>
<value>WEBF1RE</value>
</parameter>
</ResourceParams>
<ResourceParams name="DB2TEST">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>SELECT CODIGOSOLUCION FROM TSDRS7B.RESPUEST</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>500</value>
</parameter>
<parameter>
<name>password</name>
<value>ESTH3R</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:odbc:DB2TEST</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>sun.jdbc.odbc.JdbcOdbcDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>200</value>
</parameter>
<parameter>
<name>username</name>
<value>VM6GLC1</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>10</value>
</parameter>
</ResourceParams>
</GlobalNamingResources>
<Service className="org.apache.catalina.core.StandardService" debug="0" name="Tomcat-Standalone">
<Connector className="org.apache.coyote.tomcat4.CoyoteConnector" acceptCount="50" bufferSize="10240" compression="on" connectionLinger="-1" connectionTimeout="60000" connectionUploadTimeout="300000" debug="0" disableUploadTimeout="true" enableLookups="false" maxKeepAliveRequests="100" maxProcessors="75" minProcessors="50" port="80" proxyPort="0" scheme="http" secure="false" serverSocketTimeout="0" tcpNoDelay="true" tomcatAuthentication="true" useBodyEncodingForURI="true" useURIValidationHack="false">
<Factory className="org.apache.catalina.net.DefaultServerSocketFactory"/>
</Connector>
<Engine className="org.apache.catalina.core.StandardEngine" debug="0" defaultHost="localhost" mapperClass="org.apache.catalina.core.StandardEngineMapper" name="Standalone">
<Host className="org.apache.catalina.core.StandardHost" appBase="webapps" autoDeploy="true" configClass="org.apache.catalina.startup.ContextConfig" contextClass="org.apache.catalina.core.StandardContext" debug="0" deployXML="true" errorReportValveClass="org.apache.catalina.valves.ErrorReportValve" liveDeploy="true" mapperClass="org.apache.catalina.core.StandardHostMapper" name="localhost" unpackWARs="true">
<Valve className="org.apache.catalina.valves.AccessLogValve"
directory="logs" prefix="localhost_access_log." suffix=".txt"
pattern="common"/>
<Context className="org.apache.catalina.core.StandardContext" cachingAllowed="true" charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true" crossContext="false" debug="0" displayName="Tomcat Administration Application" docBase="../server/webapps/admin" mapperClass="org.apache.catalina.core.StandardContextMapper" path="/admin" privileged="true" reloadable="false" swallowOutput="false" useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
<Logger className="org.apache.catalina.logger.FileLogger" debug="0" directory="logs" prefix="localhost_admin_log." suffix=".txt" timestamp="true" verbosity="1"/>
</Context>
<Context className="org.apache.catalina.core.StandardContext" cachingAllowed="true" charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true" crossContext="false" debug="0" docBase="D:\Program Files\Apache Group\Tomcat 4.1\webapps\desarrollo" mapperClass="org.apache.catalina.core.StandardContextMapper" path="/desarrollo" privileged="false" reloadable="false" swallowOutput="true" useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
<ResourceLink global="PSDRS7B" name="PSDRS7B" type="javax.sql.Datasource"/>
<ResourceLink global="DB2TEST" name="DB2TEST" type="javax.sql.DataSource"/>
</Context>
<Context className="org.apache.catalina.core.StandardContext" cachingAllowed="true" charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true" crossContext="false" debug="0" docBase="D:\Program Files\Apache Group\Tomcat 4.1\webapps\ROOT" mapperClass="org.apache.catalina.core.StandardContextMapper" path="" privileged="false" reloadable="false" swallowOutput="true" useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
<Logger className="org.apache.catalina.logger.FileLogger" debug="0" directory="logs" prefix="localhost_root_log." suffix=".txt" timestamp="true" verbosity="1"/>
<ResourceLink global="PSDRS7B" name="PSDRS7B" type="javax.sql.DataSource"/>
</Context>
<Context className="org.apache.catalina.core.StandardContext" cachingAllowed="true" charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true" crossContext="false" debug="0" displayName="Tomcat Manager Application" docBase="../server/webapps/manager" mapperClass="org.apache.catalina.core.StandardContextMapper" path="/manager" privileged="true" reloadable="false" swallowOutput="false" useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
<ResourceLink global="UserDatabase" name="users" type="org.apache.catalina.UserDatabase"/>
</Context>
<Logger className="org.apache.catalina.logger.FileLogger" debug="0" directory="logs" prefix="localhost_log." suffix=".txt" timestamp="true" verbosity="1"/>
</Host>
<Logger className="org.apache.catalina.logger.FileLogger" debug="0" directory="logs" prefix="catalina_log." suffix=".txt" timestamp="true" verbosity="1"/>
<Realm className="org.apache.catalina.realm.UserDatabaseRealm" debug="0" resourceName="UserDatabase" validate="true"/>
</Engine>
</Service>
</Server>
-------------------------------------------------------------------------------------------------------------------------
this is my web.xml: ------------------------------------------------------------------------------------------------------------------------- <?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd";>
<web-app>
<servlet>
<servlet-name>ConsultaS7B</servlet-name>
<display-name>ConsultaS7B</display-name>
<description>ConsultaS7B</description>
<servlet-class>rservlets.ConsultaS7B</servlet-class>
</servlet>
<servlet>
(like 100 servlets....)
</servlet>
<servlet-mapping>
<servlet-name>ConsultaS7B</servlet-name>
<url-pattern>/servlet/rservlets.ConsultaS7B</url-pattern>
</servlet-mapping>
<servlet-mapping>
(the same 100 servlets ...)
</servlet-mapping>
<session-config>
<session-timeout>
3600
</session-timeout>
</session-config>
<mime-mapping>
<extension>snp</extension>
<mime-type>www/unknown</mime-type>
</mime-mapping>
<welcome-file-list>
<welcome-file>
login.html
</welcome-file>
</welcome-file-list>
</web-app>
-------------------------------------------------------------------------------------------------------------------------this the java file that access the database:
------------------------------------------------------------------------------------------------------------------------- package rhost;
/**
* Clase que contiene la lógica para la conexión y solicitud de información
* al IBM/390 y JDBC
*/
import javax.naming.*;
import javax.sql.*;
import java.io.*;
import java.util.*;
import java.sql.*;
public class ConectaBD{
//Conexión a la Base de Datos
private String fieldContextDB = null;
private Connection fieldConexionDB = null;
private String fieldDriverDB = null;
private String fieldUrlDB = null;
private String fieldUsuarioDB = null;
private String fieldClaveDB = null;
private boolean bMuestraQueryDB = false;
private boolean bOldConnection = false;
public boolean bOldHistran = false; public boolean bUseConcilia = false; public boolean bOnlinePOS = false;
public boolean bOnlineATM = false;
int banco = 0;
// Variables del Log
public final int CREAR_LOG = 0;
public final int USAR_LOG = 1;
// Variable para conectar por JDBC o por ConnManager
//private final boolean USA_JDBC = true;
// Variables varias
//public static boolean DEBUG = true;
Properties propiedades = new Properties();
/**
* Constructor de la Clase ConectaTR
*/
public ConectaBD() {
super();
initVariables();
}
/**
* Método que devuelve el ResultSet con las filas, dado un PreparedStatement
* @return java.sql.ResultSet
* @param ps java.sql.PreparedStatement
*/
protected java.sql.ResultSet getResultSetQ(PreparedStatement ps) throws java.sql.SQLException {
ResultSet rs = null;
rs = ps.executeQuery();
return rs;
}
/**
* Método que obtiene el resultado de una sentencia SQL
* @return java.sql.PreparedStatement
* @param sentencia java.lang.String
* @param numtr java.lang.Long
*/
protected java.sql.ResultSet getResultSetQ(PreparedStatement ps, long numtr) throws java.sql.SQLException {
ResultSet rs = null;
ps.setLong(1, numtr);
rs = ps.executeQuery();
return rs;
}
/**
* Método que actualiza la base de datos a partir de una sentencia SQL, inserta la trama y un long, que es el número de trace
* @return java.sql.ResultSet
* @param sentencia java.lang.String
* @param trama java.lang.String
* @param trace java.lang.Long
*/
protected void getResultSetU(String sentencia) throws java.sql.SQLException {
PreparedStatement ps = null;
try {
if (bMuestraQueryDB) {
hazLog(sentencia);
}
ps = fieldConexionDB.prepareStatement(sentencia);
ps.executeUpdate();
ps.close();
ps = null;
} catch (Exception e) {
if (ps != null) {
try { ps.close(); } catch (SQLException e2) { ; }
ps = null;
} throw new SQLException("Error: getResultSetU - "+e);
}
}
/**
* Método que actualiza la base de datos a partir de una sentencia SQL y un long, que es el número de trace
* @return void
* @param sentencia java.lang.String
* @param numtr java.lang.Long
*/
protected int getResultSetU(String sentencia, long trace) throws java.sql.SQLException {
PreparedStatement ps = null;
int estado = -1;
try {
if (bMuestraQueryDB) {
hazLog(sentencia);
}
ps = fieldConexionDB.prepareStatement(sentencia);
ps.setLong(1, trace);
estado = ps.executeUpdate();
ps.close();
ps = null;
} catch (Exception e) {
if (ps != null) {
try { ps.close(); } catch (SQLException e2) { ; }
ps = null;
} throw new SQLException("Error: getResultSetU - "+e); }
return estado;
}
/**
* Método que devuelve el PreparedStatement de una sentencia SQL
* @return java.sql.PreparedStatement
* @param sentencia java.lang.String
*/
public java.sql.PreparedStatement getStatement(String sentencia) throws java.sql.SQLException {
if (bMuestraQueryDB) {
hazLog(sentencia);
}
try {
return fieldConexionDB.prepareStatement(sentencia);
} catch (Exception e){
System.out.println("getStatement: "+e);
return null;
}
}
/**
* Método para inicializar variables. Este método se encarga de inicializar las
* variables que se encuentra en el archivo de propiedades, Banco.prs
* Consiste de las siguientes zonas de lectura:
* Propiedades para la Conexión a la Base de Datos del AS/400
*/
private void initVariables() {
/* Desarrollo del método initVariables. */
try {
java.io.InputStream variables = this.getClass().getResourceAsStream("/suiche7b.properties");
propiedades.load(variables);
if (bMuestraQueryDB == true)
System.out.println("Procesado el Archivo de Propiedades");
// Para la Base de Datos
fieldContextDB = propiedades.getProperty("ContextDB");
fieldDriverDB = propiedades.getProperty("DriverDB");
fieldUrlDB = propiedades.getProperty("UrlDB");
fieldUsuarioDB = propiedades.getProperty("UsuarioDB");
fieldClaveDB = propiedades.getProperty("ClaveDB");
bMuestraQueryDB = "1".equals(propiedades.getProperty("MuestraQueryDB"));
bOldConnection = "1".equals(propiedades.getProperty("OldConnection"));
bOldHistran = "1".equals(propiedades.getProperty("OldHistran"));
bUseConcilia = "1".equals(propiedades.getProperty("UseConcilia"));
bOnlinePOS = "1".equals(propiedades.getProperty("OnlinePOS"));
bOnlineATM = "1".equals(propiedades.getProperty("OnlineATM"));
} catch (java.io.FileNotFoundException e) {
System.out.println("initVariables - Archivo de propiedades no encontrado: " + e);
} catch (java.io.IOException e) {
System.out.println("initVariables - Problemas con la lectura del archivo de propiedades: " + e);
}
return;
}
/**
* Método de liberar conexión, es invocado cuando ocurre un error en la ejecución
* @return void
* @param void
*/
private void liberarConexion() {
/* Desarrollo del Metodo de liberar conexión. */
try {
//if (USA_JDBC)
fieldConexionDB.close();
} catch (Exception e) {
System.out.println("Error en desconexión a la Base de Datos");
}
return;
}
private void obtenerConexion() {
/* Desarrollo del metodo para obtener la conexion */
try {
if (bMuestraQueryDB == true)
System.out.println("Obteniendo Conexión a la BD");
// Con la Base de Datos if (bOldConnection) {
Class.forName(fieldDriverDB);
fieldConexionDB = DriverManager.getConnection(fieldUrlDB, fieldUsuarioDB, fieldClaveDB);
} else {
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(fieldContextDB);
fieldConexionDB = ds.getConnection(); }
if (bMuestraQueryDB == true)
System.out.println("Terminó de Realizar la Conexión a la BD");
}catch (javax.naming.NamingException e) {
System.out.println("obtenerConexion - Error Naming Exception: " + e);
liberarConexion();
}
catch (java.sql.SQLException e) {
System.out.println("obtenerConexion - Error en la conexion a la Base de Datos: " + e);
liberarConexion();
} catch (java.lang.ClassNotFoundException e) {
System.out.println("obtenerConexion - Error en la carga del Driver de la Base de Datos: " + e);
liberarConexion();
}
return;
}
/**
* Rutina que setea los campos blob del archivo
* @param IDField <b> Identificador del campo que contiene el blob</b>
* @param sts <b> Statement de SQL </b>
* @param FileStr <b> String que contiene el path donde se almacena el archivo </b>
* @return sw <b> si logro o no insertar el campo blob </b>
*/
protected boolean SetBlobFile(int IDField,PreparedStatement sts,String FileStr){
try{
if(FileStr!=null){
java.io.File m_file = new java.io.File(FileStr);
java.io.FileInputStream fis = new java.io.FileInputStream(m_file);
long filelength = m_file.length();
if (filelength != 0){
sts.setBinaryStream(IDField, fis, (int) filelength);
}else{
System.out.println("SetBlobFile: la imagen tiene longitud cero");
sts.setObject(IDField,null);
}
}else{
//Se debe setear el campo en null
sts.setObject(IDField,null);
}
}catch(Exception e){
System.out.println("SetBlobFile: "+ e);
return false;
}
return true;
}
public boolean ComienzaJDBC(){
try {
obtenerConexion();
if (fieldConexionDB != null){ this.fieldConexionDB.setAutoCommit(false);
return true;
}
else{
return false;
}
} catch (SQLException e) {
System.out.println("Error ComienzaJDBC: "+e);
if (fieldConexionDB != null) {
try { fieldConexionDB.close(); } catch (SQLException e1) { ; }
fieldConexionDB = null;
} return false;
}
}
public boolean ComienzaJDBC(boolean AutoCommit){
try {
obtenerConexion();
if (fieldConexionDB != null){ this.fieldConexionDB.setAutoCommit(AutoCommit);
return true;
}
else{
return false;
}
} catch (SQLException e) {
System.out.println("Error ComienzaJDBC: "+e);
if (fieldConexionDB != null) {
try { fieldConexionDB.close(); } catch (SQLException e1) { ; }
fieldConexionDB = null;
} return false;
}
}
public boolean TerminaJDBC(){
try {
if (fieldConexionDB != null){
this.fieldConexionDB.commit();
this.fieldConexionDB.setAutoCommit(true);
fieldConexionDB.close();
fieldConexionDB = null;
}
return true;
} catch (SQLException e) {
System.out.println("Error TerminaJDBC: "+e);
if (fieldConexionDB != null) {
try { fieldConexionDB.close(); } catch (SQLException e1) { ; }
fieldConexionDB = null;
} return false;
}
}
public boolean RollBackJDBC(){
try {
this.fieldConexionDB.rollback();
this.fieldConexionDB.setAutoCommit(true);
fieldConexionDB.close();
return true;
} catch (SQLException e) {
System.out.println("Error TerminaJDBC: "+e);
return false;
}
}
}
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
