wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== DB2 product informations ====

<cli prompt='#'>
[root@prtsm02 bin]# /opt/tivoli/tsm/db2/bin/db2level 
DB21085I  This instance or install (instance name, where applicable: "tsmsrv2") 
uses "64" bits and DB2 code release "SQL11010" with level identifier 
"0201010F".
Informational tokens are "DB2 v11.1.0.0", "s1606081100", "DYN1606081100AMD64", 
and Fix Pack "0".
Product is installed at "/opt/tivoli/tsm/db2".
</cli>

DB2 software installations on machine:
<cli prompt='#'>
[root@prtsm02 install]# /opt/tivoli/tsm/db2/install/db2ls 

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID 
---------------------------------------------------------------------------------------------------------------------
/opt/tivoli/tsm/db2              11.1.0.0        0                            Sun Jan 29 14:07:31 2017 CET             0 
</cli>

DB2 Products:
<cli prompt='#'>
[root@prtsm02 bin]# /opt/tivoli/tsm/db2/install/db2ls -q -p -b /opt/tivoli/tsm/db2

Install Path : /opt/tivoli/tsm/db2

Product Response File ID                  Level   Fix Pack   Product Description  
---------------------------------------------------------------------------------------------------------------------
DB2_SERVER_EDITION                      11.1.0.0          0   DB2 Server Edition 
</cli>

DB2 features including hidden:
<cli prompt='#'>
[root@prtsm02 bin]# /opt/tivoli/tsm/db2/install/db2ls -q -a -b /opt/tivoli/tsm/db2

Install Path : /opt/tivoli/tsm/db2

Feature Response File ID             Level   Fix Pack   Feature Description  
---------------------------------------------------------------------------------------------------------------------
BASE_CLIENT_R                      11.1.0.0          0   Base Client Support for installation with root privileges 
DB2_PRODUCT_MESSAGES_EN            11.1.0.0          0   Product Messages - English 
BASE_CLIENT                        11.1.0.0          0   Base client support 
JAVA_RUNTIME_SUPPORT               11.1.0.0          0   Java Runtime Support 
DB2_JAVA_HELP_EN                   11.1.0.0          0   Java Help (HTML) - English 
BASE_DB2_ENGINE_R                  11.1.0.0          0   Base server support for installation with root privileges 
GSK                                11.1.0.0          0   Global Secure ToolKit 
JAVA_SUPPORT                       11.1.0.0          0   Java support 
SQL_PROCEDURES                     11.1.0.0          0   SQL procedures 
ICU_SUP                            11.1.0.0          0   ICU Utilities 
JAVA_COMMON_FILES                  11.1.0.0          0   Java Common files 
BASE_DB2_ENGINE                    11.1.0.0          0   Base server support 
JDK                                11.1.0.0          0   IBM Software Development Kit (SDK) for Java(TM) 
...
</cli>

List the database version
<cli prompt='$'>
[db2inst1@BIGFIX01 ~]$ db2 connect to BESREPOR

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.8.0
 SQL authorization ID   = DB2INST1
 Local database alias   = BESREPOR

[db2inst1@BIGFIX01 ~]$ db2 "SELECT fixpack_num, service_level FROM TABLE (sysproc.env_get_inst_info()) as informationOfInstance"

FIXPACK_NUM SERVICE_LEVEL
----------- --------------------------------------------------------------------------------------------------------------------------------
          0 DB2 v11.5.8.0

  1 record(s) selected.
</cli>

==== DB2 processes ====

On Windows use **db2stat**

