Run DB2 SQL Scripts on command line

Use this:


1. login as the db user - if on unix use db2profile to set environment
2.  run db2 and connect to the db2 instance
3. quit
4. run the script using
      db2 -tvf < script name>

Note: some may require special options:
db2 +c -td@ -vf <script  name>

The above means auto commit is on, use @ as the delimiter for every SQL in the script.

For more details:
db2 ? options

Create sample unicadb database on DB2 on linux

CREATE DATABASE UNICADB AUTOMATIC STORAGE YES ON '/apps/db2data/unicadb' DBPATH ON '/apps/db2data/unicadb' USING CODESET UTF-8 TERRITORY US PAGESIZE 32 K

CONNECT TO UNICADB

CREATE BUFFERPOOL UNICA32KBPOOL  SIZE 3000 PAGESIZE 32K 

CREATE REGULAR TABLESPACE UNICA PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL UNICA32KBPOOL  AUTORESIZE YES

create bufferpool unicasys32kbpool size 500 pagesize 32K

CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY AUTOMATIC STORAGE BUFFERPOOL unicasys32kbpool

REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

GRANT USE OF TABLESPACE UNICA TO USER UMP WITH GRANT OPTION 

GRANT USE OF TABLESPACE UNICA TO USER UC  WITH GRANT OPTION

GRANT USE OF TABLESPACE UNICA TO USER UMO WITH GRANT OPTION

Installing DB2 Data Studio on Linux - libswt-pi-gtk.so error

If you can the following errors, it means you don't have 32bit GTK components installed:

unica@rhel-server-6 staging]$ ./install
00:00.38 ERROR [main] com.ibm.cic.agent.internal.ui.AgentUI reportStatus
  Could not load SWT library. Reasons:
      /apps/staging/configuration/org.eclipse.osgi/bundles/109/1/.cp/libswt-pi-gtk-4237.so (libgtk-x11-2.0.so.0: cannot open shared object file: No such file or directory)
      swt-pi-gtk (Not found in java.library.path)
      /home/unica/.swt/lib/linux/x86/libswt-pi-gtk-4237.so (libgtk-x11-2.0.so.0: cannot open shared object file: No such file or directory)
      /home/unica/.swt/lib/linux/x86/libswt-pi-gtk.so (/home/unica/.swt/lib/linux/x86/liblibswt-pi-gtk.so.so: cannot open shared object file: No such file or directory)
  java.lang.UnsatisfiedLinkError: Could not load SWT library. Reasons:
    /apps/staging/configuration/org.eclipse.osgi/bundles/109/1/.cp/libswt-pi-gtk-4237.so (libgtk-x11-2.0.so.0: cannot open shared object file: No such file or directory)
    swt-pi-gtk (Not found in java.library.path)
    /home/unica/.swt/lib/linux/x86/libswt-pi-gtk-4237.so (libgtk-x11-2.0.so.0: cannot open shared object file: No such file or directory)
    /home/unica/.swt/lib/linux/x86/libswt-pi-gtk.so (/home/unica/.swt/lib/linux/x86/liblibswt-pi-gtk.so.so: cannot open shared object file: No such file or directory)

    at org.eclipse.swt.internal.Library.loadLibrary(Library.java:331)
    at org.eclipse.swt.internal.Library.loadLibrary(Library.java:240)
    at org.eclipse.swt.internal.gtk.OS.<clinit>(OS.java:22)
    at java.lang.J9VMInternals.initializeImpl(Native Method)
    ...
The displayed failed to initialize.  See the log /apps/staging/configuration/1418895329737.log for details.

To solve:

yum install gtk2.i686 gtk2-engines.i686 PackageKit-gtk-module.i686 PackageKit-gtk-module.x86_64 libcanberra-gtk2.x86_64 libcanberra-gtk2.i686

rdma and log_mtts_per_seg when installing DB2 10.5

I got the following errors when running db2prereqcheck:

Validating "rdma" ...
Found package "rdma" on host "localhost.localdomain".
DBT3566E  The db2prereqcheck utility detected that the service named "rdma" is not enabled on host "localhost.localdomain".
   ERROR : Requirement not matched.


Validating "modprobe.conf" ...
DBT3588W  The db2prereqcheck utility was unable to validate the configuration of the log_mtts_per_seg parameter on the following host machine: "localhost.localdomain". Reason code: "1".
   WARNING : Requirement not matched.


