Example Outputs of db2pd Command | Generated by AI
Here are some example outputs of the db2pd -locks command and explanations of what the key information means:
Example 1: Basic db2pd -locks Output
Database Partition 0 -- Database SAMPLE -- Active -- Up 2 days 05:50:35
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseObj
0x0000000228A62400 5 00020006000000000000000054 Table X G 1 1 1 0 0x0000000000000000
0x0000000228A62600 6 00020006000000000000000054 Table S G 2 1 1 0 0x0000000000000000
0x0000000228A62800 7 00020006000000000000000054 Table S W 3 1 0 0 0x0000000000000000
Explanation:
- Address: Memory address of the lock control block
- TranHdl: Transaction handle that owns the lock
- Lockname: Hexadecimal identifier for the locked object
- Type: Type of object being locked (Table, Row, etc.)
- Mode:
- X: Exclusive lock (prevents others from reading or writing)
- S: Share lock (allows other share locks but prevents exclusive locks)
- IX: Intent Exclusive (indicates intent to modify rows)
- IS: Intent Share (indicates intent to read rows)
- U: Update lock (can be upgraded to exclusive)
- Sts (Status):
- G: Granted (lock is held)
- W: Waiting (transaction is waiting for lock)
- C: Converting (lock is changing state)
- Owner: Application ID that owns the lock
- Dur (Duration): How long the lock is held (1=transaction duration)
- HoldCount: Number of times lock has been acquired
In this example, we see:
- Transaction 5 has an exclusive lock (X) on a table, preventing others from accessing it
- Transaction 6 has a share lock (S) that has been granted
- Transaction 7 is waiting (W) to acquire a share lock on the same table
Example 2: Detailed db2pd -locks detailed Output
Database Partition 0 -- Database SAMPLE -- Active -- Up 2 days 07:30:22
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseObj
0x0000000228A62400 12 00030008000000000000000054 Table X G 1025 1 1 0 0x0000000000000000
Application Handle: 52
Application ID: *LOCAL.db2inst1.220316190145
Application Name: db2bp
Authorization ID: DB2INST1
Correlation Token: db2inst1.db2bp.220316231245.12
Lock Mode: X (Exclusive)
Lock Status: Granted
Lock Reference Count: 1
Lock Handle Count: 1
0x0000000228A62800 14 00030008000000000000000054 Table S W 1026 1 0 0 0x0000000000000000
Application Handle: 53
Application ID: *LOCAL.db2inst1.220316190226
Application Name: db2bp.exe
Authorization ID: DB2INST1
Correlation Token: db2inst1.db2bp.220316231325.14
Lock Mode: S (Share)
Lock Status: Waiting
Lock Reference Count: 0
Lock Handle Count: 1
Wait Start Time: 2022-03-16-19.03.25.712635
Waited: 32 seconds
Explanation:
The detailed output provides significant additional information:
- Application Handle: Unique identifier for the application
- Application ID: Full application identifier including host, user, and timestamp
- Application Name: Name of the application (db2bp = command line processor)
- Authorization ID: User ID running the application
- Correlation Token: Tracking identifier for the transaction
- Wait Start Time: When the lock wait began
- Waited: How long the transaction has been waiting
In this example:
- Transaction 12 (application handle 52) has an exclusive lock on a table, run by user DB2INST1
- Transaction 14 (application handle 53) has been waiting for 32 seconds to acquire a share lock on the same table
Example 3: Lock Waits with db2pd -locks show waiters
Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 02:15:44
Lock Waiters:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseObj
0x0000000228A62800 22 00030008000000000000000054 Table S W 2048 1 0 0 0x0000000000000000
Lock Holders:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseObj
0x0000000228A62400 18 00030008000000000000000054 Table X G 2045 1 1 0 0x0000000000000000
Waiter-Holder Relationship:
Waiter (TranHdl=22, AppHdl=65) is waiting on:
Holder (TranHdl=18, AppHdl=63)
Explanation:
This output specifically shows the relationship between waiting transactions and the transactions holding the locks they need:
- The “Lock Waiters” section shows transactions waiting for locks
- The “Lock Holders” section shows transactions currently holding locks
- The “Waiter-Holder Relationship” section explicitly shows which transaction is blocking which other transaction
In this example:
- Transaction 22 (application handle 65) is waiting to acquire a share lock
- Transaction 18 (application handle 63) is holding an exclusive lock on the same resource
- The exclusive lock is preventing the share lock from being granted
This information is extremely valuable for troubleshooting deadlocks or lock contention, as it clearly shows which applications are blocking others. You can use the application handles to force applications if necessary.
Key Insights from db2pd Output:
- Identifying Bottlenecks: Find tables with many locks or waiters
- Deadlock Detection: Identify circular wait conditions
- Lock Escalation: See when row locks are being escalated to table locks
- Long-Running Transactions: Identify transactions holding locks for extended periods
- Application Design Issues: Patterns of lock contention may indicate application design problems
The db2pd command is particularly valuable because it doesn’t require database connections and doesn’t affect performance significantly, making it safe to use in production environments.