DB2 Version 8 Connectivity Cheat Sheet

Raul Chong (rfchong@ca.ibm.com), Database consultant, SDI Corp.

Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years in IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMS to DB2. He can be reached at: rfchong@ca.ibm.com.

Summary: Newly updated with iSeries connectivity information! For those who like to use commands instead of GUI tools, this article tells you how to get your clients connected to DB2 servers, either on distributed systems or OS/390, using DB2 commands. It includes a cheat sheet that you can print off and keep handy to make the process a breeze.

Tag this!

Date: 23 Jan 2003
Level: Introductory
Activity: 5094 views

Introduction

The purpose of this article is to provide quick and easy instructions for setting up connectivity between IBM ® DB2 ® Universal Database ^TM for Linux, UNIX ® and Windows ® V8 clients and DB2 servers using DB2 commands. The DB2 servers can be -- for this article -- DB2 UDB for Linux, UNIX and Windows version 8, DB2 UDB for z/OS ^TM version 8 or DB2 for iSeries ® 5.2. Note that though we are using the latest versions of DB2 UDB in the different platforms, the same instructions can apply to other DB2 UDB versions.

In case you've followed all the instructions and the connection isn't working, I've included some things to check to help you determine what the problem is as well as typical error messages.

The Configuration Assistant (CA) GUI tool, formerly known as the Client Configuration Assistant (CCA), can be used on either Windows or UNIX platforms as an easy graphical interface for establishing connectivity. However, if you know the actual DB2 commands that are executed under the covers by the CA Tool, you will have a better understanding of what is required when connecting DB2 clients to DB2 servers. Moreover, the commands can always be saved in a script that can be easily distributed to any DB2 client that requires the same connectivity setup. For completeness, however, I will include the steps to follow using the Configuration Assistant in the last section of this article.

The tables below show the steps for setting up connectivity using the DB2 Command Line Processor (CLP). Only "basic" command options will be shown. With DB2 Version 8, TCPIP is the only protocol supported. If you'd like to print off a slimmed down version of these charts to keep handy at your desk, go to the download section at the end of the article.

We'll examine five common scenarios where DB2 UDB for Linux, UNIX and Windows is always the client. For the scenarios where DB2 UDB for Linux, UNIX and Windows is the server (other than scenario 1), stay tuned for Part 2 of this article.

Scenario 1 - DB2 Linux/UNIX/Windows client to DB2 Linux/Unix/Windows server

From DB2 Linux/Unix/Windows machine to DB2 Linux/Unix/Windows machine. (There is no need to have DB2 Connect ^TM installed.)

Figure 1. DB2 Linux/Unix/Windows to DB2 Linux/Unix/Windows
Scenario 1

Machine 1 ('libra') Linux/UNIX/Windows	Machine 2 ('mpower') Linux/Unix/Windows
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
db2 catalog tcpip node mpwrnode remote mpower.ca.ibm.com server 50000 Notes: 'mpwrnode' is an arbitrary name chosen for the node. The IP address can be used instead of the hostname A service name defined in the 'services' file of this client with a value of 50000 can be specified instead of the port number itself.	For this example: mpower.ca.ibm.com = Host name of machine 2. 50000 = The port used for DB2. To find out the port used, issue this command from the CLP: `get dbm cfg` Then, look for the parameter SVCENAME If the value of SVCENAME is not the port number but a string, then look in your system for the file 'services' and grep for this string, which is normally based on your DB2 instance name. For example, if your instance name is 'db2inst1', you will normally find a corresponding entry like this: `db2cdb2inst1 50000/tcp` The 'services' file can be located at: `/etc/services` (UNIX) `X:\WINNT\System32\drivers\etc\services` (Windows)
`db2 catalog db a42ds1 at node mpwrnode`	a42ds1 = Name of the database you want to access in machine 2.
db2 terminate
`db2 connect to a42ds1` user <userid as defined on server machine 2> using <password as defined on server machine 2>

What to check if you cannot connect:

Client machine 'libra'	Database server 'mpower'
`ping mpower.ca.ibm.com`	mpower.ca.ibm.com = Host name of Database Server If you cannot ping there may be problems with the DNS. Try pinging the IP address.
`ping 158.228.20.100`	158.228.20.100 = IP address of Database Server This will confirm if there are problems or not with the network.
	Is DB2 started? If not run `db2start` . Is `DB2COMM` set to `TCPIP` ? Check by executing: `db2set -all` . If this registry variable is not set, you should execute: `db2set db2comm=tcpip` and then issue a `db2stop/db2start` to make sure the change takes effect. Is `SVCENAME` set to the port number or service name in the `services?` file of this server machine? Check this parameter from the CLP by issuing: `get dbm cfg`