After doing some research, since I am not using the DB2 PureScale features, it should be OK to ignore these errors.

Migrating Lotus Notes to a new laptop (or image)

Nothing to do with DB2 but ... :-)

Here's a good link: http://superuser.com/questions/455872/reinstalling-lotus-notes-what-files-to-save

Here's what I restored:

1. notes\ID file
2. notes\data\archive\*.nsf
3. notes\data\desktop.nsf

To be more complete:

1. desktop.*
2. bookmarks.nsf
3. names.nsf
4. notes.ini
5. ID file.

Useful links on using DB2MOVE

 How to use DB2MOVE

DB2MOVE with examples

Grant DBADM Privilege

1. Connect to DB
db2 => connect reset
DB20000I  The SQL command completed successfully.
db2 => connect to dmdb

   Database Connection Information

 Database server        = DB2/NT64 10.1.4
 SQL authorization ID   = ADMINIST...
 Local database alias   = DMDB


2. Grant privilege
db2 => grant dbadm on database to user db2admin
DB20000I  The SQL command completed successfully.
db2 =>

Using DB2MOVE to move schema1 to schema2 on another database

NOTES:
- Need LOAD privilege - easiest is to grant DBADM privilge


Step 1: Export from Source DB

D:\IBM\Backups\DB2>db2move DMDB export -aw -l lobs -sn sa

Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Tue Jul 08 09:39:22 2014


All schema names matching:  SA;

Connecting to database DMDB ... successful!  Server : DB2 Common Server V10.1.4

Binding package automatically ... D:\IBM\SQLLIB\BND\DB2COMMON.BND ... successful
!

Binding package automatically ... D:\IBM\SQLLIB\BND\DB2MOVE.BND ... successful!

