DB2 commands

Connect to database

sudo su - db2admin

v8host: /home/db2admin==> db2 connect to db001sa user db2admin using foo

   Database Connection Information

 Database server        = DB2/AIX64 9.7.3
 SQL authorization ID   = WASADMIN
 Local database alias   = DAT012SA

List instances

db2ilist

List current instance

v8host: /home/db2admin==> db2 get instance

 The current database manager instance is:  db2con01

List catalog for node

v8host: /home/db2admin==> db2 list node directory show detail

 Node Directory

 Number of entries in the directory = 2

Node 1 entry:

 Node name                      = A1SA12
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = v8DevDb.myco.com
 Service name                   = 50121
 Remote instance name           =
 System                         =
 Operating system type          = None

Node 2 entry:

 Node name                      = POCSA12
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = v8DevDb.myco.com
 Service name                   = 50010
 Remote instance name           =
 System                         =
 Operating system type          = None

Attach to Node

db2 attach to db001saN user db2admin using db2admin1

List tables

db2 list tables [for {user | all | system | schema schema-name}] [show detail]

View table space information

db2 list tablespaces [show detail

Attach to db

db2 attach to db001sa user db2admin using db2admin1

Find DB2 version

db2level
db2licm l

Check for SVCENAME from database manager configuration

db2 get dbm cfg

Update SVCENAME if needed (require restart of db2 instance)

db2 update dbm cfg using SVCENAME DB2_svr20aix

Set TCPIP communication protocol (require restart of db2 instance)

db2set DB2COMM=tcpip

Check registry to TCPIP changes

db2set -a
db2set -lr

Stop Instance

db2stop

Stop (FORCE) Instance

db2stop force

Start Instance

db2start

Create Instance

db2icrt u

Delete Instance

db2idrop

Find all databases for a certain instance

db2 list database directory

Check for active databases

db2 list active databases

Create database

db2 create db

Drop database

db2 drop db

Start/Activate database

db2 activate db

Deactivate database

db2 deactivate db

Delete tables

db2 drop table

Find table structure

db2 describe table

Catalog remote node

db2 catalog tcpip node remote server

Catalog remote database (node must be calatalog first)

db2 catalog db at node

List catalog for db

db2 list db directory show detail

Delete db catalog

db2 uncatalog db dbname

Delete node catalog

db2 uncatalog node nodename

Enable a user account that has been disabled.

db2 connect to sky_stg user db2admin using db2admin1
db2 "update userreg set status=1 where LOGONID='wcsadmin'"

Catalog a remote database

### Sample commands
sudo su - db2admin
db2 catalog tcpip node db001sa remote hostname server 60004
db2 terminate
db2 list node directory show detail
db2 catalog database db001sa at node db001sa
db2 terminate
db2 list database directory
db2 connect to db001sa user db2admin using db2admin1
db2 list tables
db2 "terminate"
db2 "quit"

### Another catalog example db2 catalog tcpip node qasa12 remote s1qa8b.myco.com server 50120
db2 CATALOG DATABASE dqa012sa AT NODE qasa12 AUTHENTICATION SERVER
db2 update cli cfg for section dqa012sa using LockTimeout 300

Set instance for user

/opt/db2/db2_97_03/instance/db2icrt -u db2admin

Install the DB2 client

1. Download IBM DB2 Data Server Runtime Client.

2. Copy client software to target portal host...
   scp ibm_data_server_runtime_client_aix64_v10.5.tar.gz wpsadmin@v8host:/opt/IBM/software

3. Unarchive
   cd /opt/IBM/software
   gunzip ibm_data_server_runtime_client_aix64_v10.5.tar.gz
   tar xvf ibm_data_server_runtime_client_aix64_v10.5.tar

4. Kick off install program...
   ./db2setup

5. When the DB2 Launchpad opens, choose Install a Product.

6. Select the client to install.

   Follow the prompts in the DB2 Setup wizard.

When installation is complete, the IBM data server client is installed in...

/opt/IBM/db2/V10.5

For more info, see: Installing IBM data server clients