DB2™ Audit Trail Database Setup

This section addresses the setup and installation of a DB2 audit trail database. The section includes setup of DB2, creation of the DB2 audit trail database and schema.

Prerequisites

The following sections assume a basic understanding of DB2 database server installation, configuration and the DB2 command line utility.

Before an audit trail database can be created, a properly installed and configured instance of a DB2 database server must exist. DB2 can be downloaded from the following IBM Download Website.

After downloading DB2, install DB2 onto the database server. The server installation directory is referred to as %DB2_HOME%.

Setting Up A DB2 Server

The following tasks must be performed to setup the DB2 server, database directories, buffer pools and security.

Using the sample db2cli.ini Configuration File

With version 9.7 and later, a sample configuration file called db2cli.ini is provided to help you get started.

The following usage description is a slightly adapted snippet from the IBM DB2 9.7 Information Center > Database application development online help on the following site:

IBM DB2 9.7 Information Center

Refer to this page for more details.

The db2cli.ini file contains various keywords and values that can be used to configure the behavior of CLI (Call Level Interface) and the applications using it. The keywords are associated with the database alias name, and affect all CLI and ODBC applications that access the database.

You can create a db2cli.ini file that is based on the db2cli.ini.sample file and that is stored in the same location. The location of the sample configuration file depends on your driver type and platform. Please refer to IBM DB2 9.7 Information Center for your specific location. You can use the environment variable DB2CLIINIPATH to specify a different location for the db2cli.ini file.

Note that if you use a copy of the db2cli.ini file from version 9.7 Fix Pack 2 or earlier on Windows, the file is in a different location. You can keep the copy of the db2cli.ini file in the previous location, but this location might not be valid in future releases.

The configuration keywords in the db2cli.ini file enable you to:

Note
If the CLI/ODBC configuration keywords set in the db2cli.ini file conflict with keywords in the SQLDriverConnect() connection string, then the SQLDriverConnect() keywords will take precedence.

The db2cli.ini initialization file is an ASCII file which stores values for the CLI configuration options. The db2cli.ini.sample sample configuration file is shipped to help you get started. While most CLI/ODBC configuration keywords are set in the db2cli.ini initialization file, some keywords are set by providing the keyword information in the connection string to SQLDriverConnect() instead.

There is one section within the file for each database (data source) you like to configure. If needed, there is also a common section that affects all database connections.

Only the keywords that apply to all database connections through the CLI/ODBC driver are included in the COMMON section. This includes the following keywords:

All other keywords are to be placed in the database specific section, described below.

Note
Configuration keywords are valid in the COMMON section, however, they will apply to all database connections.

The COMMON section of the db2cli.ini file begins with:

[COMMON]

Before setting a common keyword it is important to evaluate its impact on all CLI/ODBC connections from that client. A keyword such as TRACE, for instance, will generate information on all CLI/ODBC applications connecting to DB2� on that client, even if you are intending to troubleshoot only one of those applications.

Each database specific section always begins with the name of the data source name (DSN) between square brackets:

[data source name]

This is called the section header.

The parameters are set by specifying a keyword with its associated keyword value in the form:

KeywordName =keywordValue

The following is a sample .INI file with two database alias sections:

; This is a comment line.
[MYDB22]
AutoCommit=0
TableType="'TABLE','SYSTEM TABLE'"

; This is another comment line.
[MYDB2MVS]
CurrentSQLID=SAAID
TableType="'TABLE'"
SchemaList="'USER1',CURRENT SQLID,'USER2'"

Although you can edit the db2cli.ini file manually on all platforms, it is recommended that you use the Configuration Assistant if it is available on your platform or the UPDATE CLI CONFIGURATION command. You must add a blank line after the last entry if you manually edit the db2cli.ini file.

Setting Transaction Isolation Level

Following table shows the DB2 UDB(Universal DataBase) equivalent to the ANSI SQL Isolation Level:

ANSI SQL Isolation Level DB2 UDB Equivalent
SERIALIZABLE Repeatable Read (RR)
REPEATABLE READ Read stability(RS)
READ COMMITTED (default) Cursor Stability (CS)
READ UNCOMMITTED Uncommitted Read (UR)

To determine the isolation level in DB2, execute the following query:

SELECT ISOLATION FROM SYSCAT.PACKAGES;

Infinity Process Platform databases must run in read-committed mode. So if the ISOLATION value is CS, then you can proceed further. Otherwise, execute the following query to change the ISOLATION level to CS.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Database Creation

A separate Infinity Process Platform database partition for the default DB2 instance has to be created. To perform this, use the DB2 command line processor and execute the following command on the prompt:

db2 => CREATE DATABASE TESTDB ALIAS TESTDB

This command creates a database with default settings. For advanced options, consult the DB2 administration guide.

Now, connect to this database so that all subsequent commands are executed against it.

db2 => CONNECT TO TESTDB

You must be sure that your current OS user has sufficient rights. Later, when creating table spaces, we need SYSADM rights. To check whether you have these, run the command:

db2 => GET AUTHORIZATIONS

If your current user does not have SYSADM rights, reconnect with a user that does.

db2 => CONNECT RESET
db2 => CONNECT TO TESTDB USER <db2admin> USING TESTDB

Changing the Buffer Pool Size

The default page size for a newly created database is insufficient for use with Infinity Process Platform. Therefore, configure a new buffer pool with page size 8 and set it as default buffer pool for table spaces to be created later. The buffer size in the following command can be customized according to user requirements. The recommended size is 1000.

db2 => CREATE Bufferpool TESTDBBUFFERPOOL SIZE 1000 PAGESIZE 8 K