EXPORT: 267324 rows from table "SA      "."ACCOUNT"
EXPORT:      2 rows from table "SA      "."ACCOUNT_TYPE_CODE"
EXPORT: 267324 rows from table "SA      "."ACCOUNT_WACHOVIA"
EXPORT: 520479 rows from table "SA      "."ACCT_PURCH_SUM"
EXPORT:      4 rows from table "SA      "."ACOSESSION"
EXPORT:     28 rows from table "SA      "."ACOSESSIONSTATUS"
EXPORT:      6 rows from table "SA      "."ACO_CONTACTCHANNEL"
EXPORT: 148306 rows from table "SA      "."ACO_CONTACT_LOG"
EXPORT:  20000 rows from table "SA      "."ACO_OPTOUTLIST"
EXPORT:      0 rows from table "SA      "."ACO_PROPOSED_CONTACTS"
EXPORT:     13 rows from table "SA      "."ACO_VERSION"
EXPORT:     50 rows from table "SA      "."AC_PROFILE_FIELDS"
EXPORT:     50 rows from table "SA      "."AGE_RANGE"
EXPORT:      0 rows from table "SA      "."AQ_INBOUND"
EXPORT:      0 rows from table "SA      "."AQ_OUTBOUND"
EXPORT:      5 rows from table "SA      "."BEHAVIOR"
EXPORT:  59922 rows from table "SA      "."BEHAVIORAL_STATE"
EXPORT:      5 rows from table "SA      "."BEHAVIOR_BANKING"
EXPORT:      5 rows from table "SA      "."BEHAVIOR_CREDITCARD"
EXPORT:      5 rows from table "SA      "."BEHAVIOR_INSURANCE"
EXPORT:      7 rows from table "SA      "."CATEGORY"
EXPORT:      7 rows from table "SA      "."CATEGORY_FS"
EXPORT:  74153 rows from table "SA      "."CONTACT_LOG"
EXPORT:     30 rows from table "SA      "."CONTACT_PERIOD"
EXPORT: 199922 rows from table "SA      "."CUSTOMER"
EXPORT: 247852 rows from table "SA      "."CUSTOMER_ACCOUNT"
EXPORT: 199922 rows from table "SA      "."CUSTOMER_CONTACT"
EXPORT: 399844 rows from table "SA      "."CUSTOMER_MODEL_SCORE"
EXPORT: 501148 rows from table "SA      "."CUSTOMER_MODEL_SCORE_DEMO"
EXPORT:    730 rows from table "SA      "."CUSTOMER_PERIOD"
EXPORT: 358907 rows from table "SA      "."CUSTOMER_SUPPRESSION"
EXPORT:    212 rows from table "SA      "."DIMENSIONTABLE"
EXPORT:    503 rows from table "SA      "."DIMENSIONTABLEOLD"
EXPORT:     48 rows from table "SA      "."DIMENSION_WACHOVIA"
EXPORT:      4 rows from table "SA      "."EDUCATION"
EXPORT:      4 rows from table "SA      "."ETHNIC"
EXPORT:      2 rows from table "SA      "."GENDER"
EXPORT: 195605 rows from table "SA      "."HOUSEHOLD"
EXPORT:      0 rows from table "SA      "."HOUSEHOLD_MODEL_SCORE"
EXPORT: 354219 rows from table "SA      "."HOUSEHOLD_SUPPRESSION"
EXPORT:    438 rows from table "SA      "."INCOME_RANGE"
EXPORT:      0 rows from table "SA      "."INDIV"
EXPORT:      0 rows from table "SA      "."INDIV_CONTACT"
EXPORT:      0 rows from table "SA      "."INSURANCE_PRODUCT"
EXPORT:    397 rows from table "SA      "."JCDIMENSIONTABLE"
EXPORT:  22000 rows from table "SA      "."JCTMP"
EXPORT:      8 rows from table "SA      "."LANGUAGE"
EXPORT:      7 rows from table "SA      "."MODEL"
EXPORT:     98 rows from table "SA      "."OCCUPATION"
EXPORT:    944 rows from table "SA      "."OGANIZATION_BANKING"
EXPORT:      8 rows from table "SA      "."ORGANIZATION_"
EXPORT:    944 rows from table "SA      "."ORGANIZATION_CREDITCARD"
EXPORT:    944 rows from table "SA      "."ORGANIZATION_INSURANCE"
EXPORT:    944 rows from table "SA      "."ORGANIZATION_VIEW"
EXPORT:    343 rows from table "SA      "."POT_VALUE_RANGE"
EXPORT: 116586 rows from table "SA      "."PROD"
EXPORT:    325 rows from table "SA      "."PRODS2"
EXPORT:    982 rows from table "SA      "."PRODUCT"
EXPORT:   1018 rows from table "SA      "."PRODUCT_BANKING"
EXPORT:    189 rows from table "SA      "."PRODUCT_CLASS"
EXPORT:    189 rows from table "SA      "."PRODUCT_CLASS_FS"
EXPORT:    982 rows from table "SA      "."PRODUCT_VIEW"
EXPORT:  10000 rows from table "SA      "."PROSPECT"
EXPORT:      5 rows from table "SA      "."RELATIONSHIP"
EXPORT: 165277 rows from table "SA      "."RELATIONSHIP_STATE"
EXPORT:      7 rows from table "SA      "."RELIGION"
EXPORT:    160 rows from table "SA      "."REPORTDIMENSIONTABLE"
EXPORT:   2764 rows from table "SA      "."RESPONSE"
EXPORT:    290 rows from table "SA      "."RESPONSE_PERIOD"
EXPORT:      0 rows from table "SA      "."RESPONSE_TYPE"
EXPORT: 199922 rows from table "SA      "."RETAILPROD"
EXPORT: 533207 rows from table "SA      "."SALES"
EXPORT:      4 rows from table "SA      "."SALES_CHANNEL"
EXPORT:     72 rows from table "SA      "."SALES_GROUP"
EXPORT: 533207 rows from table "SA      "."SALES_ITEM"
EXPORT: 533207 rows from table "SA      "."SALES_ITEM_VIEW"
EXPORT:    718 rows from table "SA      "."SALES_PERIOD"
EXPORT:    944 rows from table "SA      "."SALES_RESOURCE"
EXPORT:      2 rows from table "SA      "."SCORE_PERIOD"
EXPORT:      2 rows from table "SA      "."SCORE_TYPE"
EXPORT:      3 rows from table "SA      "."SEED"
EXPORT:     34 rows from table "SA      "."SUB_CATEGORY"
EXPORT:     34 rows from table "SA      "."SUB_CATEGORY_FS"
EXPORT:      7 rows from table "SA      "."SUPPRESSION"
EXPORT:   7956 rows from table "SA      "."TEMPORARY"
EXPORT: 267404 rows from table "SA      "."TEMP_DAN_ACCOUNT"
EXPORT: 199922 rows from table "SA      "."TEMP_DAN_CUSTOMER"
EXPORT:      0 rows from table "SA      "."TESTTWO"
EXPORT:    427 rows from table "SA      "."TRANSACTION_MART"
EXPORT: 199922 rows from table "SA      "."VALUE_STATE"
EXPORT:      9 rows from table "SA      "."V_ACCT_BAL"
EXPORT:     10 rows from table "SA      "."V_ACCT_BALANCE_DIMENSION"
EXPORT:      2 rows from table "SA      "."V_ACCT_TYPE_DIMENSION"
EXPORT:      8 rows from table "SA      "."V_AGE_DIMENSION"
EXPORT:      3 rows from table "SA      "."V_CUSTOMERVALUE_DIMENSION"
EXPORT:      3 rows from table "SA      "."V_CUSTOMER_CUSTVALUE_DIMEMSION"
EXPORT:      3 rows from table "SA      "."V_CUSTOMER_DAYSLASTPURCH_DIMEN"
EXPORT:      9 rows from table "SA      "."V_CUSTOMER_INCOME_DIMENSION"
EXPORT: 399844 rows from table "SA      "."V_CUSTOMER_MODEL_SCORE"
EXPORT: 199922 rows from table "SA      "."V_CUSTOMER_VALUE"
EXPORT:    301 rows from table "SA      "."V_CUSTOMER_VALUE_DIMENSION"
EXPORT:      3 rows from table "SA      "."V_CUSTOMER_VALUE_M3"
EXPORT:      3 rows from table "SA      "."V_CUSTOMER_VALUE_MONTH_1_DIMEN"
EXPORT:      5 rows from table "SA      "."V_CUSTOMER_VALUE_MONTH_2_DIMEN"
EXPORT:      3 rows from table "SA      "."V_CUSTOMER_VALUE_MONTH_3_DIMEN"
EXPORT:      3 rows from table "SA      "."V_DAYSSINCEPURCH_DIMENSION"
EXPORT:     25 rows from table "SA      "."V_LAST_TRANSACTION_DATE"
EXPORT:    982 rows from table "SA      "."V_PRODUCT_SELECT"
EXPORT:    982 rows from table "SA      "."V_PRODUCT_SELECT_FS"
EXPORT:     50 rows from table "SA      "."V_PROFILE_CUSTOMER_AGE"
EXPORT:      8 rows from table "SA      "."V_PROSPECT_AGE_DIMENSION"
EXPORT:      0 rows from table "SA      "."V_PROSPECT_AVGBALANCE_DIMENSIO"
EXPORT:      0 rows from table "SA      "."V_PROSPECT_AVG_BALANCE_DIMENSI"
EXPORT:     76 rows from table "SA      "."V_PROSPECT_REGION_DIMENSION"
EXPORT:     12 rows from table "SA      "."V_PURCHASES_LAST_12_MONTHS"
EXPORT:     76 rows from table "SA      "."V_REGION_DIMENSION"
EXPORT: 497663 rows from table "SA      "."V_SALES_DETAIL"
EXPORT:     36 rows from table "SA      "."V_SALES_TIME_DIMENSION"
EXPORT:     36 rows from table "SA      "."V_TIME_DIMENSION"
EXPORT:  42195 rows from table "SA      "."ZIPCODES"
EXPORT:  42192 rows from table "SA      "."ZIPNOV99"
EXPORT:  29470 rows from table "SA      "."ZIPS"

