DB2 Commands

db2 activate db <dbsid>                               #Activate Database
db2 backup database <sid>                          #Take an offline backup to specified location
"disk:\location"
db2 connect to <db2sid>                              #Establish connection to an instance
db2 deactivate <dbsid>                              #Deactivate an active database
db2 drop database <target db2sid>              #Delete and instance
db2 force application all                              #Close all applications that uses DB2 Database.
db2 get db cfg for <db2 sid>                       #Display configuration parameter of an instance
db2 get dbm cfg                                          #Display configuration parameter of database manager.
db2 list tablespaces show detail                  #Displays table space information
db2 list utilities show detail                        #Display Database backup status
db2 restore db <sid> from “disk:\path”      # Restore database from a backup image
replace history file
db2 rollforward db <db2sid> query status  #Display rollforward status
db2 rollforward db <SID> to end of logs  #Apply all pending logs
db2 terminate                                              #Close the database connection
db2 update db cfg for <db2 sid>                 #Change value of a instance configuration parameter.
using <parameter_name> <new value>
db2 update dbm cfg using <parameter_name>  #Change value of a database manager configuration parameter.
<new value>
db2_kill -all                                                        #Kill a hanged instance
db2cc                                                              #Open DB2 Control Centre
db2cmd                                                              #Open DB2 Command line Tool
db2level                                                             #Display DB2 version and fix pack level
db2licm -l                                                          #View license information
db2start                                                             #Start Database Normaly
db2stop                                                            #Stop Database Normaly
db2stop force                                                   #Stop Database forcely
db6level                                                            #Display DB2 Client Version


db2admin stop                                                 #stops the db2 administration server instance
db2admin starts                                               #starts the db2 administration server instance
db2 list applications                                       #shows the current connections (for more
                                                                           detail add the ‘show detail’ parameter)
db2 connect to <dbname>                             #connects to the database named in <dbname>
db2 connect reset                                           #closes the database connection held by that user

db2 update db cfg for                                     #To Update Db parametre with new value
<dbname> using <param> <new setting>
db2 backup db <dbname> to                           #very simplistic backup DB command
 <bk_dir> with 4 buffers
db2 backup db <dbname>                              #Simple offline backup, do stopsap and db2start
use TSM
db2 backup db <dbname>                              #Simple online backup using TSM
online use TSM
db2 backup db <dbname> tablespace <tablespace name> online use TSM
db2 list history backup all for <SID>| more                #lists history for the backups
 
db2 restore db <sid> from . taken at <timestamp>     #restore from disk

This will put the database in ROLLFORWARD
state
db2 restore db <sid> use TSM taken at <timestamp>

Rollforward

db2 rollforward db <sid> to <time to recover to>

To get the rollforward status

db2 rollforward db <sid> query status

Alter Tablespaces

db2 “alter tablespace <?> extend (all containers <no of pages>)”

To run stats for all tables


dmdb6srp –n <SID> -t ALL

Stop the Db2-License-Daemon-Process                          'db2licd end'                                                  
'ps-ef   grep db2licd' (for checking)                                

Install the DB2-License                                                   db2licm -a <RDBMS-CD-Path>\db2udbee.lic'
                                                                                           'db2licm -l' (for checking)
                                                                             
                                       
Query to check table size in Db2

db2 "select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as TAB_SIZE from
syscat.tables a, syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID ORDER BY
TAB_SIZE desc"|more

The db2 diagnostic log for SAP instances is usually stored under <instance home>/db2dump/db2diag.log. This directory
will also house any dumps that may occur. If using the std SAP archive exit for DB2, the log and error files for the archives will resides here as well. Simply
view with ‘more’.


To Read db2diag


db2diag -g db:= -gi level=severe


db2diag -g db:= -gi level=error


db2diag -gi "level=error" -H 1d


db2 force application all                                                                  #Terminate all applications


db2stop force                                                                                  # Forcefully stopping database


db2level                                                                                       # Current version and fix pack details


db2 list db directories                                                                   #  Directories used by database


db2 list utilities show detail                                                            #Running utilities like backup/restore/runstat


db2 list history backup all for <DBsid>                                       # Backup history of database

db2 list history backup all for <DBSID> | more                            #Lists history for the backups

db2 restore db <dbname>                                                               # Database restore


db2 " select distinct tabschema from syscat.tables "                      #DB2 Schema name search

db2pd -logs -db <dbname>                                                              # Archive log details


db2 update db cfg for <SID> using LOGARCHMETH1 DISK:/db2/<SID>/log_archive


db2top                                                                                         #DB2 monitoring tool c