Friends,

 

Here i am sharing some of my DB2 commands used by me in times of need which also can help others in case searching for the exact command. I know everyone can find the relevant/required commands & their functionalities on IBM Portal. But having at one place all the command helps to run task without bothering where to search & browse for the exact command.

 

 

1. This is used just after you open the DB2 Command Line Processor or Command Line Editor :-

 

          ” connect to SID

 

2. Connect command to different user (OS-Login) rather than the logged in OS User :-

 

          ” connect to SID user < OS User name > using < password of that OS User >

 

3. Stopping DB2 various methods command :-

 

  • db2stop
  • db2stop immediate
  • db2stop force

 

4. Starting DB2 :-

 

          ” db2start

 

5. Initializing DB2(if required) :-

 

          ” db2initialize

 

6. Activates Database Commands( in case required after 1st time installation or other DB Setup-copy job) :-

 

          ” activate db < dbSID >

 

7. Deactivating DB2 :-

 

          ” deactivate db SID

 

8. To check DB2 Current version and fix pack details :-

 

          ” db2level

 

9. Display Tablespace information :-

 

          ” list tablespaces show detail

 

10. Reorg Tables ( All ) :-

 

          ” reorgchk update statistics on table all

 

11. To see the Directory of DB2 :-

 

  • list db directory
  • list db directory on < path given in local Database Directory option >

 

12. Restarting Database at one-shot :-

 

          ” restart database < SID >

 

13. To see the current DB2 configuration, various steps :-

 

  • get dbm cfg
  • get database configuration
  • get db cfg for < SID here its dbSID >

 

14. To reset all database configuration :-

 

          ” reset database configuration

 

15. See the archive-log files that were copied :-

 

          ” list history all for db < SID >

 

16. Prune Command to clear up temporary arch(history) files from DB memory :-

 

  • prune history < time-stamp which is here as YYYYMMDDHHMMSS >
  • prune history < current time-stamp > and delete


17. To check the available backups :-

 

  • list history backup since < current time-stamp > for db < SID >
  • list history backup all for < SID >

 

18. Majorly Used various Online Backup commands :-

 

         Normal process= ” backup db < SID > online to < backup target location >

         With Compress-mode Enabled= “ backup db < SID > online to < disk location path > compress

         If Login to Administrator, so to use the command as say DB2SID login, then, it would be like=

          ” backup db < SID > user < OS User which is db2SID here> using < password of that OS-user > online to < backup location >

 

19. Majorly Used various Offline Backup commands :-

 

          ” backup db < SID > to < :\disk location >

          ” backup database < SID > to < :\disk location >

          ” backup db < SID > to < disk location also can be the tape library location as per user choice>

          ” backup db < SID > to < disk location path > compress

    

     So to take an offline backup for the current running/online database steps would be as below :-

 

          “db2stop

     then “db2start

     & then ” backup database < SID > user < OS-User > using < that OS User password > to < disk location like my case is to tape library \\.\tape0 >


20. Backup Restore various steps :-

 

  • restore db < SID > from < disk location where the backup is present > taken at < time-stamp > “, Here Time-stamp can be checked through “db2ckrst
  • restore db < SID > from ” :\disk_path ” replace history file
  • restore db < SID > incremental taken at < time-stamp >

 

21. If going to restore in different system or say for system-copy restore script create & running :-

 

  • Create => db2 RESTORE db <SID> FROM/SAPBACKUP TAKEN AT <time-stamp> REDIRECT GENERATE SCRIPT/db2/db2restore<TARGET SID>.clp
  • Running that script => db2 -tvf db2restore<target SID >.clp

 

22. Restore & Roll-forward :-

 

     Restore= ” restore db <SID > from < disk location which is backup directory > LOGTARGET < dir to which log files will be copied >

     Roll-Forward= ” rollforward db < SID > to end of logs overflow log path < dir to which log files will be copied for this purpose>

 

23. Offline roll-forward to a Point-In-Time different steps :-

 

  • rollforward database < SID > user < OS-User > using < Password of that OS-User > to < time-stamp > and stop
  • rollforward db < SID > to end of logs and complete overflow log path < backup log generation path >
  • rollforward db < SID > to < time-stamp > and stop overflow log path < backup log path for log_arch >
  • rollforward database < SID > to end of backup and complete

24. Taking Online Backup & Restoring that backup total commands sequence :-

 

  • backup database < SID > online compress include logs
  • restore db < SID > logtarget < log generation path location set for log_arch >
  • rollforward database < SID > to end of backup and complete

 

25. Recover database ( This will use the latest backup image & roll-forward to the end of logs using all default values ) :-

 

          ” recover db < SID >

 

26. To Check status/health/quality of the taken Backups :-

 

  • ckbkp <bkp image name >
  • ckbkp < backup_location\backup_image_name >

 

27. Disabling archive log steps for SAP (Recommended to do the tasks through DB2SID Login) :-

 

          1. Stop SAP System.

          2. db2 update db cfg for < SID > using logretain off

          3. db2 update db cfg for < SID > using LOGARCHMETH1 OFF

          4. db2 backup db < DBSID > at <path of bkp location>

          5. db2 update db cfg for < SID > using logretain on

          6. db2 update db cfg for < SID > using LOGARCHMETH1 TSM

          7. Start SAP System

 

Note :- In case DB2 10.1 command goes like :-

          update db cfg using logretain off

 

So, Setting the logs archive to log_archive directory :-
          update db cfg using logarchmeth1 < server hard-disk drive path to the folder log_arch in my case F:\db2\SID\log_arch\DB2SID\SID\NODE0000\C0000000 >

 

Point to be noted here :- In circular login, the db will become/reflect backup pending state & that is why need a OFFLINE BACKUP. With Online Backup here, we can not restore & roll forward.

 

 

LASTLY,

 

28. Steps in sequence to reset SAP* User id for 000 Client through DB2 clp Login & DB2SID as OS-Login ( Also can follow for any Client, if required ) :-

 

     1st Step= connect to SID

     2nd Step= select BNAME, MANDT, UFLAG from SAP< SID actually Schema name here Schema means SID >.USR02 where BNAME = ‘SAP*’

     3rd Step= UPDATE SAP< SID >.USR02 set UFLAG=0 where BNAME=’SAP*’ and MANDT=’000′

               Note:  In German BNAME means User-Name & MANDT means CLIENT

     4th Step= DELETE from SAP<SID>.USR02 where BNAME=’SAP*’ and MANDT=’000′

     5th Step= Try Login to 000 client with SAP* & the default password for that “pass” or Restart SAP & try Login Again with this Login Details

 

 

 

Note :- For Windows:- In all the above matters, in case you do not know to open the db2clp, only know to run normal CMD, then :-

 

Windows+R -> cmd -> db2cmd -> db2

 

Here the last db2 is for having the db2 in-front of your ever db2 commands.

 

This will open up to the db2clp window which will let you to enter db2 commands & execute your task. Its for going to DB2 CLP window.

 

For example, say you want to go to db2 license manager. So the process would be :-

 

          Windows+R -> cmd -> db2cmd -> db2licm

 

which will give you the required options to manage the db2 license, whether to view or manage.

 

 

Hope these are also some of your regularly used DB2 commands.

 

Friends, Once again if you need an edit or would like update some more essential db2 commands, then I would love to hear from your side. You are most welcome.

 

Thanks & Regards,

Kaushik

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply