Setting up the Database

Setting up the Database

MS SQL Server

---

DB2

This guide includes the individual steps that are required to set up an DB2 databaseA database is an organized collection of data including relevant data structures. for AE usage.

Important: Automic recommends carefully reading the relevant notes about optimizing the AE database's performance before you start setting up the database.
See:Configuration & Performance of the Database, the Automic Compatibility Checker 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.

Note that your database will become inconsistent when you modify database contents.

Procedure

General Requirements:

• 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

• Special requirements:

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

The following parameters can 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).
• 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.
• DB2_EVALUNCOMMITTED to ON
• DB2_SKIPINSERTED to ON
• DB2_SKIPDELETED to ON

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

• Setting up DB2 alias on CAE client:

• Setting for the DB2 code page:

db2set db2codepage=819

Login with DB2 Admin:

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

• Definition of tablespaces:
• Create tablespaces of page size 8, 16 and 32 KB.