tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Friedrich Gonzalez <sopo...@suiche7b.com.ve>
Subject Tomcat 4.1 DBCP DB2 Problem
Date Tue, 21 Dec 2004 17:03:40 GMT
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: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message