Disconnecting from database ... successful!

End time:  Tue Jul 08 09:41:37 2014

D:\IBM\Backups\DB2>

http://www.dbatodba.com/db2/how-to-do/how-to-use-db2move


Step 2: Create TMPDB and grant db2adm to schema2
db2 => CREATE DATABASE tmpdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
SQL1047N  The application is already connected to another database.
db2 => disconnect
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"DISCONNECT".  Expected tokens may include:  "<identifier>".  SQLSTATE=42601
db2 => disc
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "disc".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
db2 => help
DB21052I  "db2ic" has been submitted for execution.
db2 => connect reset
DB20000I  The SQL command completed successfully.
db2 => CREATE DATABASE tmpdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
DB20000I  The CREATE DATABASE command completed successfully.
db2 => connect to tmpdb

   Database Connection Information

 Database server        = DB2/NT64 10.1.4
 SQL authorization ID   = ADMINIST...
 Local database alias   = TMPDB

db2 => create regular tablespace datamart pagesize 32k
DB20000I  The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE datamart TO USER datamart WITH GRANT OPTION
DB20000I  The SQL command completed successfully.
db2 => REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC
DB20000I  The SQL command completed successfully.
db2 => grant  dbadm on database to user datamart
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.

Step 3: Import into temp DB
Z:\Export\DB2>db2move tmpdb import -io replace_create -l lobs

Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****

