==== DB2 product informations ====
[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".
DB2 software installations on machine:
[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
DB2 Products:
[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
DB2 features including hidden:
[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)
...
List the database version
[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.
==== DB2 processes ====
On Windows use **db2stat**
DB2 UNIX processes:
[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
As DB2 instance user:
DB2 internal processes:
[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
DB2 processes:
[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
==== DB2 start/stop ====
As DB instance user, start you database
[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.
Stop a database
[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
==== DB2 list instances ====
To list all registered instance on a server, as root:
[root@rhlab01 ~]# /opt/tivoli/tsm/db2/instance/db2ilist
tsminst1
tsminst2
==== DB2 list database ====
To list all DB on an instance, as instance user:
[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 =
==== List tables ====
List all tables using list tables or list tables for all
[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
==== Select on table ====
==== DB2 Database Manager Configuration ====
List parameters for a specific instance tsminst1 (you can have multiple instance per server)
[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) =
==== DB2 Database Configuration ====
List parameters for a specific database in the instance tsminst1 (you can have multiple DB per instance)
[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) =
==== Update DB2 parameter ====
''Ex'' DIAGLEVEL in DBM configuration
[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
Check the effective parameter, for the instance:
[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
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:
[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
**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:
[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
You can also set a custom value for Database memory, but then you have to manage memory allocation for maybe other DB, instances...
[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)
Some parameters on the database config can't be changed, theses are set at DB creation ''Ex'' Database code set : UTF-8
[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
==== Monitoring ====
Monitor a database table usage per:
[tsminst1@rhlab01 ~]$ db2top -d TSMDB1
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)
[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
==== DB2 using SQL queries ====
Ex of SQL script, export a table into a file and import then into a new DB
$ 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;
Call the script
$ db2 -tvf myscipt.sql
===== List DB2 installed componenets =====
[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
...
[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
...