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 command line ======

===== Check TSM database state during start =====

<cli prompt='$'>
$ 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
</cli>      

**Type can be :**
  CRASH RECOVERY
  RESTORE
  RUNSTATS (this is the normal started status)
  

<cli prompt='>'>
[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".
</cli>

<cli prompt='$'>
[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=*
</cli>

Update a DB2 parameter, for example parallel IO if you use RAID 5, commonly disks on a storage array on the SAN:
<cli prompt='$'>
[tsminst1@tsmaix]/tsm/tsm1/home$ db2set DB2_PARALLEL_IO=*:5 
</cli>

<cli prompt='>'>
[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         =
</cli>

<cli prompt='>'>
[eco-aix61@tsminst1] /tsm/config> db2ilist
tsminst1
tsminst2
</cli>

<cli prompt='>'>
[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
</cli>

Tuning parameter on DB2, as root set the instance profile:
<cli prompt='>'>
[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
</cli>

=== add 2nd instance on TSM on same server: ===

/etc/services:
<code>
DB2_tsminst1      60000/tcp
DB2_tsminst1_1    60001/tcp
DB2_tsminst1_2    60002/tcp
DB2_tsminst1_END  60003/tcp
</code>
<cli prompt='#'>
  DB2_tsminst2      60004/tcp
  DB2_tsminst2_1    60005/tcp
  DB2_tsminst2_2    60006/tcp
  DB2_tsminst2_END  60007/tcp
</cli>

Now register a new instance into the server:
<cli prompt='#'>
[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
</cli>

**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).
<code>
Description  : Size of instance shared memory (4KB)
Parameter    : (INSTANCE_MEMORY)
Current Value: AUTOMATIC(363892)
Delayed Value: AUTOMATIC(363892)
</code>

Add DBMEMPERCENT to the dsmserv.opt file:
<code>
dbmempercent 30
</code>

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:
<code>
Description  : Size of instance shared memory (4KB)
Parameter    : (INSTANCE_MEMORY)
Current Value: 143872
Delayed Value: 143872
</code>
wiki

User Tools

Site Tools

Page Tools
