1 snapshot monitor

The data collected by DB2 SNAPSHOT MONITOR is stored in memory through the DBM level parameter MON_HEAP_SZ sets the size of the storage space.

 

What the DB2 monitor can monitor:

• Buffer pool: the number of reads and writes and the time taken.
• Lock: number of locks and deadlocks maintained.
• Sort: number of all heaps, overflow, sorting performance.
• Statement: start time, stop time and finish time of SQL statement.
• Table: measurement activity (read line, write line)
• Timestamp: record timestamp.
• Unit of work: start time, end time and completion time of the work unit.

 

Monitor elements:

• Counter: used to store the cumulative number of activities or events (for example, the total number of executed SQL statements for a database). The counter value increases throughout the life cycle of the monitor: in many cases, it may be reset.
• Metric value: indicates the current value of an item (for example, the number of applications currently connected to the database). Different from the counter value, the value of Gauges can be higher or lower; Their real-time values at any measured point usually depend on the level of database activity.
• High water level value: indicates the maximum or minimum value that an indicator can reach after monitoring (for example, the maximum value used by util_heap_sz).
• Information elements: provide details of the execution of all monitoring activities (such as buffer pool name, database name and alias, detailed path, etc.).
• Timestamp: indicates the date and time when an activity or event occurred (for example, the date and time when the first connection to the database was established). Time stamps are regarded as seconds and subtle quantities that have elapsed since January 1, 1970.
• Time element: the cost of recording time spent on performing an activity or event (for example, the time spent on sorting operations). The value of the time element is expressed in seconds and subtle quantities from the beginning of the activity or event. Some time elements can be reset.

 

Check whether the global monitor switch is on

[db2inst1]# db2 get dbm cfg | grep DFT_MON
   Buffer pool                         (DFT_MON_BUFPOOL) = ON
   Lock                                   (DFT_MON_LOCK) = ON
   Sort                                   (DFT_MON_SORT) = ON
   Statement                              (DFT_MON_STMT) = ON
   Table                                 (DFT_MON_TABLE) = ON
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = ON

 

Check whether the application level monitor switch is on

[db2inst1]# db2 get monitor switches 

            Monitor Recording Switches

Switch list for member 0
Buffer Pool Activity Information  (BUFFERPOOL) = ON  01/20/2022 20:55:09.399500
Lock Information                        (LOCK) = ON  01/20/2022 20:55:09.399500
Sorting Information                     (SORT) = ON  01/20/2022 20:55:09.399500
SQL Statement Information          (STATEMENT) = ON  01/20/2022 20:55:09.399500
Table Activity Information             (TABLE) = ON  01/20/2022 20:55:09.399500
Take Timestamp Information         (TIMESTAMP) = ON  01/20/2022 20:55:09.399500
Unit of Work Information                 (UOW) = ON  01/20/2022 20:55:09.399500

 

Turn on LOCK monitor

[db2inst1]# db2 update dbm cfg using DFT_MON_LOCK on    #The instance needs to be restarted to take effect
 perhaps
[db2inst1]# db2 update dbm cfg using LOCK on

 

Reset all counters under the instance

[db2inst1]# db2 reset monitor all

 

Reset all counters for a database

[db2inst1]# db2 reset monitor for [database | db][database alias]

2 use get snapshot to monitor

View help commands

db2 => ? get snapshot
GET SNAPSHOT FOR {DATABASE MANAGER | ALL [DCS] DATABASES |
ALL [DCS] APPLICATIONS | ALL BUFFERPOOLS | [DCS] APPLICATION
{APPLID appl-id | AGENTID appl-handle} | FCM FOR ALL DBPARTITIONNUMS |
LOCKS FOR APPLICATION {APPLID appl-id | AGENTID appl-handle} |
{ALL | [DCS] DATABASE | [DCS] APPLICATIONS | TABLES |
TABLESPACES | LOCKS | BUFFERPOOLS | DYNAMIC SQL [write to file]}
ON database-alias} [AT MEMBER member-number | GLOBAL]

 

View all monitoring

db2 get snapshot for all

 

View instance level monitoring

db2 get snapshot for dbm

 

View a database monitor

db2 get snapshot for database on dbname

 

View a database tablespace monitor

db2 get snapshot for tablespaces on dbname

 

View the bufferpool monitoring of a database

db2 get snapshot for bufferpools on dbname

 

View all monitoring tables in a database

db2 get snapshot for tables on dbname

 

View all LOCK monitoring of a database

db2 get snapshot for locks on dbname

 

View all application monitoring for a database

db2 get snapshot for applications on dbname

 

View dynamic SQL monitoring of a database

db2 get snapshot for dynamic sql on dbname

 

View a database health

db2 get health snapshot for database on dbname

3 use table function monitoring

Table function	remarks
SNAPSHOT_DBM	Database manager information

 

 

 

 

 

 

 

 

Not finished, to be updated!!!

db2 get snapshot for tablespace on dbname