Action:  IMPORT

Start time:  Tue Jul 08 11:21:45 2014


Connecting to database TMPDB ... successful!  Server : DB2 Common Server V10.1.4


Binding package automatically ... D:\IBM\SQLLIB\BND\DB2COMMON.BND ... successful
!

Binding package automatically ... D:\IBM\SQLLIB\BND\DB2MOVE.BND ... successful!

* IMPORT:  table "SA      "."ACCOUNT"
  -Rows read:     267324
  -Inserted:      267324
  -Rejected:           0
  -Committed:     267324

* IMPORT:  table "SA      "."ACCOUNT_TYPE_CODE"
  -Rows read:          2
  -Inserted:           2
  -Rejected:           0
  -Committed:          2

* IMPORT:  table "SA      "."ACCOUNT_WACHOVIA"
  -Rows read:     267324
  -Inserted:      267324
  -Rejected:           0
  -Committed:     267324

* IMPORT:  table "SA      "."ACCT_PURCH_SUM"
  -Rows read:     520479
  -Inserted:      520479
  -Rejected:           0
  -Committed:     520479

* IMPORT:  table "SA      "."ACOSESSION"
  -Rows read:          4
  -Inserted:           4
  -Rejected:           0
  -Committed:          4

* IMPORT:  table "SA      "."ACOSESSIONSTATUS"
  -Rows read:         28
  -Inserted:          28
  -Rejected:           0
  -Committed:         28

* IMPORT:  table "SA      "."ACO_CONTACTCHANNEL"
  -Rows read:          6
  -Inserted:           6
  -Rejected:           0
  -Committed:          6

* IMPORT:  table "SA      "."ACO_CONTACT_LOG"
  -Rows read:     148306
  -Inserted:      148306
  -Rejected:           0
  -Committed:     148306

* IMPORT:  table "SA      "."ACO_OPTOUTLIST"
  -Rows read:      20000
  -Inserted:       20000
  -Rejected:           0
  -Committed:      20000

* IMPORT:  table "SA      "."ACO_PROPOSED_CONTACTS"
  -Rows read:          0
  -Inserted:           0
  -Rejected:           0
  -Committed:          0

* IMPORT:  table "SA      "."ACO_VERSION"
  -Rows read:         13
  -Inserted:          13
  -Rejected:           0
  -Committed:         13

* IMPORT:  table "SA      "."AC_PROFILE_FIELDS"
  -Rows read:         50
  -Inserted:          50
  -Rejected:           0
  -Committed:         50

* IMPORT:  table "SA      "."AGE_RANGE"
  -Rows read:         50
  -Inserted:          50
  -Rejected:           0
  -Committed:         50

* IMPORT:  table "SA      "."AQ_INBOUND"
  -Rows read:          0
  -Inserted:           0
  -Rejected:           0
  -Committed:          0

* IMPORT:  table "SA      "."AQ_OUTBOUND"
  -Rows read:          0
  -Inserted:           0
  -Rejected:           0
  -Committed:          0

* IMPORT:  table "SA      "."BEHAVIOR"
  -Rows read:          5
  -Inserted:           5
  -Rejected:           0
  -Committed:          5

* IMPORT:  table "SA      "."BEHAVIORAL_STATE"
  -Rows read:      59922
  -Inserted:       59922
  -Rejected:           0
  -Committed:      59922

* IMPORT:  table "SA      "."BEHAVIOR_BANKING"
  -Rows read:          5
  -Inserted:           5
  -Rejected:           0
  -Committed:          5

* IMPORT:  table "SA      "."BEHAVIOR_CREDITCARD"
  -Rows read:          5
  -Inserted:           5
  -Rejected:           0
  -Committed:          5

* IMPORT:  table "SA      "."BEHAVIOR_INSURANCE"
  -Rows read:          5
  -Inserted:           5
  -Rejected:           0
  -Committed:          5

* IMPORT:  table "SA      "."CATEGORY"
  -Rows read:          7
  -Inserted:           7
  -Rejected:           0
  -Committed:          7

* IMPORT:  table "SA      "."CATEGORY_FS"
  -Rows read:          7
  -Inserted:           7
  -Rejected:           0
  -Committed:          7