DB2 UNIX processes:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ ps -ef | grep db2
root     19886     1  0 Sep25 ?        00:01:38 /opt/tivoli/tsm/db2/bin/db2fmcd
root     30834     1  0 12:49 pts/0    00:00:00 db2wdog 0 [tsminst1]
tsminst1 30836 30834 26 12:49 pts/0    00:00:27 db2sysc 0
root     30842 30834  0 12:49 pts/0    00:00:00 db2ckpwd 0
root     30843 30834  0 12:49 pts/0    00:00:00 db2ckpwd 0
root     30844 30834  0 12:49 pts/0    00:00:00 db2ckpwd 0
tsminst1 30846 30834  0 12:49 pts/0    00:00:00 db2vend (PD Vendor Process - 1) 0
tsminst1 30853 30834  0 12:49 pts/0    00:00:00 db2acd 0 ,0,0,0,1,0,0,00000000,0,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000bca61000,0000000000000000,0000000000000000,1,0,0,,,,,a80035,14,1e014,2,0,1,0000000000041fc0,0x240000000,0x240000000,1600000,9948004,2,bd08012
tsminst1 30892 30834  0 12:49 pts/0    00:00:00 db2fmp ( ,0,0,0,0,0,0,00000000,0,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000bca61000,0000000000000000,0000000000000000,1,0,0,,,,,a80035,14,1e014,2,0,1,0000000000061fc0,0x240000000,0x240000000,1600000,9948004,2,be78030
</cli>

As DB2 instance user:
DB2 internal processes:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2pd -edus

Database Member 0 -- Active -- Up 0 days 00:01:18 -- Date 2017-10-05-12.50.44.752838

List of all EDUs for database member 0

db2sysc PID: 30836
db2wdog PID: 30834
db2acd  PID: 30853

EDU ID    TID                  Kernel TID     EDU Name                               USR (s)         SYS (s) 
=====================================================================================================
114       139815970400000      31230       db2agntdp (TSMDB1  ) 0                 0.000000    0.000000
113       139815974594304      31207       db2agent (TSMDB1) 0                    0.000000    0.000000
.....
79        139819044824832      30931       db2agent (TSMDB1) 0                    3.700000    0.180000
78        139819057407744      30930       db2agent (TSMDB1) 0                    4.910000    0.270000
76        139819065796352      30925       db2pcsd (TSMDB1) 0                     0.040000    0.000000
75        139819078379264      30924       db2fw0 (TSMDB1) 0                      0.000000    0.000000
74        139819082573568      30923       db2dbctrld (TSMDB1) 0                  0.010000    0.000000
73        139819086767872      30922       db2lused (TSMDB1) 0                    0.000000    0.000000
72        139819090962176      30921       db2wlmd (TSMDB1) 0                     0.000000    0.000000
71        139819095156480      30920       db2taskd (TSMDB1) 0                    0.000000    0.000000
....
60        139819069990656      30909       db2pfchr (TSMDB1) 0                    0.000000    0.000000
59        139819053213440      30908       db2pfchr (TSMDB1) 0                    0.000000    0.000000
58        139819132905216      30907       db2pclnr (TSMDB1) 0                    0.000000    0.000000
57        139819153876736      30906       db2logalloc.0 (TSMDB1) 0               0.000000    0.000000
56        139819145488128      30905       db2lfr.0 (TSMDB1) 0                    0.000000    0.000000
55        139819149682432      30904       db2loggw (TSMDB1) 0                    0.070000    0.190000
54        139819158071040      30903       db2loggr (TSMDB1) 0                    0.040000    0.070000
53        139819137099520      30902       db2logmgr (TSMDB1) 0                   0.000000    0.000000
52        139819141293824      30901       db2logts (TSMDB1) 0                    0.000000    0.000000
51        139819162265344      30900       db2dlock (TSMDB1) 0                    0.000000    0.000000
50        139819049019136      30894       db2stmm (TSMDB1) 0                     0.030000    0.010000
21        139819166459648      30861       db2agent (TSMDB1) 0                    2.510000    1.220000
20        139819170653952      30851       db2spmlw 0                             0.000000    0.000000
19        139819174848256      30850       db2spmrsy 0                            0.000000    0.000000
18        139819179042560      30849       db2resync 0                            0.000000    0.010000
17        139819183236864      30848       db2tcpcm 0                             0.000000    0.000000
16        139819187431168      30847       db2ipccm 0                             0.080000    0.070000
15        139819191625472      30845       db2wlmtm 0                             0.100000    0.020000
14        139819195819776      30841       db2wlmt 0                              0.000000    0.000000
13        139819200014080      30840       db2licc 0                              0.000000    0.000000
12        139819204208384      30839       db2thcln 0                             0.000000    0.000000
11        139819208402688      30838       db2alarm 0                             0.010000    0.000000
1         139818948355840      30837       db2sysc 0                              0.120000    0.030000
</cli>

DB2 processes:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2_local_ps 
Node 0
     UID        PID       PPID    C     STIME     TTY     TIME CMD
    root      30834          1    0     12:49   pts/0 00:00:00 db2wdog 0 [tsminst1]
tsminst1      30836      30834    4     12:49   pts/0 00:00:32 db2sysc 0
    root      30842      30834    0     12:49   pts/0 00:00:00 db2ckpwd 0
    root      30843      30834    0     12:49   pts/0 00:00:00 db2ckpwd 0
    root      30844      30834    0     12:49   pts/0 00:00:00 db2ckpwd 0
tsminst1      30846      30834    0     12:49   pts/0 00:00:00 db2vend (PD Vendor Process - 1) 0
tsminst1      30853      30834    0     12:49   pts/0 00:00:00 db2acd 0 ,0,0,0,1,0,0,00000000,0,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000bca61000,0000000000000000,0000000000000000,1,0,0,,,,,a80035,14,1e014,2,0,1,0000000000041fc0,0x240000000,0x240000000,1600000,9948004,2,bd08012
tsminst1      30892      30834    0     12:49   pts/0 00:00:00 db2fmp ( ,0,0,0,0,0,0,00000000,0,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000bca61000,0000000000000000,0000000000000000,1,0,0,,,,,a80035,14,1e014,2,0,1,0000000000061fc0,0x240000000,0x240000000,1600000,9948004,2,be78030
tsminst1      31894      30834    0     12:54   pts/0 00:00:00 db2fmp ( ,1,0,0,0,0,0,00000000,0,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000bca61000,0000000000000000,0000000000000000,1,0,0,,,,,a80035,14,1e014,2,0,1,0000000000081fc0,0x240000000,0x240000000,1600000,9948004,2,d9a007e
</cli>

==== DB2 start/stop ====

As DB instance user, start you database
<cli prompt='#$'>
[root@rhlab01 ~]# su - tsminst1
[tsminst1@rhlab01 ~]$ db2start
10/05/2017 13:13:52     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
</cli>

Stop a database
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2stop
10/05/2017 13:13:45     0   0   SQL1032N  No start database manager command was issued.
SQL1032N  No start database manager command was issued.  SQLSTATE=57019
</cli>

==== DB2 list instances ====

To list all registered instance on a server, as root:
<cli prompt='#'>
[root@rhlab01 ~]# /opt/tivoli/tsm/db2/instance/db2ilist 
tsminst1
tsminst2
</cli>

==== DB2 list database ====

To list all DB on an instance, as instance user:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = TSMAL001
 Database name                        = TSMDB1
 Node name                            = TSMNODE1
 Database release level               = 14.00
 Comment                              = TSM SERVER DATABASE VIA TCPIP
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = TSMDB1
 Database name                        = TSMDB1
 Local database directory             = /opt/tivoli/tsminst1/config
 Database release level               = 14.00
 Comment                              = TSM SERVER DATABASE
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
</cli>


==== List tables ====

List all tables using list tables or list tables for all
<cli prompt='$'>
[db2inst1@LINVH ~]$ db2 LIST TABLES for all |  grep ADM
ADMINFIELDS                     DBO             T     2017-02-09-10.17.01.153303
COMPUTER_ADMINISTRATORS         DBO             T     2017-02-09-10.17.01.547467
ADMINTABCOMPRESSINFO            SYSIBMADM       V     2021-07-26-16.07.45.473279
ADMINTABINFO                    SYSIBMADM       V     2021-07-26-16.07.45.460435
</cli>

==== Select on table ====
==== DB2 Database Manager Configuration ====

List parameters for a specific instance tsminst1 (you can have multiple instance per server)
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get dbm cfg 

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x1400

 CPU speed (millisec/instruction)             (CPUSPEED) = 1.889377e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 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) = /tsminst1/config/sqllib/java/jdk64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /tsminst1/config/sqllib/db2dump/
 Current member resolved DIAGPATH                        = /tsminst1/config/sqllib/db2dump/
 Alternate diagnostic data directory path (ALT_DIAGPATH) = 
 Current member resolved ALT_DIAGPATH                    = 
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 1024

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = ON
   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) = TSMSRVRS
 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) = 
 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) = dsmdb2pw
 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) = /tsminst1/config

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(90)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Global instance memory (% or 4KB)     (INSTANCE_MEMORY) = AUTOMATIC(772705)
 Member instance memory (% or 4KB)                       = GLOBAL
 Agent stack size                       (AGENT_STACK_SZ) = 1024
 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) = 65535
 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_COORDAGENTS)

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
 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) = rhlab01
 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) = 51500
 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

 Number of FCM buffers                 (FCM_NUM_BUFFERS) = AUTOMATIC(4096)
 FCM buffer size                       (FCM_BUFFER_SIZE) = 32768
 Number of FCM channels               (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
 FCM parallelism                       (FCM_PARALLELISM) = AUTOMATIC(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) = 
 Current effective arch level         (CUR_EFF_ARCH_LVL) = V:11 R:1 M:1 F:1 I:0 SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:11 R:1 M:1 F:1 I:0 SB:36520

 Keystore type                           (KEYSTORE_TYPE) = NONE
 Keystore location                   (KEYSTORE_LOCATION) = 
</cli>

==== DB2 Database Configuration ====

List parameters for a specific database in the instance tsminst1 (you can have multiple DB per instance)
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get db cfg for TSMDB1

       Database Configuration for Database TSMDB1

 Database configuration release level                    = 0x1400
 Database release level                                  = 0x1400

 Database territory                                      = C
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country/region code                            = 1
 Database collating sequence                             = IDENTITY
 Alternate collating sequence              (ALT_COLLATE) = 
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = OFF
 Date compatibility                                      = OFF
 Database page size                                      = 16384

 Statement concentrator                      (STMT_CONC) = OFF

 Discovery support for this database       (DISCOVER_DB) = ENABLE

 Restrict access                                         = YES
 Default query optimization class         (DFT_QUERYOPT) = 5
 Degree of parallelism                      (DFT_DEGREE) = 1
 Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO
 Default refresh age                   (DFT_REFRESH_AGE) = 0
 Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
 Number of frequent values retained     (NUM_FREQVALUES) = 10
 Number of quantiles retained            (NUM_QUANTILES) = 20

 Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN

 Backup pending                                          = NO

 All committed transactions have been written to disk    = YES
 Rollforward pending                                     = NO
 Restore pending                                         = NO

 Upgrade pending                                         = NO

 Multi-page file allocation enabled                      = YES

 Log retain for recovery status                          = NO
 User exit for logging status                            = YES

 Self tuning memory                    (SELF_TUNING_MEM) = ON
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(453024)
 Database memory threshold               (DB_MEM_THRESH) = 100
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(11328)
 Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(97)
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(2250)
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5028)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(1005)

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(4502)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2048
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = AUTOMATIC(250000)
 SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(8192)
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
 Lock timeout (sec)                        (LOCKTIMEOUT) = -1

 Changed pages threshold                (CHNGPGS_THRESH) = 40
 Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(1)
 Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(12)
 Sequential detect flag                      (SEQDETECT) = YES
 Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC

 Track modified pages                         (TRACKMOD) = YES

 Default number of containers                            = 10
 Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 32

 Max number of active applications            (MAXAPPLS) = AUTOMATIC(52)
 Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
 Max DB files open per application            (MAXFILOP) = 61440

 Log file size (4KB)                         (LOGFILSIZ) = 131072
 Number of primary log files                (LOGPRIMARY) = 32
 Number of secondary log files               (LOGSECOND) = 0
 Changed path to log files                  (NEWLOGPATH) = 
 Path to log files                                       = /opt/tivoli/tsminst1/log/NODE0000/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = S0000010.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 90
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 29

 Percent log file reclaimed before soft chckpt (SOFTMAX) = 0
 Target for oldest page in LBP       (PAGE_AGE_TRGT_MCR) = 240

 HADR database role                                      = STANDARD
 HADR local host name                  (HADR_LOCAL_HOST) = 
 HADR local service name                (HADR_LOCAL_SVC) = 
 HADR remote host name                (HADR_REMOTE_HOST) = 
 HADR remote service name              (HADR_REMOTE_SVC) = 
 HADR instance name of remote server  (HADR_REMOTE_INST) = 
 HADR timeout value                       (HADR_TIMEOUT) = 120
 HADR target list                     (HADR_TARGET_LIST) = 
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
 HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

 First log archive method                 (LOGARCHMETH1) = DISK:/opt/tivoli/tsminst1/archlog/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
 Failover log archive path                (FAILARCHPATH) = 
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Vendor options                              (VENDOROPT) = 

 Auto restart enabled                      (AUTORESTART) = OFF
 Index re-creation time and redo index build  (INDEXREC) = SYSTEM (RESTART)
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
 Number of database backups to retain   (NUM_DB_BACKUPS) = 1
 Recovery history retention (days)     (REC_HIS_RETENTN) = 0
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON

 TSM management class                    (TSM_MGMTCLASS) = 
 TSM node name                            (TSM_NODENAME) = $$_TSMDBMGR_$$
 TSM owner                                   (TSM_OWNER) = 
 TSM password                             (TSM_PASSWORD) = *****

 Automatic maintenance                      (AUTO_MAINT) = ON
   Automatic database backup            (AUTO_DB_BACKUP) = OFF
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
       Real-time statistics            (AUTO_STMT_STATS) = ON
       Statistical views              (AUTO_STATS_VIEWS) = OFF
       Automatic sampling                (AUTO_SAMPLING) = ON
     Automatic reorganization               (AUTO_REORG) = OFF

 Auto-Revalidation                          (AUTO_REVAL) = DISABLED

 Currently Committed                        (CUR_COMMIT) = ON
 CHAR output with DECIMAL input        (DEC_TO_CHAR_FMT) = NEW
 Enable XML Character operations        (ENABLE_XMLCHAR) = YES
 Monitor Collect Settings
 Request metrics                       (MON_REQ_METRICS) = BASE
 Activity metrics                      (MON_ACT_METRICS) = BASE
 Object metrics                        (MON_OBJ_METRICS) = EXTENDED
 Routine data                             (MON_RTN_DATA) = NONE
   Routine executable list            (MON_RTN_EXECLIST) = OFF
 Unit of work events                      (MON_UOW_DATA) = NONE
   UOW events with package list        (MON_UOW_PKGLIST) = OFF
   UOW events with executable list    (MON_UOW_EXECLIST) = OFF
 Lock timeout events                   (MON_LOCKTIMEOUT) = NONE
 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
 Lock wait events                         (MON_LOCKWAIT) = NONE
 Lock wait event threshold               (MON_LW_THRESH) = 5000000
 Number of package list entries         (MON_PKGLIST_SZ) = 32
 Lock event notification level         (MON_LCK_MSG_LVL) = 1

 SMTP Server                               (SMTP_SERVER) = 
 SQL conditional compilation flags         (SQL_CCFLAGS) = 
 Section actuals setting               (SECTION_ACTUALS) = NONE
 Connect procedure                        (CONNECT_PROC) = 
 Adjust temporal SYSTEM_TIME period (SYSTIME_PERIOD_ADJ) = NO
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO
 Default data capture on new Schemas   (DFT_SCHEMAS_DCC) = NO
 Strict I/O for EXTBL_LOCATION         (EXTBL_STRICT_IO) = NO
 Allowed paths for external tables      (EXTBL_LOCATION) = /tsminst1/config
 Default table organization              (DFT_TABLE_ORG) = ROW
 Default string units                     (STRING_UNITS) = SYSTEM
 National character string mapping       (NCHAR_MAPPING) = NOT APPLICABLE
 Database is in write suspend state                      = NO
 Extended row size support             (EXTENDED_ROW_SZ) = ENABLE
 Encryption Library for Backup                 (ENCRLIB) = 
 Encryption Options for Backup                (ENCROPTS) = 

 WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0
 Target agent load per CPU core    (WLM_AGENT_LOAD_TRGT) = AUTOMATIC(11)
 WLM admission control enabled      (WLM_ADMISSION_CTRL) = NO
 Allocated share of CPU resources       (WLM_CPU_SHARES) = 1000
 CPU share behavior (hard/soft)     (WLM_CPU_SHARE_MODE) = HARD
 Maximum allowable CPU utilization (%)   (WLM_CPU_LIMIT) = 0
 Encrypted database                                      = NO
 Procedural language stack trace        (PL_STACK_TRACE) = NONE
 HADR SSL certificate label             (HADR_SSL_LABEL) = 
