Using deprecated monitoring features in a Db2
pureScale environment
The IBM®
Db2
pureScale Feature extends
the database 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:
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 database 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: 1Deadlock 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: 1Deadlock Node: 0Participant no.: 2Participant 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 occurred 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: 1Deadlock Node: 0Participant no.: 1Participant 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 occurred 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
These commands
are deprecated. They report only information that is known on the member they are
run on. They do not retrieve information from other members in the
instance. Consequently, 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.