Next, restart the database in order to make the new buffer pool available.

db2 => RESTART DATABASE TESTDB

Table Space Creation

Next, create a regular table space within the TESTDB database. Before table space creation, the database manager instance should be stopped and restarted as follows:

db2 => STOP DATABASE MANAGER FORCE
db2 => START DATABASE MANAGER

The setting MANAGED BY SYSTEM or MANAGED BY DATABASE depends on whether you created the database with COLLATE USING DATABASE or COLLATE USING SYSTEM option. The MANAGED BY DATABASE option is usually used in production environments where separate space can be allocated. In this example, system managed is used.

db2 => CREATE REGULAR TABLESPACE TESTDBTABLESPACE PAGESIZE 8 K MANAGED BY SYSTEM USING ('C:/database/db2/TESTDBTS') BUFFERPOOL TESTDBBUFFERPOOL

Also, create a temporary table space with the same buffer pool.

db2 => CREATE SYSTEM TEMPORARY TABLESPACE TESTDB_TEMP_TS PAGESIZE 8 K MANAGED BY SYSTEM USING ('C:/database/db2/TESTDBTempTS') BUFFERPOOL TESTDBBUFFERPOOL

Note: These examples show table spaces on a Windows platform, replace with the correct directory syntax in a Unix environment.

Setup your database to use the correct table space by default. For details on how to create table spaces for your database, please refer to the according manual of the DB2 audit trail.

In case you like to setup a table space for a user/schema explicitly, one option is to perform the following steps:

  1. Spool the schema creation script into a file
  2. Adapt statements in order to use the "correct" table space. Note that this might be different for tables and indexes, e.g.:
    CREATE TABLE <table_name> (<columns....>) IN <ts_name>.
  3. Execute the adapted schema creation command

Schema and Tables Generation

In DB2, the database users are actually system users i.e., given any platform windows/unix, the account you used to log in to the system will be used to carry out any database specific operations.

If logged on as XXXX, the same account is used to generate schema and any tables. With this in mind, create a schema with the following command:

db2 => CREATE SCHEMA <schema> AUTHORIZATION XXXX

If you need to connect with a different user, set the authorization user like:

db2 => CREATE SCHEMA <schema> AUTHORIZATION <db2admin>
		

Infinity Process Platform Schema Setup

Once the pre-requisites have been met, the audit trail database schema can be created within DB2. This process utilizes the System Console utility provided with Infinity Process Platform.

Execute the System Console with the following arguments:

sysconsole -v -r com.ibm.db2.jcc.DB2Driver -dbtype DB2 -dbschema <dbschema> -l jdbc:db2://<host>:<port>/<schema> -d <user> -s <password> createschema

The following is displayed after the command is invoked:

Infinity Process Platform (TM) Administration Console, Version 4.x
Copyright (C) SunGard CSA LLC, 2000-2011. All rights reserved.

Create Infinity Process Platform schema:

Database type   : DB2
Database URL    : jdbc:db2://<host>:<port>/<schema>
Database user   : <user>
Database driver : com.ibm.db2.jcc.DB2Driver

Do you want to proceed? (Y/N):

Answer Y to proceed. The following is displayed after successful creation of the audit trail database schema.

Schema created.

Alternative Schema Setup

If you do not have the ability to directly create the database schema because of privileges within your database environment or desire to create a file with the appropriate SQL commands, the following procedures can be followed to create a file containing the appropriate DDL and DML statements that can be executed within the database as a SQL script.

sysconsole -v -dbtype DB2 ddl -file <fileName> -schemaName <schemaName>

Once the DDL/DML file has been created by the previous command, follow the Database setup instructions from the previous sections if not already completed, and then have a user who has appropriate privileges execute the created DDL/DML script file as such:

command prompt>  db2 -f <fileName>

Local and Remote Mode Setup

The following sections discuss the datasource / database connection pool setup of the audit trail database based on the deployment mode.

Spring Local Mode Setup

If you use Infinity Process Platform in Spring local mode, you have to perform the following steps:

  1. In your carnot.properties file, add the following properties:
    AuditTrail.Type = DB2
    
    AuditTrail.DriverClass = <jdbcDriver>
    AuditTrail.URL = jdbc:db2://<host>:<port>/<schema>
    AuditTrail.User = <yourDBUserName>
    AuditTrail.Password = <yourDBUserPassword>
    AuditTrail.Instance =
    AuditTrail.Port =
    AuditTrail.Host =
    Set the type of the audit trail database to DB2 and use the database driver class specific to your installation of DB2.
  2. In your carnot-spring-ds-jndi-context.xml replace the parameter ${AuditTrail.XaDataSourceClass} in the carnotXaAuditTrailDataSourceLocal bean part with class com.ibm.db2.jcc.DB2XADataSource:
    <bean id="carnotXaAuditTrailDataSourceLocal" class="com.ibm.db2.jcc.DB2XADataSource" lazy-init="true">
        <property name="serverName" value="${AuditTrail.Host}" />
        <property name="portNumber" value="${AuditTrail.Port}" />
        <property name="databaseName" value="${AuditTrail.Instance}" />
        <property name="user" value="${AuditTrail.User}" />
        <property name="password" value="${AuditTrail.Password}" />
    </bean>
            

Appropriate JDBC drivers must be copied from the %DB2_HOME%/IBM/SQLLIB/java/ directory to the lib directory of your working environment.

Remote Mode Setup

The audit trail database datasource and connection pool setup is configured based on the application server/container utilized for the Infinity Process Platform deployment. Refer to Application Server Setup for more information regarding datasource and connection pool setup for an audit trail database.