</cli>

==== Update DB2 parameter ====

''Ex'' DIAGLEVEL in DBM configuration
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get dbm cfg | grep -i diagl
 Diagnostic error capture level              (DIAGLEVEL) = 3
[tsminst1@rhlab01 ~]$ db2 update dbm cfg using diaglevel 4
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.
[tsminst1@rhlab01 ~]$ db2 get dbm cfg | grep -i diagl
 Diagnostic error capture level              (DIAGLEVEL) = 4
</cli>

Check the effective parameter, for the instance:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 attach to tsminst1

   Instance Attachment Information

 Instance server        = DB2/LINUXX8664 11.1.1.1
 Authorization ID       = TSMINST1
 Local instance alias   = TSMINST1

[tsminst1@rhlab01 ~]$ db2 get dbm cfg show detail

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Description                                   Parameter   Current Value              Delayed Value
 --------------------------------------------------------------------------------------------------
 Database manager configuration release level            = 0x1400
...
 Diagnostic error capture level              (DIAGLEVEL) = 3                          4           
</cli>

Now you can see parameter is not effective. Some parameters are dynamic, and other required a restart of the DB (db2stop / db2start).

==== Parameters of the instance ====

Some parameters can be changed.

  NUMDB: 32 maximum number of database per instance
  FEDERATED: to be able to connect to other remote database
  DIAGLEVEL: default 3 can be increased to have more detail
  NOTIFYLEVEL: the administration notification log
  DIAGSIZE: if not 0, then 10% for notify and 90% for diag

