DB2 Commands Cheat Sheet

I am frequently using DB2 for Traveler HA Pools, Connections and Sametime databases and I am very happy with it. The performance and stability is great. In a small environment, there is not very much you have to do after you install DB2 and create databases you need.

But like in any system there are some basic tasks you need to do on a regular basis to ensure that everything runs smoothly. To help me achieve this I have a list of basic commands which I use frequently, I think that everybody who uses DB2 in their environment will know of them:

  • DB2ICRT <NewInstanceName> –> Create new Instance
  • DB2IDROP <InstanceName> –> Deletes the Instance
  • DB2 LIST DB DIRECTORY –> Shows the directory of an instance
  • DB2 LIST DB DIRECTORY ON D:\ –> Shows the directory of an instance only on D: Drive
  • DB2 CREATE DB <dbName> –> creates Database
  • DB2 CREATE DB <dbName> ON E:\ –> Creates Database on E: Drive
  • DB2 DROP DB <dbName> –> Deletes a Database
  • db2set -all –> Lists important paths and configuration parameters
  • db2 get dbm cfg –> Shows the database manager configuration
  • db2 get db cfg –> Shows a database specific configuration
  • db2 deactivate database <dbname> –> deactivate a database
  • db2 activate database <dbname> –> activate a database
  • backup database “traveler” online to “C:\Backup” compress include logs without prompting  –> Backup (Online, including logs, with compression)

Commands for HADR mode

  • db2pd -db <dbname> -hadr –> Shows the HADR database state
  • db2 takeover hadr on db <dbname> –> Takeover

Status Check

  • db2 get connection state –> Shows if a database is “connectable”
  • db2 list active databases –> Lists all active databases
  • db2 list applications –> Lists all Applications which are currently connected to the databases

  • db2 prune history yyyymmdd and delete –> Deletes achive logs
  • db2 list history backup all for db “db_name”
    –> Lists database backup history and the locations of the backup files

Version check

  • db2licm -v
  • db2licm -l
  • db2level

Sure, you can do all this by using the IBM Data Studio, which is free to use, but sometimes you can be a lot faster by using the command line.

If you are missing some important commands which you are using frequently, please feel free to mention them in comments, I will then include them in the list.