Using deprecated monitoring features in a DB2 pureScale environment

The IBM® DB2 pureScale Feature extends the DB2 monitoring infrastructure with a rich set of monitor elements that you can use to retrieve information about a DB2 pureScale instance. However, there are some limitations to be aware of when retrieving and interpreting monitoring data using deprecated monitoring interfaces.

This topic describes the limitations to keep in mind when you are using any of the following deprecated features:

Monitoring locking with snapshot monitor

If you use snapshot monitoring commands, functions or views to examine information about locks between members, details about the applications holding locks are only displayed if the application is running on the same member as where the snapshot is being taken. Otherwise, the ID of the application holding the lock are reported as REMOTE APPLICATION, and other information, such as the application ID and the lock mode are omitted. For this reason, consider taking a global snapshot so that information from all members is returned.

For example, Figure 1 shows the output of a GET SNAPSHOT FOR APPLICATION command, where the application holding the lock is on the same member as where the command is run:
Figure 1. Output for GET SNAPSHOT FOR APPLICATION command - command run on member holding lock. In this example, the application holding the lock is running on the same member on which the GET SNAPSHOT command is being run:
 ID of agent holding lock                 = 73
 Application ID holding lock              = *N0.user1.080616184956
 Database partition lock wait occurred on = 0
 Lock name                                = 0x02000400000000000000000054
 Lock attributes                          = 0x00000000
 Release flags                            = 0x00000000
 Lock object type                         = Table
 Lock mode                                = Exclusive Lock (X)
 Lock mode requested                      = Share Lock (S)
 Name of tablespace holding lock          = USERSPACE1
 Schema of table holding lock             = USER1
 Name of table holding lock               = T1
 Data Partition Id of table holding lock  = 0
 Lock wait start timestamp                = 06/16/2009 14:50:26.744694
However, if the lock is held by an application on a remote member, the same report would look like what is shown in Figure 2:
Figure 2. Output for GET SNAPSHOT FOR APPLICATION command - command run on member other than one holding lock. In this example, the application holding the lock is running on a different member on which the GET SNAPSHOT command is being run. The ID of agent holding lock and the Lock mode lines are omitted. Also, the Application ID holding lock is shown as REMOTE APPLICATION.
 Application ID holding lock              = REMOTE APPLICATION
 Database partition lock wait occurred on = 0
 Lock name                                = 0x02000400000000000000000054
 Lock attributes                          = 0x00000000
 Release flags                            = 0x00000000
 Lock object type                         = Table
 Lock mode requested                      = Share Lock (S)
 Name of tablespace holding lock          = USERSPACE1
 Schema of table holding lock             = USER1
 Name of table holding lock               = T1
 Data Partition Id of table holding lock  = 0
 Lock wait start timestamp                = 06/16/2009 14:50:26.744694

If you take a global snapshot, data for all members is returned. You can determine where the lock is being held by examining the lock name from the snapshot output. If you skim through the reports for each of the members, you can quickly find which application holds the lock in question.

Deadlock event monitor

When a deadlock occurs, the deadlock detector generates information used by event monitors that track deadlocks. The CREATE EVENT MONITOR ... FOR DEADLOCKS command, which is deprecated, might not show certain details for deadlocks between members in a DB2 pureScale environment. For deadlock event reporting, the details regarding the application that was the victim of the deadlock might not be shown in the output of the dbevmon tool, as shown in Figure 3 and in Figure 4:
Figure 3. Sample db2evmon output, DB2 pureScale instances

3) Deadlock Event ...
  Deadlock ID:   1
  Deadlock node: 0
  Number of applications deadlocked: 2
  Deadlock detection time: 06/17/2009 14:46:22.543136
  Rolled back Appl participant no: 2
Figure 4. Sample db2evmon output, all other types of DB2 instances

3) Deadlock Event ...
  Deadlock ID:   1
  Deadlock node: 0
  Number of applications deadlocked: 2
  Deadlock detection time: 06/17/2009 14:46:22.543136
  Rolled back Appl participant no: 2
  Rolled back Appl Id: *N0.finance.081217170042
  Rolled back Appl seq number: : 0001
  Rolled back Appl handle: 66