Differently from Oracle, on DB2 create a user and group directly on OS level to assing specific function to a group. ''Ex'' SYSMON_GROUP is set to allow a group to do snapshots at DB2 level
  SYSADM group name                        (SYSADM_GROUP) = TSMSRVRS
  SYSCTRL group name                      (SYSCTRL_GROUP) = 
  SYSMAINT group name                    (SYSMAINT_GROUP) = 
  SYSMON group name                        (SYSMON_GROUP) = 

Instance parameters
  DFTDBPATH: default instance path
  INSTANCE_MEMORY: memory for all DB on this instance (default: AUTOMATIC)

==== Memory usage ====

Memory used by instance and databases:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get dbm cfg  | grep MEM
 Global instance memory (% or 4KB)     (INSTANCE_MEMORY) = AUTOMATIC(772705)

[tsminst1@rhlab01 ~]$ db2pd -dbptnmem

Database Member 0 -- Active -- Up 0 days 00:35:45 -- Date 2017-10-05-14.28.15.005832

Database Member Memory Controller Statistics

Controller Automatic: Y
Controller License Limit: N
Controller Limit Enforced: N

Memory Limit:         3090820 KB
Current usage:        184512 KB
HWM usage:            184512 KB
Cached memory:        33024 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
DBMS-tsminst1           157568        157568       10624
FMP_RESOURCES            22528         22528       22400
PRIVATE                   4416          4416           0
</cli>

