====== DB2 command line ======
===== Check TSM database state during start =====
$ db2 list utilities show detail
ID = 14346
Type = RESTORE
Database Name = DBTEST4
Partition Number = 0
Description = db
Start Time = 05/19/2014 09:29:09.946528
State = Executing
Invocation Type = User
Progress Monitoring:
Completed Work = 157494358016 bytes
Start Time = 05/19/2014 09:29:09.946531
**Type can be :**
CRASH RECOVERY
RESTORE
RUNSTATS (this is the normal started status)
[eco-aix61@tsminst1] /tsm/config/sqllib/bin> db2level
DB21085I Instance "tsminst1" uses "64" bits and DB2 code release "SQL09074"
with level identifier "08050107".
Informational tokens are "DB2 v9.7.0.4", "s110330", "IP23236", and Fix Pack
"4".
Product is installed at "/opt/tivoli/tsm/db2".
[tsminst1@tsmaix]/tsm/tsm1/home$ db2set
DB2_PMODEL_SETTINGS=MAX_BACKGROUND_SYSAPPS:500
DB2_SKIPINSERTED=ON
DB2_KEEPTABLELOCK=OFF
DB2_EVALUNCOMMITTED=ON
DB2_SKIPDELETED=ON
DB2COMM=TCPIP
DB2CODEPAGE=819
DB2_PARALLEL_IO=*
Update a DB2 parameter, for example parallel IO if you use RAID 5, commonly disks on a storage array on the SAN:
[tsminst1@tsmaix]/tsm/tsm1/home$ db2set DB2_PARALLEL_IO=*:5
[eco-aix61@tsminst1] /tsm/config> db2 list db 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 = d.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 = /tsm/config
Database release level = d.00
Comment = TSM SERVER DATABASE
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[eco-aix61@tsminst1] /tsm/config> db2ilist
tsminst1
tsminst2
[eco-aix61@tsminst1] /tsm/config> db2 get dbm cfg
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x0d00
CPU speed (millisec/instruction) (CPUSPEED) = 5.786217e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 8
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) = /tsm/config/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /tsm/config/sqllib/db2dump/
Alternate diagnostic data directory path (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) = ON
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 (CLUSTER_MGR) =
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) = /tsm/config/tsminst1
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
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(2204490)
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) = 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_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) = eco_aix6
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) = YES
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)
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
Tuning parameter on DB2, as root set the instance profile:
[eco-aix61@root] /root> db2 get db cfg for TSMDB1 | grep -i hadr
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 log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
=== add 2nd instance on TSM on same server: ===
/etc/services:
DB2_tsminst1 60000/tcp
DB2_tsminst1_1 60001/tcp
DB2_tsminst1_2 60002/tcp
DB2_tsminst1_END 60003/tcp
DB2_tsminst2 60004/tcp
DB2_tsminst2_1 60005/tcp
DB2_tsminst2_2 60006/tcp
DB2_tsminst2_END 60007/tcp
Now register a new instance into the server:
[root@tsm1]/opt/tivoli/tsm/db2/bin/db2ilist
tsminst1
[root@tsm1]/opt/tivoli/tsm# /opt/tivoli/tsm/db2/instance/db2iset -a tsminst2
[root@tsm1]/opt/tivoli/tsm# /opt/tivoli/tsm/db2/bin/db2ilist
tsminst1
tsminst2
**Limit the DB2 memory usage**
The following memory settings exist without DBMEMPERCENT being specified in the dsmserv.opt file:
(These values are found in the output from "db2 get dbm cfg show detail" issued as the db2 instance owner).
Description : Size of instance shared memory (4KB)
Parameter : (INSTANCE_MEMORY)
Current Value: AUTOMATIC(363892)
Delayed Value: AUTOMATIC(363892)
Add DBMEMPERCENT to the dsmserv.opt file:
dbmempercent 30
Halt and restart the server, the server reports:\\
ANR1383I Memory usage by the database is limited to 562MB.
If you issue the "db2 get dbm cfg show detail" again, you will see that the values have not been applied:
DB2 config reports:
Description : Size of instance shared memory (4KB)
Parameter : (INSTANCE_MEMORY)
Current Value: 143872
Delayed Value: 143872