Tomcat 7: How to enable HTTP Session DataBase persistence for PostgreSQL and DB2

In this short article I will show you how to enable HTTP Session DataBase persistence, I will use two kind 

of relational database, DB2 and PostgreSQL.

It is worth to understand that Tomcat's Persistent Manager doesn't store the session data when session's attribute is updated, session manager uses some configuration parameters to define persistence frecuency, so if you plan to run some critical application probably you need to define another persistence strategy.

PostgreSQL


1) Create manually the session table and index.

CREATE TABLE tomcat_sessions
(
  session_id character varying(100) NOT NULL,
  valid_session character(1) NOT NULL,
  max_inactive integer NOT NULL,
  last_access bigint NOT NULL,
  app_name character varying(255),
  session_data bytea,
  CONSTRAINT tomcat_sessions_pkey PRIMARY KEY (session_id)
)

CREATE INDEX app_name_index
  ON tomcat_sessions
  USING btree
  (app_name);

2) Copy the PostgreSQL JDBC library postgresql-jdbc3.jar to $CATALINA_HOME/lib

3) Configure globaly Session Manager in $CATALINA_HOME/conf/context.conf

    <Manager className="org.apache.catalina.session.PersistentManager"
        distributable="true"  processExpiresFrequency="3" maxIdleBackup="1" >
        <Store className="org.apache.catalina.session.JDBCStore"
            driverName="org.postgresql.Driver"
            connectionURL="jdbc:postgresql://[YOUR_DB_IP]:[DB_PORT]/sessions"
            connectionName="tomcat" connectionPassword="password"
            sessionAppCol="app_name" sessionDataCol="session_data" sessionIdCol="session_id"
            sessionLastAccessedCol="last_access" sessionMaxInactiveCol="max_inactive"
            sessionTable="tomcat_sessions" sessionValidCol="valid_session" />
    </Manager>


maxIdleBackup: The time interval (in seconds) since the last access to a session before it is eligible for being persisted to the session store, or -1 to disable this feature. By default, this feature is disabled.

processExpiresFrequency: Frequency of the session expiration, and related manager operations. Manager operations will be done once for the specified amount of backgroundProcess calls (i.e., the lower the amount,  the more often the checks will occur). The minimum value is 1, and the default value is 6.

 Maximun store frecuency is about : maxIdleBackup + processExpiresFrequency * engine.backgroundProcessorDelay seconds, plus the time it takes to handle other session expiration, swapping, etc. tasks.

For more information check the official Tomcat documentation

http://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/catalina/session/PersistentManagerBase.html

It is also possible to use a datasource definition instead of defining connectionURL and driverName.

     <Resource name="jdbc/sessions" auth="Container" type="javax.sql.DataSource"
        username="tomcat"
        password="password"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://[YOUR_DB_IP]:[DB_PORT]/sessions"
        maxActive="20"
        maxIdle="10"
        validationQuery="select 1" />

    <Manager className="org.apache.catalina.session.PersistentManager"
        distributable="true"  processExpiresFrequency="3" maxIdleBackup="1" >
        <Store className="org.apache.catalina.session.JDBCStore"
            dataSourceName="jdbc/sessions"
            sessionAppCol="app_name" sessionDataCol="session_data" sessionIdCol="session_id"
            sessionLastAccessedCol="last_access" sessionMaxInactiveCol="max_inactive"
            sessionTable="tomcat_sessions" sessionValidCol="valid_session" />
    </Manager>

DB2

    

1) In our test case we used DB2 9.7, it is also necessary to create manually the session table and index

    create table tomcat_sessions ( session_id     varchar(100),

                                                   valid_session  char(1) not null, 

                                                   max_inactive   int not null, 

                                                   last_access    bigint not null, 

                                                  app_name       varchar(255),

                                                  session_data   blob )

    create unique index app_name_index on tomcat_sessions (app_name)

2)  Copy the DB2 driver library db2jcc4.jar to $CATALINA_HOME/lib

3) Configure globally Session Manager in $CATALINA_HOME/conf/context.conf

     <Resource name="jdbc/sessions" auth="Container" type="javax.sql.DataSource"

        username="tomcat"

        password="password"

        driverClassName="com.ibm.db2.jcc.DB2Driver"

        url="jdbc:db2://[YOUR_DB2_IP]:[YOUR_DB2_PORT]/sessions"

        maxActive="20"

        maxIdle="10"

        validationQuery="select 1 from sysibm.sysdummy1" />

    <Manager className="org.apache.catalina.session.PersistentManager"

        distributable="true"  processExpiresFrequency="3" maxIdleBackup="1" >

        <Store className="org.apache.catalina.session.JDBCStore"

            dataSourceName="jdbc/sessions"

            sessionAppCol="app_name" sessionDataCol="session_data" sessionIdCol="session_id"

            sessionLastAccessedCol="last_access" sessionMaxInactiveCol="max_inactive"

            sessionTable="tomcat_sessions" sessionValidCol="valid_session" />

    </Manager>