**Memory Limit** correspond to the hard limit if fixed in instance parameter INSTANCE_MEMORY (772705*4=3090820KB). If fixed, it's the memory for all DB in this instance.

**Cache Memory** is preallocated and not in use.

When activate a DB:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 activate db TSMDB1
DB20000I  The ACTIVATE DATABASE command completed successfully.
[tsminst1@rhlab01 ~]$ db2pd -dbptnmem

Database Member 0 -- Active -- Up 0 days 00:49:56 -- Date 2017-10-05-14.42.26.842587

Database Member Memory Controller Statistics

Controller Automatic: Y
Controller License Limit: N
Controller Limit Enforced: N

Memory Limit:         3090820 KB
Current usage:        2148672 KB
HWM usage:            2148672 KB
Cached memory:        346176 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-TSMDB1             160000        160000      158976
DBMS-tsminst1           157568        157568       10624
FMP_RESOURCES            22528         22528       22400
PRIVATE                  14080         14080           0
DB-TSMDB1              1794496       1794496      154176
</cli> 

You can also set a custom value for Database memory, but then you have to manage memory allocation for maybe other DB, instances...
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get db cfg for TSMDB1 | grep MEM
 Self tuning memory                    (SELF_TUNING_MEM) = ON
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(462168)
 Database memory threshold               (DB_MEM_THRESH) = 100
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)
</cli>

