Tuesday, December 9, 2014

How to connect to DB2 database remotely

Catalog the node


Syntax: db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT>


C:\Program Files\IBM\SQLLIB\BIN>db2 catalog tcpip node server1 remote 192.168.6.141 server 50000
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Catalog the database


Syntax:  db2 catalog database <database_name> at node <node_name>


C:\Program Files\IBM\SQLLIB\BIN>db2 catalog database sampledb at node server1
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Refresh directory


C:\Program Files\IBM\SQLLIB\BIN>db2 terminate
DB20000I  The TERMINATE command completed successfully.

Connect to database


Syntax: db2 connect to <database_name> user <user_name> (you will then be prompted for a password)


C:\Program Files\IBM\SQLLIB\BIN>db2 connect to sampledb user db2admin using P@ssw0rd

   Database Connection Information

Database server        = DB2/NT64 9.7.6
SQL authorization ID   = DB2ADMIN
Local database alias   = SAMPLEDB

C:\Program Files\IBM\SQLLIB\BIN>db2 list tables

Table/View                      Schema          Type  Creation time

------------------------------- --------------- ----- --------------------------


  0 record(s) selected.

Verify catalog database


 
C:\Program Files\IBM\SQLLIB\BIN>db2 list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias                       = SAMPLEDB
Database name                        = SAMPLEDB
Node name                            = SERVER1
Database release level               = d.00
Comment                              =
Directory entry type                 = Remote
Catalog database partition number    = -1
Alternate server hostname            =
Alternate server port number         =

C:\Program Files\IBM\SQLLIB\BIN>db2 list node directory show detail

Node Directory

Number of entries in the directory = 1

Node 1 entry:

Node name                      = SERVER1
Comment                        =
Directory entry type           = LOCAL
Protocol                       = TCPIP
Hostname                       = 192.168.6.141
Service name                   = 50000
Remote instance name           =
System                         =
Operating system type          = None