In order to connect to a DB2 database that is not on the server or workstation you’re connecting from, you’ll need to make DB2 aware of that database by cataloging it, unless you’re using JDBC type 4 drivers. JDBC type 4 drivers do not require that a database be cataloged, but specify all of the connection information each time a connection is made.
DB2 Client
DB2 databases can be cataloged on DB2 servers or DB2 clients. Before trying cataloging, make sure you at least have the DB2 client installed. DB2 Clients are free (no licensing required). All App servers should have DB2 clients already installed on them. Web servers should not. You can download DB2 Clients here: https://www-304.ibm.com/support/docview.wss?uid=swg27007053
Select the same version and FixPack as the servers you’re connecting to. Then select the proper OS. Finally, select “IBM Data Server Client”. Installation and Instance creation are outside the scope of this post.
Make sure you have all the inputs
You need:
- REMOTE: Short host name with proper entry in hosts file OR Fully qualified host name OR IP address (that is the order of preference if you have all)
- SERVER: Port number the db2 server is listening on. To get this, log into db2 server as the db2 instance owner (frequently db2inst1), and do:
$ db2 get dbm cfg |grep SVCENAME
TCP/IP Service name (SVCENAME) = db2c_db2inst1
$ cat /etc/services |grep db2c_db2inst1
db2c_db2inst1 50001/tcp
- NODENAME: This is a name that you make up. You might want to develop a standard for your company and stick to it.
- DATABASENAME: The name of the database.
Catalog the node
The general form of the syntax for doing this is:
db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT>
For example:
Logged in (su – is fine) as the db2 instance owner or a privileged user on the client:
$ db2 catalog tcpip node prod_ecom remote servername.example.com server 50001
Catalog the database
The general form of the syntax for doing this is:
db2 catalog database <DATABASENAME> at node <NODENAME>
For example:
Logged in (su – is fine) as the db2 instance owner or a privileged user on the client:
db2 catalog database wcs_prod at node prod_ecom
Refresh directory
After cataloging, do a db2 terminate to ensure everything shows up
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
Always test a connection!!
It is very important to test the connection because a typo could cause it not to work. Or there could be network issues preventing connection.
The general form of the syntax for doing this is:
db2 connect to <database_name> user <user_name> (you will then be prompted for a password)
For example:
Logged in (su – is fine) as the db2 instance owner or a privileged user:
db2 connect to wcs_prod user ecrooks
Enter current password for ecrooks: Database Connection Information Database server = DB2/LINUX 8.2.9 SQL authorization ID = ECROOKS Local database alias = WCS_PROD