Some parameters on the database config can't be changed, theses are set at DB creation ''Ex'' Database code set : UTF-8
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 get db cfg for TSMDB1

       Database Configuration for Database TSMDB1

 Database configuration release level                    = 0x1400
 Database release level                                  = 0x1400

 Database territory                                      = C
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country/region code                            = 1
</cli>

==== Monitoring ====

Monitor a database table usage per:
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2top -d TSMDB1 
</cli>
  d - Database            l - Sessions            a - Agent
  t - Tablespaces         b - Bufferpools         T - Tables
  D - Dynamic SQL         U - Locks               m - Memory
  s - Statements          p - Members             u - Utilities           
  A - HADR                F - Federation          B - Bottlenecks         
  J - Skew monitor        q - Quit

==== DB creation ====

Example DB using India, non unicode DB will consume less space than UNICODE (filed needs bigger length)
<cli prompt='$'>
[tsminst1@rhlab01 ~]$ db2 create db testdb using codeset iso-8859-15 territory in
[tsminst1@rhlab01 ~]$ db2 get db cfg for testdb

       Database Configuration for Database testdb

 Database configuration release level                    = 0x1400
 Database release level                                  = 0x1400

 Database territory                                      = in
 Database code page                                      = 923
 Database code set                                       = ISO885915
 Database country/region code                            = 91
