正确地打开DB2的监控开关(monitor switches)

[问题] 
db2中，为什么抓取snapshot 的时候，有些条目显示的是 Not Collected?
比如：
Most recent operation stop timestamp       = Not Collected


或者，根本就没有抓取到。
比如想查看某个应用当前正在执行的dynamic statement语句，却发现快照中并没有。


[原因]
原因是没有正确地打开DB2的监控开关。


[方案]
打开monitor switches有两种方式，第一种是会话级别（只在当前会话有效），第二种是实例级别。其中实例级别，如果应用已经连接，则需要 Reattach实例才会生效。



1.） 会话级别：
To activate any of the local monitor switches, use the UPDATE MONITOR SWITCHES command. The switches will remain active until the application (CLP) detaches, or until they are deactivated with another UPDATE MONITOR SWITCHES command. The following example updates all of the local monitor switches to be ON:
db2 update monitor switches using BUFFERPOOL on LOCK on
SORT on STATEMENT on TIMESTAMP on TABLE on UOW on


2.） 实例级别： 


It is also possible to manipulate the monitor switches at the database manager level. This involves changing the dft_monswitches parameters in the database manager configuration file, using the UPDATE DBM CFG command. In the following example, only lock switch controlled information is to be collected in addition to the basic information.
db2 update dbm cfg using DFT_MON_LOCK on 


Whenever a monitoring application is started, it inherits its monitor switch settings from the database manager. Any changes to the database manager's monitor switch settings will not impact any running monitoring applications. Monitoring applications must reattach themselves to the instance to pick up any changes to monitor switch settings.



[测试]


1.） 会话级别， 想查看当前application执行的动态SQL语句，使用快照，没有抓取到
C:\windows\system32>db2 get monitor switches


            Monitor Recording Switches


Switch list for member 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  2015-12-02 08:56:07.923490
Unit of Work Information                 (UOW) = OFF




C:\windows\system32>db2 "values application_id()"


1
--------------------------------------------------
*LOCAL.DB2.151202034052


  1 record(s) selected.
  
C:\windows\system32>db2 +c "insert into test values(110)"
DB20000I  The SQL command completed successfully.




//从application snapshot中看不到正在执行的dynamic statement
C:\windows\system32>db2 get snapshot for application applid *LOCAL.DB2.151202034052


            Application Snapshot


Application handle                         = 111
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1386
Application country/region code            = 86
DUOW correlation token                     = *LOCAL.DB2.151202034052


..


Workspace Information




Most recent operation                      = Execute Immediate
Most recent operation start timestamp      = Not Collected
Most recent operation stop timestamp       = Not Collected
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Number of OLAP functions                   = 0
Number of OLAP function overflows          = 0


Memory usage for application:


  Memory Pool Type                         = Application Heap
     Current size (bytes)                  = 131072
     High water mark (bytes)               = 131072
     Configured size (bytes)               = 1048576


Agent process/thread ID                    = 6940
  Agent Lock timeout (seconds)             = -1
  Memory usage for agent:


    Memory Pool Type                       = Other Memory
       Current size (bytes)                = 262144
       High water mark (bytes)             = 262144
       Configured size (bytes)             = 8271167488
  


C:\windows\system32>db2 rollback
DB20000I  The SQL command completed successfully.


//打开UOW和STATEMENT的监控开关，再次执行SQL语句  
C:\windows\system32>db2 update monitor switches using UOW ON STATEMENT ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.
  


C:\windows\system32>db2 update monitor switches using UOW ON STATEMENT ON
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.


C:\windows\system32>db2 +c "insert into test values(110)"
DB20000I  The SQL command completed successfully.


//可以看以，snapshot中多出了一项statement，能够找出对应的SQL语句
C:\windows\system32>db2 get snapshot for application applid *LOCAL.DB2.151202034052


            Application Snapshot


Application handle                         = 111
Application status                         = UOW Waiting
Status change time                         = 2015-12-02 11:43:36.688678
Application code page                      = 1386
Application country/region code            = 86


..


Workspace Information




Most recent operation                      = Execute Immediate
Most recent operation start timestamp      = 2015-12-02 11:43:36.672226
Most recent operation stop timestamp       = 2015-12-02 11:43:36.688669
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Number of OLAP functions                   = 0
Number of OLAP function overflows          = 0


