Common DB2 commands

+

Search Tips   |   Advanced Search

Connect to database (local)

sudo su - wasadmin

s1ad7a: /home/wasadmin==> db2 connect to db001sa user wasadmin 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

s1ad7a: /home/wasadmin==> db2 get instance

 The current database manager instance is:  db2con01

List catalog for node

s1ad7a: /home/wasadmin==> 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                       = s1ad7b.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                       = s1ad7b.myco.com
 Service name                   = 50010
 Remote instance name           =
 System                         =
 Operating system type          = None

Attach to Node

db2 attach to db001saN user wasadmin using wasadmin1

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 wasadmin using wasadmin1

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 wasadmin using wasadmin1
db2 "update userreg set status=1 where LOGONID='wcsadmin'"

Catalog a remote database

### Sample commands
sudo su - wasadmin
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 wasadmin using wasadmin1
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 wasadmin