...
[tsminst1@rhlab01 ~]$ db2start
</cli>

==== DB2 using SQL queries ====

Ex of SQL script, export a table into a file and import then into a new DB
<cli prompt='$'>
$ cat myscipt.sql
connect to MYDB;
create table mytab1(id int,name varchar(8));
insert into mytab1 values=(1,'@@');
select * from mytab1;
export to mytab1_exp.ixf of ixf of select * from mytab1;
connect reset;
connect to testdb;
create table mytab1(id int,name varchar(8));
import from mytab1_exp.ixf of ixf insert into mytab1;
select * from mytab1;
connect reset;
</cli>

Call the script
<cli prompt='$'>
$ db2 -tvf myscipt.sql
</cli>

===== List DB2 installed componenets =====

<cli prompt='#'>
[root@lnx01]/tmp/db2/universal # ./db2ls -q -b /opt/ibm/db2/V11.5

Install Path : /opt/ibm/db2/V11.5

Feature Response File ID             Level   Fix Pack   Feature Description
---------------------------------------------------------------------------------------------------------------------
BASE_CLIENT                        11.5.4.0          0   Base client support
JAVA_SUPPORT                       11.5.4.0          0   Java support
SQL_PROCEDURES                     11.5.4.0          0   SQL procedures
BASE_DB2_ENGINE                    11.5.4.0          0   Base server support
DB2_DATA_SOURCE_SUPPORT            11.5.4.0          0   DB2 data source support
...
</cli>

<cli prompt='#'>
[root@lnx01]/tmp/db2/universal # ./db2ls -q -a -b /opt/ibm/db2/V11.5

Install Path : /opt/ibm/db2/V11.5

Feature Response File ID             Level   Fix Pack   Feature Description
---------------------------------------------------------------------------------------------------------------------
BASE_CLIENT_R                      11.5.4.0          0   Base Client Support for installation with root privileges
DB2_PRODUCT_MESSAGES_EN            11.5.4.0          0   Product Messages - English
BASE_CLIENT                        11.5.4.0          0   Base client support
JAVA_RUNTIME_SUPPORT               11.5.4.0          0   Java Runtime Support
DB2_JAVA_HELP_EN                   11.5.4.0          0   Java Help (HTML) - English
BASE_DB2_ENGINE_R                  11.5.4.0          0   Base server support for installation with root privileges
GSK                                11.5.4.0          0   Global Secure ToolKit
...
</cli>
wiki

User Tools

Site Tools

Page Tools