Statement type                             = Dynamic SQL Statement
Statement                                  = Execute Immediate
Section number                             = 203
Application creator                        = NULLID
Package name                               = SQLC2K26
Consistency Token                          =
Package Version ID                         =
Cursor name                                =
Statement member number                    = 0
Statement start timestamp                  = 2015-12-02 11:43:36.672226
Statement stop timestamp                   = 2015-12-02 11:43:36.688669
Elapsed time of last completed stmt(sec.ms)= 0.016443
Total Statement user CPU time              = 0.000000
Total Statement system CPU time            = 0.000000
SQL compiler cost estimate in timerons     = 7
SQL compiler cardinality estimate          = 1
Degree of parallelism requested            = 1
Number of agents working on statement      = 1
Number of subagents created for statement  = 1
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 0
Rows written                               = 1
Internal rows deleted                      = 0
Internal rows updated                      = 0
Internal rows inserted                     = 0
Rows fetched                               = 0
Buffer pool data logical reads             = Not Collected
Buffer pool data physical reads            = Not Collected
Buffer pool temporary data logical reads   = Not Collected
Buffer pool temporary data physical reads  = Not Collected
Buffer pool index logical reads            = Not Collected
Buffer pool index logical reads            = Not Collected
Buffer pool temporary index logical reads  = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool xda logical reads              = Not Collected
Buffer pool xda physical reads             = Not Collected
Buffer pool temporary xda logical reads    = Not Collected
Buffer pool temporary xda physical reads   = Not Collected
Blocking cursor                            = NO
Dynamic SQL statement text:
insert into test values(110)



2.） 实例级别，通过修改dbm参数，如果应用已经连接，必须让应用重新attach 到实例才可以生效。
lock monitor的测试：
C:\windows\system32>db2 connect to sample


   Database Connection Information


 Database server        = DB2/NT64 10.5.4
 SQL authorization ID   = MIAOQING...

 Local database alias   = SAMPLE

C:\windows\system32>db2 get dbm cfg | find /i "DFT"
Default charge-back account (DFT_ACCOUNT_STR) =
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
Default database path (DFTDBPATH) = C:


C:\windows\system32>db2 update dbm cfg using DFT_MON_LOCK ON
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.


C:\windows\system32>db2 get monitor switches


Monitor Recording Switches

Switch list for member 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 2015-10-22 10:38:00.507560
Lock Information (LOCK) = OFF <--仍然是off
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 2015-10-22 08:53:46.974751
Unit of Work Information (UOW) = OFF



windows\system32>db2 get snapshot for locks on sample


Database Lock Snapshot

abase name = SAMPLE
abase path = C:\DB2\NODE0000\SQL00005\MEMBER0000\
ut database alias = SAMPLE
ks held = 0
lications currently connected = 2
nts currently waiting on locks = 0
pshot timestamp = 2015-10-22 11:13:54.659754


Application handle = 241
Application ID = *LOCAL.DB2.151022030618
Sequence number = 00008
Application name = db2bp.exe
CONNECT Authorization ID = MIAOQINGSONG
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1386
Locks held = 0
Total wait time (ms) = Not Collected <--数据未收集


C:\windows\system32> db2 attach to db2 <--重新attach到实例

Instance Attachment Information

Instance server = DB2/NT 10.5.4
Authorization ID = MIAOQING...
Local instance alias = DB2


C:\windows\system32>db2 get monitor switches


Monitor Recording Switches


Switch list for member 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 2015-10-22 10:38:00.507560
Lock Information (LOCK) = ON 2015-10-22 11:06:19.158247  <--变成了on
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 2015-10-22 08:53:46.974751
Unit of Work Information (UOW) = OFF




C:\windows\system32>db2 get snapshot for locks on sample


Database Lock Snapshot


Database name = SAMPLE
Database path = C:\DB2\NODE0000\SQL00005\MEMBER0000\
Input database alias = SAMPLE
Locks held = 0
Applications currently connected = 2
Agents currently waiting on locks = 0
Snapshot timestamp = 2015-10-22 11:24:12.346546


Application handle = 241
Application ID = *LOCAL.DB2.151022030618
Sequence number = 00010
Application name = db2bp.exe
CONNECT Authorization ID = MIAOQINGSONG
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1386
Locks held = 0
Total wait time (ms) = 0 <--不再是Not collected