DB2

This guide includes the individual steps that are required to set up an DB2 database for AE usage.

Important 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. See: Configuration & Performance of the Database, the Automic Compatibility Matrix for details on supported versions and platforms and the general information in Supported Platforms.
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.

Disk Space Required

Size required for the initial installation of an AE database
Test systems:	1 GB
Production systems:
Small systems	5 - 20 GB
Medium systems	20 - 50 GB
Large system	more than 50 GB

General Requirements

Installation settings for DB2 (to 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.

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

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

db2set db2codepage=819

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