DB2 Client Installation and configuration

This article describes the installation of the DB2 client on a Windows server or on a normal Windows PC. This is only needed when you use ProcMan with a DB2 database.

It is recommended to install the DB2 client first before installing HWF and ProcMan. The DB2 client is needed to connect with a DB2 database.

Overview

This document provides guidance on configuring the IBM DB2 Command Line Interface (CLI) for optimal performance and compatibility with ProcMan. The settings ensure efficient transaction handling and improved performance by adjusting the default behavior of the DB2 client.

DB2 Client Installation

1. Download the latest DB2 Client for Windows (For HORIZONT installations see v:/download/DB2_DSClient_11-1_Win_x86-64).

2. Run the setup.exe, select new installation, and accept the licence agreement. Start the installation.

3. Navigate to the installation directory (e.g. c:/program files/IBM/SQLLIB/BIN/db2cmdadmin.exe) and execute the db2cmdadmin.exe, the Command Line interface (CLI) should open now.

4. Execute the command db2 in the CLI, the DB2-Client CLI should start now.

Connect the client to the database

Now you need to connect to the DB2 Database.

In the ProcMan Installation package, you should see a file called add_connection_example.db2 in the directory /DB2. Open that file in your preferred editor (e.g. notepad++). Copy the last 6 rows (see code below) one after another and execute them in the CLI.

Important: Do not copy the semicolons. You should get positive feedback for each command in the CLI.

catalog tcpip node <SERVER> remote <IP> server <PORT> remote_instance DB2 system <IP>;
catalog database <SCHEMA> as <SCHEMA> at node <SERVER> authentication server;
catalog system odbc data source <SCHEMA>;
catalog dcs database <SCHEMA> as <SCHEMA>;
update cli cfg for section <SCHEMA> using TXNIsolation 1;
update cli cfg for section <SCHEMA> using DB2Optimization 5;

Testing the DB2 connection

To test the connection to the HORIZONT DB2 Database, execute the following two commands in the CLI.

list database directory
connect to <SCHEMA> user <USERID>

You should get a positive response in the CLI, like in the picture below.

Recommended configuration options

The following addtional configurations are recommended.

TXNIsolation

• Setting: TXNIsolation=1

• Meaning: Sets the transaction isolation level to Read Uncommitted

• Effect: Allows dirty reads, enabling higher performance with minimal locking

DB2Optimization

• Setting: DB2Optimization=5

• Meaning: Enables a higher level of CLI optimization

• Effect: Improves execution speed and efficiency by pre-packaging SQL statements more aggressively

Option A: Edit db2cli.ini

This option is recommended if file access is available.

Open the following file: c:\Users\All Users\IBM\DB2\DB2COPY1\cfg\db2cli.ini (beware that this default path of the file can be overwritten in the system variable DB2CLIINIPATH

For older DB2 Client versions the default path is: c:\Program Files\IBM\DB2\DB2COPY1\cfg\db2cli.ini

Add the following lines under the section for your database alias:

TXNIsolation=1
DB2Optimization=5

Option B: Use DB2 CLI Commands

This option is recommended if no file access is available and if your user is part of the DB2ADMIN group on the ProcMan server.

1. Open the DB2 Command Line Processor (db2cmd) on the ProcMan server

2. Run the following commands:

db2 update cli cfg for section <DBALIAS> using TXNIsolation 1
db2 update cli cfg for section <DBALIAS> using DB2Optimization 5

Replace <DBALIAS> with your configured database alias.

For to check if the options have been set correctly execute the following command:

db2 get cli cfg for section <DBALIAS>

Catalogue database

The database, which shall be used by ProcMan, has to be cataloged in the DB2 Client in a way that the user authentication is done by the DB2 Server:

catalog database MY_DB as MY_DB at node MY_NODE authentication server;

This ensures that DB2 Client does not try to authenticate the user used for establishing a database connection on the client system (where DB2 Client is installed), but sends the user information for authentication immediately to the DB2 Server, which leads to a better performance.

Troubleshooting

Licence error

If you get an licence error while executing the command

connect to <ALIAS> user <USERID>

you need to install a licence. Ask some one like Jörg/Rado for the licence file db2consv_zs.lic, place it on your PC, open the CLI, navigate to the directory where the licence file is stored and execute the command

db2licm –a db2consv_zs.lic

to install the licence. To check if the licence was installled correctly, execute the command

db2licm -l

db2cmdadmin window opens and immediately closes

This problem is described here: IBM Support

Connect with the server with remote desktop. Go to computer management and two groups. First, create local DB2ADMNS and DB2USERS groups and add your user to that group. Then, add the Domain Account to one or both of these groups. These groups are the default groups recognized by DB2. Members of the DB2ADMNS group have SYSADMIN Authority. Members of the DB2USERS group do not have SYSADMIN Authority but are able to execute DB2 commands. Also use the CLI (as admin) and execute the command 'db2extsec'.

Next, from a Windows Command Prompt execute:

db2set db2_grp_lookup=local

Now the db2cmdadmin CLI should open and not immediately close.