5.12. Create and Activate a Db2 Lock Event Monitor for S4DBs

This guide describes the steps required to create and activate an event monitor on a monitored db2 database that is need for the db2 lockeventmonitor collector of Speedgain for Databases.

5.12.1. 1. Set DB CFG Parameters for Lock Events

First, configure the database to capture lock events by setting the DB CFG parameters to at least WITHOUT_HIST.

Parameters to Set

  • Lock timeout events (MON_LOCKTIMEOUT) = WITHOUT_HIST

  • Deadlock events (MON_DEADLOCK) = WITHOUT_HIST

  • Lock wait events (MON_LOCKWAIT) = WITHOUT_HIST

Example Commands

To update the MON_LOCKTIMEOUT,MON_DEADLOCK and MON_LOCKWAIT parameter, use the following commands:

db2 update db cfg using MON_LOCKTIMEOUT WITHOUT_HIST
db2 update db cfg using MON_DEADLOCK WITHOUT_HIST
db2 update db cfg using MON_LOCKWAIT WITHOUT_HIST
Configuring Lock Wait Threshold

Lockwaits are captured after 5 seconds by default. To adjust this threshold, specify the duration (in microseconds) after which an event gets captured.

  • Lock wait event threshold (MON_LW_THRESH)

Example Command

To set the lock wait event threshold to 10 seconds, use:

db2 update db cfg using MON_LW_THRESH 10000000

5.12.2. 2. Create and Activate the Event Monitor

Next, create the event monitor and make it active.

Example Commands

Set the current schema to db2monitor:

db2 set current schema db2monitor

Create the event monitor for locking and write the data to a table:

db2 "CREATE EVENT MONITOR s4dbs_lock_monitor FOR LOCKING WRITE TO TABLE"

Activate the event monitor:

db2 "SET EVENT MONITOR s4dbs_lock_monitor state 1"
Note
 For DPF Users, ensure the tablespace used for the event monitor tables is distributed over all DPF members.

5.12.3. 3. Activate the Collector in Speedgain

Navigate to the Speedgain for Databases Configuration Section and to the tab Policies to enable the Collector in your desired policy.

Important

The Collector selects and deletes the data directly at the monitored databases to maintain a low size in the monitored database.

5.12.4. Additional Information

More Info on HowTo create an event monitor can be found in the IBM Db2 documentation (for e.g. create the tables in a seperate tablespace with a quota) - https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-event-monitor-locking

← Previous: Use the REST API to manage Speedgains configuration | ↑ Up: HowTo | ⌂ Home: Speedgain for Databases | Next: Create and Activate a Db2 Change History Monitor for S4DBs →