Wednesday, February 27, 2013

DB2 command reference


Invoke interpreter:
shell> db2

List available databases:
db2_shell> list database directory

List DB2 shell history:
db2_shell> history
# or
db2_shell> h

Edit and Execute command from DB2 shell history:
# command_num is from the above history command
db2_shell> edit command_num
# or
db2_shell> e command_num

Connect to database from within the db2 interpreter (CLP):
db2_shell> connect to databasename

Connect to database from UNIX shell:
# lasts for duration of current shell session.
db2 connect to database_name

Run SQL command from UNIX shell:
# first connect to database via UNIX shell (above)
# then run the following
db2 -vf scriptname.sql

List tables in database:
db2_shell> list tables

List indexes on a table:
SELECT indschema,INDNAME from syscat.indexes where tabname = 'MYTABLE' and tabschema = 'MYSCHEMA'

Analyze table:
runstats on table schema.tablename

List connections to database:
unix> db2 list applications
# or
db2_shell> list applications
# for detail information
unix> db2 list applications show detail

Get current user:
SELECT CURRENT USER FROM SYSIBM.SYSDUMMY1

Disconnect all connections:
force applications all
#or 
db2 terminate # this might work


Drop database:
drop database databasename

Create database:
create database databasename

Create the explain tables:
connect to databasename
db2 -vtf /opt/ibm/db2/V9.7/misc/EXPLAIN.DDL

Stop DB2:
db2stop

Start DB2: 
db2start

Allow TCP connections (probably unsafe):
update database manager configuration using svcename 3700
get database manager configuration    # verify change
db2set DB2COMM=tcpip
db2stop
db2start
sudo /sbin/iptables -A INPUT -p tcp --dport 7300 -j ACCEPT
sudo /sbin/service iptables save
sudo /sbin/service iptables restart

Update config params:
update db cfg for database_name using logfilsiz 8000
 # or
update database configuration for database_name using logprimary 9 logfilsiz 8000
force applications all

Sources:
http://www.tek-tips.com/viewthread.cfm?qid=128876

No comments: