Right way to configure Tomcat JDBC resource for MaxDB

[Former Member]

Hi,

i need to configure one tomcat server with a pool of connections for a MaxDB instance with follow database parameters:

MAXUSERTASKS=150
SESSION_TIMEOUT=60

i do this configuration on tomcat:

<Resource name="jdbc/myApp"
                auth="Container"
                type="javax.sql.DataSource"
                username="DBUSER"
                password="secret"
                driverClassName="com.sap.dbtech.jdbc.DriverSapDB"
                url="jdbc:sapdb://dbserver/DBNAME"
                maxActive="150"
                maxIdle="75"
                validationQuery="SELECT NOW() FROM DBA.DUAL"/>

is this the right way? there is a way to check if the pool is working at database side? at java side i checked that the javax.sql.DataSource.loginTimeout is Unavailable, then what is the better way to avoid errors like:

2008-06-02 13:54:36  2165 WNG 11824 COMMUNIC Releasing  T187 command timeout

i think that this problem occurs because the SESSION_TIMEOUT is 60, and the loginTimeout is Unavailable at javax.sql.DataSource then the DBCP of tomcat dont know when need to revalidate the session.

thanks for any help.

Clóvis

[lbreddemann · Accepted solution]

Hi Clovis,

afaik the javax.sql.DataSource.loginTimeout is not about a idle session timeout at the dbserver side, but about how long a login procedure is allowed to take (at least that's what the docs say ... [http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/DataSource.html#setLoginTimeout(int)]

What you're facing are session timeouts that occur due to long inactive sessions on the database.

Check the documentation on [Timeout|http://maxdb.sap.com/doc/7_6/5f/04a521376fa44a910797138d4ed497/content.htm].

Perhabs it's enought just to increase the default timeout for the user you connect with.

If this does not help you may check if you can provide connection properties for the jdbc connection and set an appropriate timeout there.

KR Lars

[Former Member · Accepted solution]

Hi, Lars,

i changed the resource to use ConnectionPoolDataSourceSapDB intead of the DriverSapDB, as follow:

<Resource name="jdbc/conces_primary"
                auth="Container"
                type="javax.sql.DataSource"
                username="VENDOR_CONCES"
                password="n2n2r0l5"
                driverClassName="com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB"
                url="jdbc:sapdb://192.168.101.5/SPDT?trace=/mnt/logs/jdbc/spdtPrimary.log"
                maxActive="30"
                maxIdle="15"/>

since i'm using a connection pool to control db connections i think that this is the right way, you can check if the ConnectionPoolDataSourceSapDB manage well the session timeout? or have you heard something about what is the right configuration to achieve a better connection pool, like have many connections open when the system really have a high load, and few connections when the system have low load?

KR

Clóvis

[lbreddemann · Accepted solution]

Hi Clovis,

to be honest, I'm no Tomcat expert at all. All I can do is read the documentation.

The error message you posted was about an timeout due to a too long idle session.

From the changes in your connection pool setup I cannot see any parameter that would influence this.

Instead - as already written - you may either want to change the session timeout in general, for the user or (by adding some connection parameters) for the sessions.

Concerning the question on wether it's better to have many connections or just a few - well this depends on what the java tasks do with the sessions.

The connection pool is used for saving resources on the database side and the time necessary to logon and create a session.

So whenever any of the java tasks needs a db connections it should "just get one" from the pool.

When you have "high load" (by the way what exactly does that mean? High CPU usage on the J2EE server? Huge transaction volume? Many parallel transactions?) this does not change.

Anyhow you have to be aware of the fact that the database can only process as many SQL requests in parallel as there are user kernel threads (UKTs) and each of the UKTs need to be able to run on a seperate CPU core. Therefore if it's necessary to have real parallel SQL activity many connections may not be the best idea.

On the other hand this requirement is seldom necessary - most often sessions can wait a bit until they get the reply from the database and synchronize on each other later on. At least that's how we do it with NetWeaver.

KR Lars

[Former Member · Accepted solution]

hi Lars,

what i'm trying to solve is the problem explained at , dont using pool of connections works right for me, but when have many parallel requests, the JDBC driver locks the DriverSapDB object, and freeze the system, then because of that problem i now must create a pool of connections, and i wish to avoid session timeout, another thing about this blocked behavior is that only occurs in a Multi-Core CPU, having that running in a Single-Core the BLOCKED problem dont occurs.

KR

Clóvis