What version and fixpack of DB2?
db2level
What DB2 products are installed?
Must issue from the install directory (i.e. /opt/IBM/db2/V(VERSIONNO)/install)
db2ls -q -b -a
For Example:
db2ls -q -b /opt/IBM/db2/V10.5 -a
Starting DB2:
db2start
Stopping DB2:
All connections off, DBs are deactivated:
db2stop
DB’s active, connections incoming, but want to force off and stop DB2:
db2stop force
What databases are available?
db2 "LIST DATABASE DIRECTORY"
What databases are available and LOCAL to this machine?
db2 "LIST DATABASE DIRECTORY" | grep -p Indirect | grep alias
*Note: -p command works in AIX only. Otherwise use previous command and
look for "Indirect"
How to catalog a database?
I am going to cheat and tell you to go here: How to Catalog a DB2 Database
What databases are active and online?
db2 "LIST ACTIVE DATABASES"
Activate a database:
db2 "ACTIVATE DATABASE dbname"
Deactivating a database:
(All connections must be removed first, see “force” command):
db2 "DEACTIVATE DATABASE dbname"
Force off all connections to a database:
db2 "FORCE APPLICATION ALL"
Force off specific connections to a database:
db2 "FORCE APPLICATION (pid)"
db2 "FORCE APPLICATION (pid, pid, pid)"
Where “pid” is the “Appl. Handle” in a “LIST APPLICATIONS” command.
How do I connect to a database?
If logged in as ID with CONNECT permission on the database, and local:
db2 "CONNECT TO (dbname)"
If not Instance ID or Remote:
db2 "CONNECT TO (dbname) USER (username)"
This will prompt for a password. If use use the “using” command with password it can be
pulled up in command line recall.
Disconnecting from a database:
db2 "TERMINATE"
Database uptime:
db2pd -
Who is connected to the database?
db2 "LIST APPLICATIONS"
How many connections are there to the database?
db2 "LIST ACTIVE DATABASES" | grep connected
List of tablespaces and their state (verbose output):
db2 "LIST TABLESPACES SHOW DETAIL"
List of tablespaces:
Are they in normal state (abbreviated, Normal State is: 0x0000)?
db2 "LIST TABLESPACES SHOW DETAIL" | grep State
Grant the ability to connect to the database:
An Individual:
db2 "GRANT CONNECT ON DATABASE TO USER (username)"
An OS group:
db2 "GRANT CONNECT ON DATABASE TO GROUP (groupname)"
Grant authorization to do something to a table
(Select from it, delete from it, etc):
db2 "GRANT <SELECT, INSERT, UPDATE, or DELETE> ON (SCHEMA).(TABLENAME) TO
(USER or GROUP) (username or groupname)"
SQL to see what is causing a lock wait:
db2 “SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK,
LOCK_MODE_REQUESTED FROM SYSIBMADM.SNAPLOCKWAIT”
SQL for top 20 SQL by Number of Executions:
db2 “SELECT NUM_EXECUTIONS as EXECUTIONS, AVERAGE_EXECUTION_TIME_S as
TIME_SECONDS, STMT_TEXT as TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL WHERE
(STMT_TEXT like ‘SELECT%’ or STMT_TEXT like ‘select%’) ORDER BY
NUM_EXECUTIONS DESC FETCH FIRST 20 ROWS ONLY”
SQL for top 20 SQL by Execution Time:
db2 “SELECT AVERAGE_EXECUTION_TIME_S as TIME_SECONDS, NUM_EXECUTIONS as
EXECUTIONS, STMT_TEXT as TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL WHERE
(STMT_TEXT like ‘SELECT%’ or STMT_TEXT like ‘select%’) ORDER BY
AVERAGE_EXECUTION_TIME_S DESC FETCH FIRST 20 ROWS ONLY”
Using DB2’s internal monitoring and troubleshooting tool:
db2top -d
Useful options at menu: B Bottleneck; b Bufferpools; D Dyn. SQL; U Locks;
Look at how optimizer runs SQL (Explain Plan):
db2expln -d -f -g -z \; -o ORIG_EXPLAIN.out
Where file.sql is a txt file containing the problem SQL for analysis. The SQL ends with a
semi-colon.
See if DB2 recommends any indexes (DB2 Advise):
db2advis -d -i | tee ORIG_ADVISE.out
Where file.sql is a txt file containing the problem SQL for analysis. The SQL ends with a
semi-colon.
Backing up a database (Offline):
No connections, Database Deactivated:
db2 "BACKUP DATABASE (dbname) TO (/directory) "
Backing up a database (Online):
DB up and active with connections:
db2 "BACKUP DATABASE (dbname) ONLINE TO (/directory)"
See details on a backup file:
(Online or offline, how granular, compressed, log path, etc)
db2ckbkp -h
Processes in memory after a DB2STOP (That may need to be killed):
AIX:
ipcs | grep db2
Linux (as Instance ID):
ipcs
Kill a process in memory after an IPCS:
ipcrm -<q, m, or s> PID
Where is the DB2 Error Log Held?
Default:
(instancehome)/sqllib/db2dump/db2diag.log
To discover where error log is, if custom:
db2 "GET DBM CFG" | grep DIAGPATH
How do I turn on Command Line Recall on AIX or LINUX:
set -o vi