* IMPORT:  table "SA      "."V_TIME_DIMENSION"
  -Rows read:         36
  -Inserted:          36
  -Rejected:           0
  -Committed:         36

* IMPORT:  table "SA      "."ZIPCODES"
  -Rows read:      42195
  -Inserted:       42195
  -Rejected:           0
  -Committed:      42195

* IMPORT:  table "SA      "."ZIPNOV99"
  -Rows read:      42192
  -Inserted:       42192
  -Rejected:           0
  -Committed:      42192

* IMPORT:  table "SA      "."ZIPS"
  -Rows read:      29470
  -Inserted:       29470
  -Rejected:           0
  -Committed:      29470


Disconnecting from database ... successful!

End time:  Tue Jul 08 11:46:35 2014

Step 4: Grant db2adm on target database
db2 => connect reset
DB20000I  The SQL command completed successfully.
db2 => connect to dmdb

   Database Connection Information

 Database server        = DB2/NT64 10.1.4
 SQL authorization ID   = ADMINIST...
 Local database alias   = DMDB

db2 => grant dbadm on database to user datamart
DB20000I  The SQL command completed successfully.



Step 5: Run DB2MOVE
Z:\Export\DB2>db2move tmpdb copy -sn sa -co TARGET_DB dmdb USER datamart using passw0rd SCHEMA_MAP ((sa,datamart)) TABLESPACE_MAP ((datamart,datamart))

Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****

Action:  COPY

Start time:  Tue Jul 08 13:06:21 2014


All schema names matching:  SA;

Connecting to database TMPDB ... successful!  Server : DB2 Common Server V10.1.


Copy schema SA to DATAMART on the target database DMDB

Create DMT :  "SYSTOOLS"."DMT_53bb7c4fcaa41"

Start Load Phase :


db2move finished successfully



Files generated:
-----------------
COPYSCHEMA.20140708130621.msg
LOADTABLE.20140708130621.MSG

Please delete these files when they are no longer needed.


End time:  Tue Jul 08 13:11:48 2014

Z:\Export\DB2>


Step 6: Drop TMPDB
D:\IBM\SQLLIB\BIN>db2 connect to tmpdb

   Database Connection Information

 Database server        = DB2/NT64 10.1.4
 SQL authorization ID   = ADMINIST...
 Local database alias   = TMPDB


D:\IBM\SQLLIB\BIN>db2 "quiesce database immediate force connections"
DB20000I  The QUIESCE DATABASE command completed successfully.

D:\IBM\SQLLIB\BIN>db2 "unquiesce database"
DB20000I  The UNQUIESCE DATABASE command completed successfully.

D:\IBM\SQLLIB\BIN>
D:\IBM\SQLLIB\BIN>db2 "force application all"
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.


D:\IBM\SQLLIB\BIN>db2stop force
07/08/2014 13:21:04     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

D:\IBM\SQLLIB\BIN>db2start
07/08/2014 13:21:18     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

D:\IBM\SQLLIB\BIN>db2 "drop database tmpdb"
DB20000I  The DROP DATABASE command completed successfully.

DB2 Instance

From the manual:

database manager instance
(1) A logical database manager environment similar to an image of the actual database manager environment. It is possible have several instances of the database manager product on the same workstation. Use these instances to separate the development environment from the production environment, tune the database manager to a particular environment and protect sensitive information.
(2) The DB2 code that manages data. An instance has its own databases (which other instances cannot access), and all its database partitions share the same system directories. It also has separate security from other instances on the same computer.

Quoted from here:
Take this as you are a client connecting to a server, so you sees a server(database host) as node providing you information about the instance and the database that is under that instance.so you catalog that node and starts taking to that database under that instance.
So any times you want to interface to that database , you must go through that instance(that has its own service name/port that is using to listen to the client).

The client also has its own instance, that provide you code/libraries to do all type of work as a client.

Now you are at machine(local) that hosts a database,so you create a instance(infact a instance is nothing but links to various libraries of the same db2 code and various binaries).
so if you want to connect to an instance you attached to it(attached is nothing more than attaching to shared structure that a running instance will have provided through its ipc daemon/listener).
Once you attached to an instance, you ask that instance to connect me to that database(infact every local database is automatically catalog during the creation).

Now in db2 terminology when you are at the local machines or mutiple machines, you can create partitions that are spread across mutiple physcial machines or as logical partitions at the same machine.These are all database partitions that are sharing the same instance and all the other things are unshared.In version 7 or earliar this database partition was known as node partition(infact all the commands the has node clause also work in V8)

