DB2 Commands : Frequent Used

[Former Member]

Hi All,

Sharing frequent used DB2 commands

DB2
Commands:

db2stop                       stops the DB2 instance

db2start                       starts the DB2 instance

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 <dbname> using <param> <new setting>              To Update Db parametre with new value

db2 backup db <dbname> to <bk_dir> with 4 buffers          very simplistic backup DB command

db2 backup db <dbname> use TSM                               Simple offline backup, do stopsap and db2start

db2 backup db <dbname> online use TSM                   Simple online backup using 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 command

BR Vaibhav