Creating a DB2 table for session persistence

Why and when to perform this task

If you are using DB2 for session persistence, a DB2 table, in which session data will be collected, must be created and defined to the application server.

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

Steps for this task

  1. Have your DB2 Administrator create a DB2 database table for storing your session data. (For more information about creating DB2 databases see the 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 that will be 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 will use the DB2 table defined within this table space to process the session data. This table must have the following format:

    CREATE TABLE database_name.table_name  (
      ID               VARCHAR(95) NOT NULL ,
      PROPID           VARCHAR(95) 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),
      SESSROW          ROWID NOT NULL GENERATED ALWAYS
      )
      IN database_name.tablespace_name;

    Note: The length attributes specified for VARCHAR in this example are not necessarily the values your DB2 Administrator should use for the DB2 table he is creating. See the DB2 SQL Reference for the version of DB2 you will be using for guidance in determining appropriate values for these length attributes for your installation.

    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.
          database_name.table_name
          (ID     ASC,
          PROPID  ASC,
          APPNAME ASC);

    Note:

    1. At run time, the Session Manager will access the target table using the identity of the J2EE server in which the owning Web application is deployed. Any Web container that is configured to use persistent sessions should be granted 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 you are using for recommendations on how to calculate the space required for an index, and adjust the size of the tables you are using accordingly.
    3. It may 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 you are using.

    A large object (LOB) table space must be defined and an auxiliary table must be defined 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 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. Configuring a DB2 table for session persistence.

Related tasks
Configuring for database session persistence
Configuring a DB2 table for session persistence



Searchable topic ID:   tprs_db2tzos
Last updated: Jun 21, 2007 9:56:50 PM CDT    WebSphere Application Server for z/OS, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.websphere.zseries.doc/info/zseries/ae/tprs_db2tzos.html

Library | Support | Terms of Use | Feedback