Snapshot monitor sample output

You can use the snapshot monitor to capture information about your database and applications that are connected to your database at a specific time. Information collected in a snapshot include the database name, the number of locks held by the database, and application handles.

To illustrate the nature of the snapshot monitor, here is an example of a snapshot being taken using the CLP, along with its corresponding output. The objective in this example is to obtain a list of the locks held by applications connected to the SAMPLE database. The steps taken are as follows:

Connect to the sample database:
```
db2 connect to sample
```
Turn on the LOCK switch with the UPDATE MONITOR SWITCHES command, so that the time spent waiting for locks is collected:
```
db2 update monitor switches using LOCK on
```
Issue a command or statement that will require locks on the database catalogs. In this case, we will declare, open, and fetch a cursor:
```
db2 -c- declare c1 cursor for
                       select * from staff where job='Sales' for update
db2 -c- open c1
db2 -c- fetch c1
```
Take the database lock snapshot, using the GET SNAPSHOT command:
```
db2 get snapshot for locks on sample
```

After the GET SNAPSHOT command is issued from the CLP, the snapshot output is directed to the screen.

            Database Lock Snapshot

Database name                              = SAMPLE
Database path                              = C:\DB2\NODE0000\SQL00001\
Input database alias                       = SAMPLE
Locks held                                 = 5
Applications currently connected           = 1
Agents currently waiting on locks          = 0
Snapshot timestamp                         = 06-05-2002 17:08:25.048027

Application handle                         = 8
Application ID                             = *LOCAL.DB2.0098C5210749
Sequence number                            = 0001
Application name                           = db2bp.exe
CONNECT Authorization ID                   = DB2ADMIN
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1252
Locks held                                 = 5
Total wait time (ms)                       = 0

List Of Locks
 Lock Name                   = 0x02000300050000000000000052
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 5
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = DB2ADMIN
 Table Name                  = STAFF
 Mode                        = U

 Lock Name                   = 0x02000300000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 3
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = DB2ADMIN
 Table Name                  = STAFF
 Mode                        = IX

 Lock Name                   = 0x01000000010000000100810056
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Variation Lock
 Mode                        = S

 Lock Name                   = 0x41414141414A48520000000041
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x434F4E544F4B4E310000000041
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

From this snapshot, you can see that there is currently one application connected to the SAMPLE database, and it is holding five locks.

     Locks held                                 = 5
     Applications currently connected           = 1

Note that the time (Status change time) when the Application status became UOW Waiting is returned as Not Collected. This is because the UOW switch is OFF.

The lock snapshot also returns the total time spent so far in waiting for locks, by applications connected to this database.

     Total wait time (ms)                       = 0