Useful DB2 Commands
The following are DB2 commands I find useful but often forget. I use this
as a reference guide to recall syntax. For detailed explanations, see the
DB2 docs.
- get dbm cfg get the database manager configuration
- get db cfg for dbname get the configuration for a given database
- get monitor switches
- update monitor switches using
- lock on
- sort on
- bufferpool on
- uow on
- table on
- statement on
- get snapshot for dbm
- get snapshot for all on dbname
- reset monitor for db dbname
- list active applications for db dbname show detail (optional)
- list active databases
- describe table schema.table
- create event monitor name for deadlocks write to file '
filename' autostart (optional)
- set event monitor name buffer
- db2evmon -path pathname
Explain Usage
- db2expln is used for static SQL. It runs against the
desired package.
- dynexpln is used for dynamic SQL.
Things to look for in a snapshot
- agents waiting - increase max number of agents
- stolen agent tokens - increase max number of agents
- database files being closed - increase MAXFILOP (MAX FILes OPen)
- LOCKTIMEOUT = -1 should be short, 10 to 15 seconds
- Sort overflow should be less than 1% of total sorts - The sort
heapsize can be increased (band-aid). But such large sorts should not
have to be performed.
- If (rows read)/txn > 1000 - This indicates the presence of
table scans. Do a "get snapshot for tables on <db>" to
get the rows read.
- To check bufferpool size: select * from syscat.bufferpools
$Revision: 1.1 $