Configuring DB2 with TCPIP

Here's a very good link

db2 dbm cfg

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0f00

 CPU speed (millisec/instruction)             (CPUSPEED) = 3.306410e-007
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+002

 Max number of concurrently active databases     (NUMDB) = 32
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = D:\IBM\SQLLIB\java\jd
k

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = C:\PROGRAMDATA\IBM\DB
2\DB2COPY1\DB2\ $m
 Current member resolved DIAGPATH                        = C:\PROGRAMDATA\IBM\DB
2\DB2COPY1\DB2\DIAG0000\
 Alternate diagnostic data directory path (ALT_DIAGPATH) =
 Current member resolved ALT_DIAGPATH                    =
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 0

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = OFF
   Lock                                   (DFT_MON_LOCK) = OFF
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = OFF

 SYSADM group name                        (SYSADM_GROUP) =
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Client Userid-Password Plugin          (CLNT_PW_PLUGIN) =
 Client Kerberos Plugin                (CLNT_KRB_PLUGIN) = IBMkrb5
 Group Plugin                             (GROUP_PLUGIN) =
 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =
 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Cluster manager                                         =

 Database manager authentication        (AUTHENTICATION) = SERVER
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = D:

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(66)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC(1046109)
 Agent stack size                       (AGENT_STACK_SZ) = 16
 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAG
ENTS)

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(MAX_COORDAG
ENTS)
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time and redo index build  (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) = BASE_WIN
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 Discovery mode                               (DISCOVER) = SEARCH
 Discover server instance                (DISCOVER_INST) = ENABLE

 SSL server keydb file                   (SSL_SVR_KEYDB) =
 SSL server stash file                   (SSL_SVR_STASH) =
 SSL server certificate label            (SSL_SVR_LABEL) =
 SSL service name                         (SSL_SVCENAME) =
 SSL cipher specs                      (SSL_CIPHERSPECS) =
 SSL versions                             (SSL_VERSIONS) =
 SSL client keydb file                  (SSL_CLNT_KEYDB) =
 SSL client stash file                  (SSL_CLNT_STASH) =

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
 Inter-node comm. parallelism          (FCM_PARALLELISM) = 1
 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

 WLM dispatcher enabled                 (WLM_DISPATCHER) = NO
 WLM dispatcher concurrency            (WLM_DISP_CONCUR) = COMPUTED
 WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO
 WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5

 Communication buffer exit library list (COMM_EXIT_LIST) =


D:\IBM\SQLLIB\BIN>

Checking for DB2SET values

C:\Users\Administrator>db2set -all

[e] DB2PATH=D:\IBM\SQLLIB
[i] DB2_COMPATIBILITY_VECTOR=ORA
[i] DB2INSTOWNER=BASE-WIN2K8X64
[i] DB2PORTRANGE=60000:60003
[i] DB2INSTPROF=C:\PROGRAMDATA\IBM\DB2\DB2COPY1
[i] DB2COMM=TCPIP
[g] DB2_EXTSECURITY=YES
[g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
[g] DB2SYSTEM=BASE-WIN2K8X64
[g] DB2PATH=D:\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2CLIINIPATH=D:\IBM\SQLLIB
[g] DB2ADMINSERVER=DB2DAS00

SQL to find out DB2 Instance Name

SELECT INST_NAME FROM TABLE(SYSPROC.ENV_GET_INST_INFO()) AS SYSTEMINFO

The default value is DB2

Another set of create database command for reference

CREATE DATABASE dmdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k

connect to dmdb

create regular tablespace datamart pagesize 32k

GRANT USE OF TABLESPACE datamart TO USER datamart WITH GRANT OPTION

REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

Script to drop disconnect and drop DB2 database

#!/bin/sh

 

db2 "connect to emmdb"

 

# Not sure if the following is actually need if the database needs to be restarted

# because of the 'currently in use' issues. However it should disconnect all application

# connected to the database.

 

db2 "quiesce database immediate force connections"

db2 "unquiesce database"

 

db2 "force application all"

 

# Note the following will impact all databases and applications connected to this instance

# Only perform these steps if the above is not a problem.

# These steps take care of 'The database is currently in use' issues after doing the above

 

db2stop force

db2start

 

# Finally drop the database

db2 "drop database emmdb"