Configuring the DB2 Client

Before you can define an ODBC Datasource, you must first configure the database connections to the DB2 Client.

This can be done with:

the DB2 Configuration assistant (if you have this installed on your PC)
alternatively you can use DB2 CLI commands. These are given below.

Copying Database Definitions between machines

The Configuration Assistant gives you the ability to Export and Import database connection definitions.

If you do not have the Configuration Assistant on your machine, there is no way of doing this.

Defining Database Connections with DB2 CLI commands

To run these commands you need to start the DB2 Command-Line Processor (CLP).

You do this by running the following command in Windows

db2cmd

db2cmd

Defining a Node

The first step is to define a Node (which is a DB2 server). This is done with a command such as the following:

db2 catalog tcpip node DB2SERV1 remote SERVER1 server 50000

where:

DB2SERV1 is the name you wish to give this node. This can be any name
SERVER1 is the Hostname or IP address of the DB2 Server
50000 is the TCP/IP Port the DB2 Server is listening on

You may need to contact your Database Administrators to find out the values for Hostname and TCP/IP Port.

You will get the following response:

DB20000I The CATALOG TCPIP NODE command completed successfully.

DB21056W Directory changes may not be effective until the directory cache is refreshed.

The above response is normal.

Checking that the node has been defined successfully

You can check that the Node has been defined with:

db2 list node directory

This won't tell you whether it is correct or not! A better way is to "attach" to the node. This is done with:

db2 attach to DB2SERV1

If this succeeds, or you get an authentication failure, then your definition is correct.

If, instead, you get an error such as:

SQL1336N The remote host "SERVER1" was not found. SQLSTATE=08001

then this means that either:

Hostname or Port are incorrect
a firewall (or other network problem) is preventing you from communicating with the DB2 server
the DB2 system is not running

Defining a Database

Once you have defined a Node, you can define the database. The most basic form of this command is:

db2 catalog database CUSTDB at node DB2SERV1

where:

CUSTDB is the name of the database on the DB2 Server
DB2SERV1 is the Node. This is the name you used

Another example:

db2 catalog database CUSTDB as CUSTDEV at node DB2SERV1 authentication client

In this:

use as CUSTDEV to give your own name for the database. This is often called the database alias.
the authentication clause is used to specify how your userid/password will be authenticated, This is an important parameter. You may wish to consult with your support group for advice on what to specify for this.

You can check that the database has been defined successfully with:

db2 list database directory

Alternatively you can see whether you can connect to the database with either:

db2 connect to CUSTDEV

db2 connect to CUSTDEV using userid

(you will be prompted for the password)

Creating an ODBC Datasource

The DB2 CLP allows you to create an ODBC Datasource. This saves the need for having to do this using AQT or the Datasource Administrator.

The command for this is:

db2 catalog odbc datasource CUSTDEV

where CUSTDEV is the name of yor database alias.

Advanced Query Tool

https://www.querytool.com