Monitoring Locking with SQLMON
MPE documents | |
 Complete PDF | |
| Table of Contents | |
| Index | |
| E0300 Edition 9 ♥ | |
| E0399 Edition 8 | |
| E0897 Edition 7 |
MONITOR Authority
Users with DBA authority or who are granted MONITOR authority can run SQLMON. Use the GRANT MONITOR command to allow users to run SQLMON. Use the REVOKE MONITOR command to revoke the authority. SYSTEM.SPECAUTH and CATALOG.SPECAUTH identify users with MONITOR authority.
Monitoring Tasks
Table 5-5 summarizes the monitoring tasks related to locking you can perform with SQLMON: Table 5-5 SQLMON Monitoring Tasks
| Task | Screens | Fields |
|---|---|---|
| Determining Size of Runtime Control Block | Overview | RUNTIME CB % Used Pages Max Pages |
| Monitoring DBEnvironment Lock Activity | Load | LOCK REQTS LOCK WAITS LOCK WAIT % |
| Comparing Number of Locks by Table | Lock TabSummary | OWNER.TABLE G TOTAL LOCKS |
| Comparing Number of Locks by Session | Lock Memory | TABLE PAGE ROW TOTAL MAXTOTAL |
| Identifying Locks on a Table or Referential Constraint (PCR) | Lock | OWNER.TABLE[/CONSTRAINT] G PAGE/ROW ID LOCK QUEUE |
| Determining Number of Sessions that are Accessing a Particular Lock | Lock | LOCK QUEUE |
| Determining Number of Transactions that are Waiting for Locks | Overview Load | IMPEDE XACT |
| Identifying Locks for which Sessions are Waiting | Lock | all fields |
| Identifying Sessions that have Obtained a Particular Lock | Lock Object | GWC MOD PIN |
| Identifying Sessions that are Waiting to Obtain (or to Convert) a Particular Lock | Lock Object | GWC MOD NEW PIN |
| Identifying Lock Activity for a Particular Session | Lock Session | all fields |
| Identifying Locks Obtained by a Particular Session that are Causing Other Sessions to Wait | Lock Impede | all fields |
| Detecting Deadlocks | Load Load Session Load Program |
DEADLOCKS |
| Resolving Deadlocks | Lock Lock Object Lock Impede |
all fields |
|
Resolving Conflicts among Concurrent Transactions |
Chapter 6 Names |