If you used a service name instead of the port number in your `catalog tcpip` command, make sure the entry is correct in the `services` file at the client machine.
`netstat -a -n` Shows all connections and port numbers and their status. Issue this from your command prompt.	`netstat -a -n` Shows all connections and port numbers and their status. Issue this from your command prompt.

Notes:

DB2COMM and SVCENAME are set up automatically during the installation of DB2 for the default instance. Any other new instance created after installation with the db2icrt command, will not have these parameters set up.
DB2COMM only needs to be set up at the server.
DB2 will check the services file in the machine where the DB2 command is issued.
Scenario 1 is also applicable when DB2 Linux/UNIX/Windows is installed on a mainframe running Linux operating system as opposed to OS/390 ® or z/OS operating systems.

Scenario 2 - DB2 Linux/UNIX/Windows client to DB2 for OS/390 and z/OS server

From DB2 Linux/Unix/Windows machine to DB2 for OS/390 and z/OS machine. (DB2 Connect must be installed on Linux/Unix/Windows machine.)

Figure 2. DB2 Linux/Unix/Windows to DB2 for OS/390 and z/OS server
Scenario 2

Machine 1 ('aries') Linux/UNIX/Windows	Machine 2 ('bigblue') DB2 for OS/390 and z/OS
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
db2 catalog tcpip node bigbnode remote bigblue.ca.ibm.com server 446 Notes: 'bigbnode' is an arbitrary name chosen for the node. The IP address can be used instead of the hostname A service name defined in the `services` file of this client with a value of 446 can be specified instead of the port number itself.	For this example: bigblue.ca.ibm.com = Host name of machine 2 446 = The port used for DB2 Port 446 is normally the default value. To find out the port used, contact your DB2 for OS/390 and z/OS DBA who can check the MVS syslog for message DSNL004I. "TCPPORT" in that message contains the port to use. Also, the `-DISPLAY DDF` command provides this info.
db2 catalog db d42d1 at node bigbnode authentication dcs	'd42d1' is the name you will use to connect to the DB2 for z/OS and OS/390 subsystem. This name is chosen arbitrarily.
db2 catalog dcs db d42d1 as s390loc	's390loc' = The LOCATION NAME of the DB2 subsystem you want to access on machine 2. To find out the LOCATION NAME, contact your DB2 for OS/390 DBA who can check the MVS syslog for message DSNL004I. "LOCATION" in that message contains the LOCATION NAME to use. Also, the `-DISPLAY DDF` command provides this info.
db2 terminate
db2 connect to d42d1 user <userid> using <password>

What to check if you cannot connect:

Client machine 'aries'	Database server 'bigblue'
`ping bigblue.ca.ibm.com`	bigblue.ca.ibm.com = Host name of Database Server If you cannot ping there may be problems with the DNS. Try pinging the IP address.
`ping 158.228.20.3`	158.228.20.3 = IP address of Database Server This will confirm if there are problems or not with the network.
Make sure you have DB2 Connect Software installed by issuing the command `db2licm -l` from the CLP
	Because the database server is DB2 UDB for OS/390 and z/OS, check the following: Is DB2 started? If not, execute `-start db2` Is DDF started? If not, execute `-start ddf`
If you used a service name instead of the port number in your `catalog tcpip` command, make sure the entry is correct in the `services` file at the client machine.
`netstat -a -n` Shows all connections and port numbers and their status. Issue this from your command prompt.	`netstat` Shows all connections and port numbers and their status. Issue this command from `option 6 Command` in the main ISPF menu.

Note:

In DB2 for OS/390 and z/OS you always connect to the subsystem, not to a specific database; thus, in Figure 2 we do not use the database name 'blue42d1' in any of the commands.

Scenario 3 - DB2 Linux/UNIX/Windows client to DB2 Connect gateway to DB2 for OS/390 and z/OS server

From DB2 Linux/UNIX/Windows machine (client, machine 0) to DB2 Linux/UNIX/Windows machine (with DB2 Connect as a gateway, machine 1) to DB2 for OS/390 and z/OS machine (machine 2).

