Thursday, June 17, 2010

DB2 Client Server Connectivity and Configuration

In this post i will discuss different scenario of DB2 client connectivity to DB2 server. In each new release of DB2, the DB2 client is re-branded with different name. The table below describe the DB2 9.1, DB2 9.5 and DB2 9.7 client name.

DB2 V9.1

DB2 V9.5

DB2 V9.7

DB2 Client IBM Data Server Client IBM Data Server Client
DB2 Runtime Client IBM Data Server Runtime Client IBM Data Server Runtime Client
IBM DB2 Driver for JDBC and SQLJ IBM Data Server Driver Package IBM Data Server Driver Package
IBM DB2 Driver for ODBC and CLI IBM Data Server Driver Package IBM Data Server Driver Package

Below are link to DB2 Information that gives the details of DB2 Client Type

DB2 Version

InfoCenter Link

V9.1 http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.rn.doc/doc/c0023028.htm
V9.5 http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html
V9.7 http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

In order to connect to DB2 Server, one of this DB2 client needs to be installed. This DB2 client binary then allows the application to connect to the DB2 server. We will present two Case of DB2 client connectivity to DB2 Server.

Requirement for setup:

1)  You need to set service port on the DB2 Server through which the DB2 Client communicate with DB2 Server. On Windows, the entry is recorded in “c:\windows\System32\drivers\etc\services” file. On Linux, the entry is recorded in “/etc/services” file. The service port entry in this file is of the form  
    •   service_name           port_number          /tcp
    •   For Example:-    DB2_test     7777   /tcp
2) Update the DB2 Database Manager configuration SVCENAME with the service port number. The following command is executed to set the SVCENAME
    • update dbm cfg using svcename DB2_test
3) DB2COMM registry variable needs to be set to value “TCPIP”. The DB2 instance needs to be bounced in order reflect the changes made to the DB2COMM registry variable. The following set of command needs to be executed in sequence to make this changes
    • db2set DB2COMM=TCPIP
    • db2stop force
    • db2start
4) On the client, DB2 Client instance must be created if it didnt already exist. This is needed to catalog the remote DB2 database. The client is created using the following command
    • db2icrt –s client {instance_name}
5) On client, the CATALOG DATABASE and CATALOG NODE command must be executed as the DB2 Client Instance owner.

Case 1:  DB2 Client is located on the same server where the DB2 Server is executing


a)  In this DB2 Client binary is NOT needed to be installed since all the files needed to connect to DB2 Server is installed as part of DB2 Server Installation. 

b) If we need to access database with different name then we need to catalog the database with different name. Following command is executed in sequence
    • CATALOG DATABASE {source_db_name} AS {alias_db_name} ON {drive | path}
c) Then connect to the alias using the following command
    • CONNECT TO {alias_db_name}

Case 2: DB2 Client is Located on the Remote Machine which in turn connect to DB2 Server running on different machine.

a) In this the DB2 Client need to be installed on the client machine. 

b) We need to catalog the remote instance. This step is needed to access the remote database created under that instance. Following command are executed
    • CATALOG TCPIP NODE {nodename} REMOTE {hostname OR IP address} SERVER {service_port_set_on_DB2_Server}
c) We then need to catalog the database in order for client to connect to the DB2 server. Following command is executed
    • CATALOG DATABASE {source_db_name} AS {alias_db_name} AT NODE {node_name_from_step_2}
d) Then connect to the alias using the following command
    • CONNECT TO {alias_db_name}




Case 3: Remote application is located on the Remote Machine which want to connect to DB2 Server running on different machine using the JDBC connection.

a) Download the IBM Data Server Driver Packages client from the following website
 http://www-01.ibm.com/support/docview.wss?uid=swg24025865

Download Steps:



   1) Select "Windows 32-bit X86"
   2) Select "IBM Data Server Driver Packages"
   3) Select "fix pack: DSClients-nt32-dsdriver-9.5.500.784-FP005"
   4) Select "Download using your browser (HTTP)"
   5) Select "v9.5fp5_nt32_dsdriver_EN.exe (24.95 MB)"



b) More details on the driver can be read at below link
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0022612.html

c) Driver Installation details can be read at below link
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html


d) Set the CLASSPATH environment variable to {driver_install_path}\java\db2jcc.jar 


e) Connect to DB2 Server using the JDBC URL as described by JAVA documentation.

3 comments:

Harmender dhaka said...

nice post
i got to know about accessing remote server by installing DB2 client program on local machine
here is a link
for people who want to learn
connecting Java Applications with DB2 server using JDBC driver(db2jcc.jar).

Unknown said...

i got permission denied excepton while running db2icrt command
do we need to set any env varialbles
like DB2DIR or DB2INSTANCE
Iam new to db2 and linux

nikkolayebba said...

There may be noticeably a bundle to know about this. I assume you made certain nice points in features also. online gambling