Creating a table for session persistence

You can use a database table to collect and store session data. If you are using a database table for session persistence, you must create and define a database table that is associated with the application server.

About this task

Whenever the session manager is set for database persistence, the session manager creates a table for its use. If you want to expand the column size limits to make it more appropriate for your website, you can create the table externally. If the external table is specified as the target table in the session manager database persistence configuration, then during the session manager startup, the external table is used. In most cases, it is better to have the session manager create the table during startup.

To create a table for collecting session data, do the following:

Procedure

Have your administrator create a database table for storing your session data using one of the following data definition language (DDL):
For DB2®:
```
CREATE TABLE <SchemaName>.sessions  (
  ID               VARCHAR(128) NOT NULL ,
  PROPID           VARCHAR(128) NOT NULL ,
  APPNAME          VARCHAR(128) NOT NULL,
  LISTENERCNT      SMALLINT ,
  LASTACCESS       BIGINT,
  CREATIONTIME     BIGINT,
  MAXINACTIVETIME  INTEGER ,
  USERNAME         VARCHAR(256) ,
  SMALL            VARCHAR(3122)  FOR BIT DATA ,
  MEDIUM           LONG VARCHAR FOR BIT DATA ,
  LARGE            BLOB(2M)
    )
  
```
For Oracle:
```
CREATE TABLE SESSIONS  (
  ID               VARCHAR(128) NOT NULL ,
  PROPID           VARCHAR(128) NOT NULL ,
  APPNAME          VARCHAR(128) NOT NULL,
  LISTENERCNT      SMALLINT ,
  LASTACCESS       INTEGER,
  CREATIONTIME     INTEGER,
  MAXINACTIVETIME  INTEGER ,
  USERNAME         VARCHAR(256) ,
  SMALL            RAW(2000),
  MEDIUM           LONG RAW ,
  LARGE            RAW(1)
  )
```
If the web container custom property UseOracleBLOB is set to true then:
```
CREATE TABLE SESSIONS  (
  ID               VARCHAR(128) NOT NULL ,
  PROPID           VARCHAR(128) NOT NULL ,
  APPNAME          VARCHAR(128) NOT NULL,
  LISTENERCNT      SMALLINT ,
  LASTACCESS       INTEGER,
  CREATIONTIME     INTEGER,
  MAXINACTIVETIME  INTEGER ,
  USERNAME         VARCHAR(256) ,
  SMALL            RAW(2000),
  MEDIUM           BLOB,
  LARGE            RAW(1)
  )
```
For Microsoft SQL :
```
CREATE TABLE SESSIONS (
ID               VARCHAR(128) NOT NULL,
PROPID           VARCHAR(128) NOT NULL,
APPNAME          VARCHAR(128) NOT NULL,
LISTENERCNT      SMALLINT NULL,
LASTACCESS       DECIMAL(21,0) NULL,
CREATIONTIME     DECIMAL(21,0) NULL,
MAXINACTIVETIME  INTEGER NULL,
USERNAME         VARCHAR(255) NULL,
SMALL            IMAGE NULL,
MEDIUM           IMAGE NULL,
LARGE            IMAGE NULL
)
```
Attention:
1. At run time, the session manager accesses the target table using the identity of the Java™ Platform, Enterprise Edition (Java EE) server in which the owning web application is deployed. Any web container that is configured to use persistent sessions must have both read and update access to the subject database table.
2. HTTP session processing uses the index defined using the CREATE INDEX statement to avoid database deadlocks. In some situations, such as when a relatively small table size is defined for the database, DB2 may decide not to use this index. When the index is not used, database deadlocks can occur. If database deadlocks occur, see the DB2 Administration Guide for the version of DB2 you are using for recommendations on how to calculate the space required for an index and adjust the size of the tables that you are using accordingly.
3. It might be necessary to tune DB2 to make efficient use of the sessions database table and to avoid deadlocks when accessing it. Your DB2 administrator should refer to the DB2 Administration Guide for specific information about tuning the version of DB2 that you are using.
4. During run time, the session manager may create an entry in the database for each web module of the application. This row of data is used internally for session management purposes, such as in session invalidation. Do not be concerned with this entry. It can be overlooked.
5. If a primary key constraint is required for the sessions database table, the primary key constraint must be defined using the ID, PROPID, and APPNAME columns.
Configure a table for session persistence.