{"id":2480,"date":"2013-09-19T18:53:28","date_gmt":"2013-09-19T10:53:28","guid":{"rendered":"http:\/\/rmohan.com\/?p=2480"},"modified":"2013-09-19T21:09:17","modified_gmt":"2013-09-19T13:09:17","slug":"db2-basics","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2480","title":{"rendered":"DB2 Basics"},"content":{"rendered":"

How to get a snapshot for DB2<\/h2>\n
db2 get snapshot for dynamic sql<\/span> write to file<\/span> on dbname<\/span> | tee<\/span> output.<\/span>file<\/pre>\n
or<\/span><\/p>\n
db2 \"select * from table(<\/span> SNAPSHOT_DYN_SQL(<\/span> \u2018dbname<\/span>',<\/span>-1) as t<\/span>\"<\/pre>\n
[edit<\/span><\/a>]<\/span>How to check DB2 licence<\/span><\/h2>\n
\/export<\/span>\/home\/ldapdb2\/sqllib\/adm<\/span>\/db2licm -l<\/pre>\n
[edit<\/span><\/a>]<\/span>How to get the DB2 version<\/h2>\n
db2level<\/pre>\n
[edit<\/span><\/a>]<\/span>How to make sure DB2 starts and works with WAS on AIX.<\/h2>\n
Modify startServer<\/span>.<\/span>sh – add<\/p>\n
. ~db2inst1\/sqllib\/db2profile<\/pre>\n
or<\/span> add the lines below to the file \/etc\/profile, and .dtprofile<\/span>:<\/p>\n
#The following three lines have been added by UDB DB2.\r\nif<\/span> [ -f \/home\/wasadmin<\/span>\/sqllib\/db2profile ]; then\r\n. \/home<\/span>\/wasadmin<\/span>\/sqllib\/db2profile\r\nfi<\/span><\/pre>\n
[edit<\/span><\/a>]<\/span>How to move a DB2 database<\/h2>\n
chown<\/span> ldapdb2 \/ldapfs<\/span>\r\nsu<\/span> - ldapdb2\r\ndb2start\r\ndb2 force application all\r\ndb2 termincate<\/span>\r\ndb2 backup db<\/span> ldapdb2 to \/Another_filesystem\r\ndb2 drop db<\/span> ldapdb2\r\ndb2 create db<\/span> ldapdb2 on \/ldapfs<\/span>\r\ndb2 force application all\r\ndb2 termincate<\/span>\r\ndb2 restore db<\/span> ldapdb2 from \/Another_filesystem replace existing redirect<\/span>\r\ndb2 \"set tablespace containers for 3 using (path '\/disks\/1\/3', path '\/disks\/2\/3', path '\/disks\/3\/3', path '\/disks\/4\/3', path '\/disks\/5\/3')\"\r\ndb2 restore db<\/span> ldapdb2 continue<\/pre>\n
Additionally, I recommend you to set the path to the DB2 log file directory on another file system to eliminate output I\/O wait time as follows:<\/p>\n
db2 update database configuration for ldapdb2 using newlogpath<\/span> [path]<\/pre>\n
[edit<\/span><\/a>]<\/span>How to stop db2 instance when it is still active<\/h2>\n
Often I met this problem when I want to stop the db2 instance using<\/p>\n
db2stop\r\nSQL1025N The database manager was not stopped because databases are still active.<\/pre>\n
Here you can find a solution<\/a><\/p>\n
This appendix describes the necessary steps to stop and start a DB2 instance. There are many ways to stop and start a DB2 instance, but the following steps will guide you to stop a DB2 instance to ensure that any defunct DB2 processes, interprocess communications, and defunct DARI processes have been removed successfully.<\/p>\n
Current configuration:<\/span>Instance: db2inst1 Database: sample Server: phantom<\/p>\n
Stop the DB2 instance. Check existing applications that are currently connected to the database by logging on to phantom server<\/span> as DB2 instance owner db2inst1:<\/p>\n
$ db2 list applications\r\n\r\nAuth Id   Appl. Name   Appl.. Handle   Appl. Id                       DB Name   # of Agents\r\n-------   ----------   -------------   ----------------------------   -------   -----------\r\nDB2INST1  db2bp        207             *LOCAL.<\/span>db2inst1.<\/span>010824003917   SAMPLE    1\r\nDB2INST1  java<\/span>         276             CCF21FFC.E5D8.010829004049     SAMPLE    1\r\nDB2INST1  java<\/span>         51              CCF21FFC.E5D9.010829004051     SAMPLE    1<\/pre>\n
If there is any application connected to the database, you can tell who is currently connected and from which location they are connected. In this case, there is one local connection from db2inst1 user ID, and there are two remote connections from IP address: xxx.<\/span>xxx.<\/span>31.252 converted from hex to decimal: CCF21FFC.<\/p>\n
For remote connections, after you get the IP address, you can get the hostname by issuing the nslookup command:<\/p>\n
$ \/usr\/sbin\/nslookup<\/span> xxx.<\/span>242.31.252\r\n\r\nServer:  charter.xxx.com\r\nAddress:  xxx.<\/span>242.31.83\r\n\r\nName:    phantom.xxx.com\r\nAddress:  xxx.<\/span>242.31.252<\/pre>\n
If there are any applications connected to the database, verify that they are not currently executing:<\/p>\n
$ db2 list applications show detail | egrep<\/span> -i<\/span> \"executing|pending\"<\/pre>\n
If there are applications executing or pending, you can now force them off. Then verify to make sure there is no application connected to the database. If you see the following message, you’re ready to stop the DB2 instance:<\/p>\n
$ db2 force application all\r\n\r\nDB20000I  The FORCE APPLICATION command completed successfully.\r\nDB21024I  This command is asynchronous and may not be effective immediately.\r\n\r\n$ db2 list applications\r\n\r\nSQL1611W  No data was returned<\/span> by Database System Monitor.  SQLSTATE=00000<\/pre>\n
Now you can stop the DB2 instance. When you get the message “SQL1064N DB2STOP processing was successful” you’re ready to do the next step. If you get the message below, you must start this step again:<\/p>\n
$ db2stop\r\n\r\nSQL1025N  The database manager was not stopped because databases are still active.<\/pre>\n
LAST RESORT. If for some reason you cannot stop the DB2 instance or DB2 commands are hung, you must run this utility to remove the DB2 engine and client’s IPC resources for that instance. This is your lifesaver:<\/p>\n
$ ipclean<\/span>\r\n\r\nipclean<\/span>: Removing DB2 engine and client's IPC resources for db2inst1.<\/pre>\n
Stop the DB2 Administration Server instance. Skip this step if DB2 Admin instance is not running; otherwise, execute this command:<\/p>\n
$ db2admin stop<\/pre>\n
Remove defunct DARI processes, DB2 background processes, or other defunct threads. List all DB2 processes for this instance:<\/p>\n
$ ps -ef | grep db2\r\n\r\ndb2as 23797 23796  0   Aug 28 ?        0:00 db2sysc\r\ndb2as 23800 23798  0   Aug 28 ?        0:00 db2sysc\r\ndb2inst1 22229     1  0 13:08:01 pts\/5    0:00 \/db2\/dbhome<\/span>\/db2inst1\/sqllib\/bin\/db2bp 20580 5\r\ndb2as 23802 23797  0   Aug 28 ?        0:00 db2sysc\r\ndb2as 23801 23797  0   Aug 28 ?        0:00 db2sysc\r\ndb2as 23799 23797  0   Aug 28 ?        0:00 db2sysc<\/pre>\n
From the list above, we notice that there are processes belonging to the DB2 Admin services<\/span> instance, so you must leave them alone. There is only one process that belongs to db2inst1, and that is a DB2 background process that did not get cleaned up after executing ipclean<\/span>. Get the PID number and kill that process:<\/p>\n
$ kill -9 22229<\/pre>\n
Most of the time, you will see many defunct processes, and to save time, you should execute the following command instead of executing the kill -9 ${<\/span>PID} command many times:<\/p>\n
$ ps -ef | grep<\/span> db2inst1 | awk<\/span> '{print \"kill -9 \"$2}' > \/tmp\/kpid<\/span>\r\n$ chmod<\/span> +x \/tmp\/kpid<\/span>\r\n$ \/tmp\/kpid<\/span><\/pre>\n
Verify that no defunct processes are left. Repeat this step if necessary:<\/p>\n
$ ps -ef | grep db2inst1<\/pre>\n
Remove defunct interprocess communication segments.<\/p>\n
List all memory segments:<\/p>\n
$ ipcs<\/span> -am | grep<\/span> db2inst1<\/pre>\n
IPC status from  as of Thu Aug 30 13:16:55 2001\r\nT      ID            KEY            MODE           OWNER          GROUP\r\nShared Memory:\r\nm      9910          0x74006380     --rw-rw-rw-    db2inst1       db2grp\r\nm      59714         0x61006380     --rw-------    db2inst1       db2grp<\/pre>\n
From the list above, you notice that there are two memory segments that were not removed when executing ipclean<\/span>. You must remove them manually:<\/p>\n
$ ipcrm<\/span> -m 9910\r\n$ ipcrm<\/span> -m 59714<\/pre>\n
List all semaphore segments:<\/p>\n
$ ipcs<\/span> -as | grep<\/span> db2inst1<\/pre>\n
IPC status from  as of Thu Aug 30 13:16:55 2001\r\nT      ID          KEY          MODE         OWNER        GROUP\r\nShared Memory:\r\ns      1900549     0x74006380   --ra-ra-ra-  db2inst1     db2grp   1\r\ns      1310727     00000000     --ra-ra----  db2inst1     db2grp   1\r\ns      2031624     0x73006380   --ra-ra-ra-  db2inst1     db2grp   1<\/pre>\n
From the list above, notice that there are three semaphore segments that were not removed after executing ipclean<\/span>. You must remove them manually:<\/p>\n
$ ipcrm -s 1900549\r\n$ ipcrm -s 1310727\r\n$ ipcrm -s 2031624<\/pre>\n
List all message queue segments:<\/p>\n
$ ipcs -aq | grep db2inst1\r\n\r\nIPC status from  as of Thu Aug 30 13:16:55 2001\r\nT           ID         KEY         MODE         OWNER     GROUP\r\nShared Memory:\r\nq           1572868    0x01dadd16  -Rrw-------  db2inst1  db2grp 65535\r\nq           901125     0x01eba5ed  --rw-------  db2inst1  db2grp 65535\r\nq           1609739    00000000    --rw-------  db2inst1  db2grp 65535\r\nq           659468     00000000    -Rrw-------  db2inst1  db2grp 65535<\/pre>\n
From the list above, notice that there are four message queue segments that were not removed after executing ipclean<\/span>. You must remove them manually:<\/p>\n
$ ipcrm<\/span> -q 1572868\r\n$ ipcrm<\/span> -q 901125\r\n$ ipcrm<\/span> -q 1609739\r\n$ ipcrm<\/span> -q 659468<\/pre>\n
Verify that there are no defunct interprocess communications left. Repeat this step if necessary:<\/p>\n
$ ipcs<\/span> -a | grep<\/span> db2inst1<\/pre>\n
Before you start the DB2 instance, it is best practice to back up the previous db2diag.log, any event logs, notification log, and the associated trap files, and start with a fresh copy. Move the current db2diag.log to the backup directory:<\/p>\n
$ mkdir -p \/db2\/backup\/db2inst1\/diaglogSep12\r\n$ cd<\/span> \/db2\/dbhome<\/span>\/db2inst1\/sqllib\/db2dump\r\n$ mv<\/span> db2diag.log \/db2\/backup\/db2inst1\/diaglogSep12\/\r\n$ mv<\/span> db2eventlog* \/db2\/backup\/db2inst1\/diaglogSep12\/\r\n$ mv<\/span> db2inst1.<\/span>nfy<\/span> \/db2\/backup\/db2inst1\/diaglogSep12\/\r\n$ touch db2diag.log db2inst1.<\/span>nfy<\/span> db2eventlog.<\/span>nnn where nnn is the database partition number\r\n$ chmod 664 db2diag.log db2inst1.nfy db2eventlog.*<\/pre>\n
If there are any trap files, group them together:<\/p>\n
$ cd<\/span> \/db2\/dbhome<\/span>\/db2inst1\/sqllib\/db2dump\r\n$ tar -cvf \/db2\/backup\/db2inst1\/diaglog\/trapAug292001.tar t* c* l* [0-9]*<\/pre>\n
Or execute this keepDiagLog<\/span>.<\/span>sh script:<\/p>\n
#!<\/span>\/bin\/ksh\r\n#\r\n# Clean up db2diag.log, trap files, dump files, etc<\/span>\r\n#\r\n# Usage:  keepDiagLog<\/span>.<\/span>sh\r\n#\r\n# Execute as DB2 instance owner\r\n#\r\nLOGTIME=`date '+%y%m%d%H%M%S'`\r\nDIAGDIR=${<\/span>HOME}<\/span>\/sqllib\/db2dump\r\ntypeset<\/span> instname=${<\/span>1-db2inst1}\r\ntypeset<\/span> ROOTDIR=${<\/span>2-\/dbbackup<\/span>}\r\ntypeset<\/span> dbname=${<\/span>3-sample}\r\ntypeset<\/span> OLDDIR=${<\/span>4-${<\/span>ROOTDIR}<\/span>\/${<\/span>instname<\/span>}<\/span>\/${<\/span>dbname}<\/span>\/db2diag${<\/span>LOGTIME}}\r\nmkdir<\/span> -p ${<\/span>OLDDIR}\r\ncd<\/span> ${<\/span>DIAGDIR}\r\ncp<\/span> -r * ${<\/span>OLDDIR}<\/span>\/\r\nfor<\/span> j in `ls<\/span>`\r\ndo<\/span>\r\n  if<\/span>  [ -d \"${<\/span>j}\" ]; then\r\n    rm<\/span> -r ${<\/span>j}\r\n  else<\/span>\r\n    rm<\/span> ${<\/span>j}\r\n  fi<\/span>\r\ndone<\/span>\r\ntouch<\/span> db2diag.log ${<\/span>instname<\/span>}.<\/span>nfy<\/span>\r\nchmod<\/span> 666 db2diag.log ${<\/span>instname<\/span>}.<\/span>nfy<\/span>\r\nexit<\/span> 0\r\n# You need to add the steps for the event log files based on the\r\n# number of database partitions defined on your server.<\/pre>\n
Now you’re ready to start the DB2 instance. Start the DB2 instance:<\/p>\n
$ db2start\r\n\r\nSQL1063N  DB2START processing was successful.<\/pre>\n
And you’re ready to start the DB2 Admin instance. Start the DB2 Admin instance:<\/p>\n
$ db2admin start<\/pre>\n
Verify the database connection.<\/p>\n
Connect to the sample database:<\/p>\n
$ db2 connect to sample\r\n\r\n   Database Connection Information\r\n Database server        = DB2\/SUN 8.1.0\r\n SQL authorization ID   = DB2INST1\r\n Local database alias   = SAMPLE<\/pre>\n
Disconnect from the sample database:<\/p>\n
 $ db2 terminate<\/pre>\n
Reactivate the database to improve performance.<\/p>\n
Activate the sample database:<\/p>\n
$ db2 activate database sample<\/span>\r\nDB20000I  The ACTIVATE DATABASE command completed successfully.<\/pre>\n","protected":false},"excerpt":{"rendered":"
How to get a snapshot for DB2 db2 get snapshot for dynamic sql write to file on dbname | tee output.file <\/p>\n
or<\/p>\n
 db2 “select * from table( SNAPSHOT_DYN_SQL( \u2018dbname’,-1) as t” [edit]How to check DB2 licence \/export\/home\/ldapdb2\/sqllib\/adm\/db2licm -l [edit]How to get the DB2 version db2level [edit]How to make sure DB2 starts and works with WAS […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2480"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2480"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2480\/revisions"}],"predecessor-version":[{"id":2481,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2480\/revisions\/2481"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}