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"