(Whether application details are shown or not is dependent on several factors, none of which are under user control.) However, you can determine which applications are involved in the deadlock by correlating the data reported for the deadlock ID and the rolled-back application participant number with information found in the Deadlocked Connection section of the dbevmon output.
Similarly, the Deadlocked Connection section of the output does not include the application ID, sequence number, or lock mode of the application holding the requested lock. However, the deadlock ID, member ID, lock name, lock time stamp, and participant number of the application holding the lock are displayed. (The member ID appears as Deadlock node in the output of the dbevmon tool.) You can use this information to correlate which applications are causing the contention. The following sample output from the db2evmon tool illustrates this behavior. The information to use for correlating the applications involved in the deadlock is underlined:

5) Deadlock Event ...
 Deadlock ID:   1
 Deadlock node: 0
 Number of applications deadlocked: 2
 Deadlock detection time: 12/17/2008 12:01:12.735436
 Rolled back Appl participant no: 2
 Rolled back Appl Id: *N0.finance.081217170042
 Rolled back Appl seq number: : 0001
 Rolled back Appl handle: 66

6) Connection Header Event ...
 Appl Handle: 66
 Appl Id: *N0.finance.081217170042
 Appl Seq number: 00001
 DRDA AS Correlation Token: *N0.finance.081217170042
 Program Name    : db2bp
 Authorization Id: FINANCE
 Execution Id    : finance
 Codepage Id: 1208
 Territory code: 1
 Client Process Id: 7201
 Client Database Alias: A
 Client Product Id: SQL09070
 Client Platform: Unknown
 Client Communication Protocol: Local
 Client Network Name: so2.torolab.ibm.com
 Connect timestamp: 12/17/2008 12:00:42.176747

 7) Deadlocked Connection ...
 Deadlock ID:   1
 Deadlock Node: 0
 Participant no.: 2
 Participant no. holding the lock: 1
 Appl Id: *N0.finance.081217170042
 Appl Seq number: 00001
 Appl Id of connection holding the lock: REMOTE_APPLICATION
 Lock wait start time: 12/17/2008 12:01:01.607230
 Lock Name       : 0x02000500040000010000000052
 Lock Attributes : 0x00000000
 Release Flags   : 0x00000000
 Lock Count      : 0
 Hold Count      : 0
 Current Mode    : none
 Deadlock detection time: 12/17/2008 12:01:17.730069
 Table of lock waited on      : T2
 Schema of lock waited on     : FINANCE
 Data partition id for table  : 0
 Tablespace of lock waited on : USERSPACE1
 Type of lock: Row
 Mode application requested on lock: NS  - Share (CS/RS)
 Node lock occured on: 2
 Lock object name: 16777220
 Application Handle: 66
 Deadlocked Statement:
   Type     : Dynamic
   Operation: Fetch
   Section  : 201
   Creator  : NULLID
   Package  : SQLC2G17
   Cursor   : SQLCUR201
   Cursor was blocking: FALSE
   Text     : select * from t2

 List of Locks:
...
 
     Database partition          : 0
     Lock Name                   : 0x020004000100FFFFFF81000000000052
     Lock Attributes             : 0x00000008
     Release Flags               : 0x40000000
     Lock Count                  : 1
     Hold Count                  : 0
     Lock Object Name            : 8454145
     Object Type                 : Row
     Tablespace Name             : USERSPACE1
     Table Schema                : FINANCE
     Table Name                  : T1
     Data partition id           : 0
     Mode                        : X   - Exclusive

     Database partition          : 0
     Lock Name                   : 0x02000500000000000000000054
     Lock Attributes             : 0x00000000
     Release Flags               : 0x00000001
     Lock Count                  : 1
     Hold Count                  : 0
     Lock Object Name            : 5
     Object Type                 : Table
     Tablespace Name             : USERSPACE1
     Table Schema                : FINANCE
     Table Name                  : T2
     Data partition id           : 0
     Mode                        : IS  - Intent Share