From machine 1 to machine 2, follow the same instructions as in Scenario 2 . There is no need to issue a catalog database command from the DB2 Connect gateway unless you want to connect from that machine.

Figure 3. DB2 Linux/Unix/Windows client to DB2 Linux/Unix/Windows DB2 Connect gateway to DB2 for OS/390 and z/OS server

Machine 0 ('raven') Linux/UNIX/Windows	Machine 1 ('aries') Linux/Unix/Windows
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 0:
db2 catalog tcpip node gatewayn remote aries.xyz.com server 50010 Notes: 'gatewayn' is an arbitrary name chosen for the node. The IP address can be used instead of the hostname. A service name defined in the `services` file of this client with a value of 50010 can be specified instead of the port number itself.	For this example: aries.xyz.com = Host name of machine 1 50010 = The port used for DB2 To find out the port used, issue this command from the CLP: `get dbm cfg` Then, look for parameter `SVCENAME` . If the value of `SVCENAME` is not the port number but a string, then look in your system for the file `etc/services` and grep for this string, which is normally based on your db2 instance name. (Same explanation as Scenario 1)
db2 catalog db d42d1 at node gatewayn authentication dcs_encrypt	d42d1 = Name of the database you want to access as specified in the `catalog dcs` command in this gateway machine (machine 1).
db2 terminate
db2 connect to d42d1 user <userid for DB2 OS/390 and z/OS> using <password DB2 OS/390 and z/OS>

What to check if you cannot connect:

Client machine 'raven'	Gateway (DB2 Connect Server) 'aries'
`ping aries.xyz.com`	aries.xyz.com = Host name of Gateway Server If you cannot ping there may be problems with the DNS. Try pinging the IP address
`ping 9.82.24.10`	9.82.24.10 = IP address of Gateway (DB2 Connect Server) This will confirm if there are problems or not with the network.
If you used a service name instead of the port number in your `catalog tcpip` command, make sure the entry is correct in the `services` file at the client machine.
	Try connecting from the Gateway to the Database Server (as shown in Scenario 2). If you cannot connect, review your setup according to Scenario 2. If you can connect, then the problem is definitely from the Client to the Gateway, thus review the setup on machine 0 according to Figure 3 .
`netstat -a -n` Shows all connections and port numbers and their status. Issue this from your command prompt.	`netstat -a -n` Shows all connections and port numbers and their status. Issue this from your command prompt.

Scenario 4 - DB2 Linux/UNIX/Windows client to DB2 for iSeries server

From DB2 Linux/UNIX/Windows machine to DB2 for iSeries machine (DB2 Connect MUST be installed on the Linux/UNIX/Windows machine)

Figure 4. DB2 Linux/Unix/Windows client to DB2 for iSeries server

Machine 1 ('aries') Linux/UNIX/Windows	Machine 2 ('big400') DB2 for iSeries
Commands to run on this machine:	Information you need to obtain from this machine, to perform the commands on machine 1:
db2 catalog tcpip node bigrnode remote big400.ca.ibm.com server 446 Notes: 'big4node' is an arbitrary name chosen for the node. The IP address can be used instead of the hostname. A service name defined in the `services` file of this client with a value of 446 can be specified instead of the port number itself.	For this example: big400.ca.ibm.com = Host name of machine 2 446 = The port used for DB2 Port 446 is the default value. In DB2 for iSeries, there is only one database per iSeries machine, thus it is very unlikely the port is changed.
db2 catalog db my400db at node big4node authentication dcs	'my400db' is the name you will use to connect to the DB2 iSeries database. This name is chosen arbitrarily.
db2 catalog dcs db my400db as torisc6	torisc6 = The local RDB Name In order to determine the local RDB name contact your iSeries administrator who can issue the command: `WRKRDBDIRE` When the `'Work with Relational Database Directory Entries'` panel appears, the administrator can find the desired value in column `'Relational Database'` that maps to the column `'Remote Location'` with a value of `'*LOCAL'` .
db2 terminate
db2 connect to my400db user <userid as defined on server machine 2> using <password as defined on server machine 2>

Resources

Download the 0301chong2.pdf (PDF 28 KB)

About the author

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=13323

ArticleTitle=DB2 Version 8 Connectivity Cheat Sheet

publish-date=01232003

author1-email=rfchong@ca.ibm.com

author1-email-cc=