Z/OS - Create a DB2 table for session persistence

Session data is collected and stored in a DB2 table. If we are using DB2 for session persistence, create and define a DB2 table associated with the application server.

1. Have your DB2 Administrator create a DB2 database table for storing your session data. See DB2 UDB for OS/390 and z/OS V7 Administration Guide

   The table space in which the database table is created must be defined with row level locking (LOCKSIZE ROW). It should also have a page size that is large enough for the objects stored in the table during a session. Following is an example of a table space definition with row level locking specified and a buffer pool page size of 32K:

     CREATE DATABASE database_name
     STOGROUP SYSDEFLT
     CCSID EBCDIC;
   
   CREATE TABLESPACE tablespace_name IN database_name
     USING STOGROUP group_name
     PRIQTY 512
     SECQTY 1024
     LOCKSIZE ROW
     BUFFERPOOL BP32K;
   

   The Session Manager uses the DB2 table defined within this table space to process the session data. This table must have the following format (with the exception of the VARCHAR lengths, which is configurable):

   CREATE TABLE database_name.table_name  (
     ID               VARCHAR(128) NOT NULL,
     PROPID           VARCHAR(128) NOT NULL,
     APPNAME          VARCHAR(64),
     LISTENERCNT      SMALLINT,
     LASTACCESS       DECIMAL(19,0),
     CREATIONTIME     DECIMAL(19,0),
     MAXINACTIVETIME  INTEGER,
     USERNAME         VARCHAR(256),
     SMALL            VARCHAR(3122)  FOR BIT DATA,
     MEDIUM           VARCHAR(28869) FOR BIT DATA,
     LARGE            BLOB(2097152),
     )
     IN database_name.tablespace_name;
   

   The length attributes specified for VARCHAR are just examples. These values should be large enough to handle the length of the application name and each session attribute name.

   A unique index must be created on the ID, PROPID, and APPNAME columns of this table. The following is an example of the index definition:

   CREATE UNIQUE INDEX database_name.index_name ON 
         database_name.table_name
         (ID     ASC,
         PROPID  ASC,
         APPNAME ASC);
   

   1. At run time, the Session Manager accesses the target table using the identity of the J2EE server in which the owning web application is deployed. Any Web container 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 isn't used, database deadlocks can occur. If this situation occurs, see the DB2 Administration Guide for the version of DB2 we are using for recommendations on how to calculate the space required for an index, and adjust the size of the tables we are using accordingly.
   3. It might be necessary to tune DB2 in order 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 we are using.

   We must define a large object (LOB) table space and also define an auxiliary table within that table space. The following is an example of the LOB table space definition:

   CREATE LOB TABLESPACE LOB_tablespace_name IN database_name
       BUFFERPOOL BP32K
       USING STOGROUP group_name
       PRIQTY 512
       SECQTY 1024
       LOCKSIZE LOB;
   
   CREATE AUX TABLE database_name.aux_table_name
       IN database_name.LOB_tablespace_name
       STORES database_name.table_name
       COLUMN LARGE;
   

   An index must be created for this auxiliary table. The following is an example of the index definition:

   CREATE INDEX database_name.aux_index_name ON database_name.aux_table_name;
   

2. Have your DB2 Administrator grant the z/OS userID, under which the server region is running, the appropriate access to this DB2 table. For example, issue the following command to grant z/OS userID CBASRU1, under which the server region is running, access to the table SESSIONS contained in the database SESSDB:

   GRANT ALL ON SESSDB.SESSIONS TO CBASRU1; 

3. Configure DB2 table for session persistence.