...
   
 Locks Held:    6
 Locks in List: 6
 Locks Displayed: 6

8) Connection Header Event ...
 Appl Handle: 131137
 Appl Id: *N2.finance.081217170053
 Appl Seq number: 00001
 DRDA AS Correlation Token: *N2.finance.081217170053
 Program Name    : db2bp
 Authorization Id: finance
 Execution Id    : finance
 Codepage Id: 1208
 Territory code: 1
 Client Process Id: 7260
 Client Database Alias: A
 Client Product Id: SQL09070
 Client Platform: Unknown
 Client Communication Protocol: Local
 Client Network Name: so2.torolab.ibm.com
 Connect timestamp: 12/17/2008 12:00:43.542242
 
9) Deadlocked Connection ...
 Deadlock ID:   1
 Deadlock Node: 0
 Participant no.: 1
 Participant no. holding the lock: 2
 Appl Id: *N2.finance.081217170053
 Appl Seq number: 00001
 Appl Id of connection holding the lock: REMOTE_APPLICATION
 Lock wait start time: 12/17/2008 12:00:57.844388
 Lock Name       : 0x020004000100FFFFFF81000000000052
 Lock Attributes : 0x00000000
 Release Flags   : 0x00000000
 Lock Count      : 0
 Hold Count      : 0
 Current Mode    : none
 Deadlock detection time: 12/17/2008 12:01:17.744611
 Table of lock waited on      : T1
 Schema of lock waited on     : FINANCE
 Data partition id for table  : 0
 Tablespace of lock waited on : USERSPACE1
 Type of lock: Row
 Mode application requested on lock: NS  - Share (CS/RS)
 Node lock occured on: 0
 Lock object name: 8454145
 Application Handle: 131137
 Deadlocked Statement:
   Type     : Dynamic
   Operation: Fetch
   Section  : 201
   Creator  : NULLID
   Package  : SQLC2G17
   Cursor   : SQLCUR201
   Cursor was blocking: FALSE
   Text     : select * from t1
 List of Locks:
...
     Database partition          : 2
     Lock Name                   : 0x02000500040000010000000052
     Lock Attributes             : 0x00000008
     Release Flags               : 0x40000000
     Lock Count                  : 1
     Hold Count                  : 0
     Lock Object Name            : 16777220
     Object Type                 : Row
     Tablespace Name             : USERSPACE1
     Table Schema                : FINANCE
     Table Name                  : T2
     Data partition id           : 0
     Mode                        : X   - Exclusive

     Database partition          : 2
     Lock Name                   : 0x02000500000000000000000054
     Lock Attributes             : 0x00000000
     Release Flags               : 0x40000000
     Lock Count                  : 1
     Hold Count                  : 0
     Lock Object Name            : 5
     Object Type                 : Table
     Tablespace Name             : USERSPACE1
     Table Schema                : FINANCE
     Table Name                  : T2
     Data partition id           : 0
     Mode                        : IX  - Intent Exclusive

     Database partition          : 2
     Lock Name                   : 0x02000400000000000000000054
     Lock Attributes             : 0x00000000
     Release Flags               : 0x00000001
     Lock Count                  : 1
     Hold Count                  : 0
     Lock Object Name            : 4
     Object Type                 : Table
     Tablespace Name             : USERSPACE1
     Table Schema                : FINANCE
     Table Name                  : T1
     Data partition id           : 0
     Mode                        : IS  - Intent Share

 Locks Held:    6
 Locks in List: 6
 Locks Displayed: 6 

LIST TABLESPACES and LIST TABLESPACE CONTAINERS commands

Both the LIST TABLESPACES and LIST TABLESPACE CONTAINERS commands were deprecated in DB2 Version 9.7. These commands report only information known on the member they are run on. They do not retrieve information from other members in the instance. So, some data, such as the number of used pages in a table space might not be reported accurately. Use the MON_GET_TABLESPACE and MON_GET_CONTAINER table functions instead.