Preparing the AE Database - DB2

This topic guides you through the steps necessary to set up a DB2 database for the Automation Engine.

Notes:

Database administration rights are necessary to perform this step.
Please read the relevant notes about optimizing the AE database's performance carefully before you start setting up the database.
Do not limit resource consumption. Transactions that repeatedly abort because of limitations that are specified in the database can impede processing in your AE system. Inconsistent database contents can be a result thereof and cause subsequent errors or endless loops.
Please note that your database will become inconsistent when you modify database contents.

More information:

This page includes the following:

Disk Space Required

The initial database space required for an Automation Engine installation depends on the type and size of the system:

Test systems: 1 GB
Production systems
- Small systems: 5 - 20 GB
- Medium systems: 20 - 50 GB
- Large systems: > 50 GB

Other Requirements

Installation settings

The following settings for DB2 must be configured on the DB computer:

A 64-bit client must be installed
Install the appropriate DB2 client software on the Automation Engine computer in order to enable access to the database. It is important to note that the CAE version must correspond to the database version.
Install DB2 for UNIX and Windows with the following options:

8, 16 and 32 KB page size for specific codes
USER temp and system space must be 8K and 32K
Create the AE database with an ASCII code table (code set 819 for ISO8859-1, code set 923 for ISO8859-15) instead of a Unicode table. Note that the default is UTF-8 (Unicode).
Create a buffer pool of 8K and one of 32K. For each of the two page sizes, set up a system temp and a regular tablespace.

Note: A temporary system tablespace of 32k must be available.

Set up the ODBC string in a way that the database can only be accessed by using a valid user ID.
The module DBMS_LOB has to be installed.

The following scripting example creates a new database with a specific coding:

create db uc4
automatic storage yes
on ...
using codeset <code set> territory <short form of country> ;

The following is a scripting example for the US:

create db uc4 automatic storage yes on ... using codeset iso-8859-1 territory us ;

DB2 for UNIX or Windows

Special Requirements

SQLDRIVERCONNECT=ODBCVAR=NNJNIORD,DSN=DB2CLI;UID=uc4;PWD=--1018A94DA12E7FA991

The following parameters have to be set in order to improve performance for DB2 on UNIX/Windows:

DLCHKTIME to 1000

This parameter controls the database's deadlock time. The predefined default value is 10 seconds, which negatively impacts the system's performance (1000 corresponds to 1 second).

For example:

db2 -v update database cfg for UC4 using DLCHKTIME 1000
LOCKLIST to 10240
LOGBUFSZ to 2048
APPLHEAPSZ at least to 2048
PCKCACHESZ to 256
CATALOGCACHE_SZ to 128
AUTO_RUNSTATS to OFF. Manually execute the RUNSTATS when the database has regularly been in use for some time and all tables contain data records. DB2 selects incorrect access channels if RUNSTATS are processed on empty tables. Deadlock situations can occur which could eventually result in a complete system standstill. The file upd_stat.sql. is provided in the folder IMAGE:\db\db2\Automation Engine version. It contains the relevant statements for the manual RUNSTAT execution.

These parameters can be called using the command "get dbm cfg" which requires no authorizations:

They can be set with db2set. For example: db2set DB2_EVALUNCOMMITTED=YES.

Setting up DB2 Alias on CAE Client

Setting for the DB2 code page:

db2set db2codepage=819

db2
catalog tcpip node <servername> remote <servername> server 50000
catalog database <db-name> as <alias-name> at node <servername>
quit

Definition of Tablespaces

Create 3 tablespaces as follows: