Analyzing locking and suspension activity by using the Assistant chat

Overview

The Assistant chat provides a conversational way to interact with your database environment using natural language. Instead of navigating through menus and forms, you can describe what you want to accomplish in your own words. It understands your intent, asks clarifying questions when needed, and guides you through complex tasks step by step.

This capability is useful in the following situations:

• You need to investigate locking problems such as deadlocks or timeout events that are affecting application response times or causing transaction failures.
• You want to identify lock escalation patterns where Db2® converted multiple row-level locks to table-level locks, which can impact concurrency and application performance.
• You need to analyze historical suspension activity to understand trends and identify recurring issues.

The Assistant chat retrieves and analyzes suspension event data collected by IBM Z Database Assistant, providing detailed information about deadlocks, timeouts, and lock escalations. You can specify time ranges to focus on recent issues or analyze historical patterns.

Example prompts

Use the following example prompts to help you write effective queries for analyzing locking and suspension activity:

Analyzing deadlocks

Show me deadlocks that occurred in the last 30 days on connection UTEC279

What deadlocks occurred on subsystem DB2P in the past week on connection UTEC279?

These prompts retrieve deadlock events where two or more transactions were waiting for locks held by each other. The response includes details about the involved transactions, resources, and lock types to help you understand the cause.

Investigating timeouts

What timeouts have occurred on subsystem DB2P in the past week on connection UTEC279?

Show me timeout events from the last 7 days on connection UTEC279

These prompts identify timeout events where transactions exceeded the configured wait time for locks. Understanding timeout patterns helps you tune timeout parameters and identify applications that might need optimization.

Investigating lock escalations

Show me lock escalations that occurred in the last 30 days on connection UTEC279

What lock escalations happened on subsystem DB2P this month on connection UTEC279?

These prompts identify lock escalation events where Db2 converted multiple row-level locks to table-level locks. Understanding escalation patterns helps you tune locking parameters and optimize application design to maintain better concurrency.

Response and returned data

When you submit a lock analysis query, the response includes the following information:

Response format

The response includes a natural language summary of the suspension events, followed by detailed information about each event presented in an easy-to-understand format. Critical patterns and trends are highlighted when relevant.

Returned data

The following data is returned depending on the type of suspension event:

• Deadlock information - Details about the transactions involved, resources being accessed, lock types, and the sequence of events that led to the deadlock
• Timeout information - Details about the transaction that timed out, the lock holder, resources involved, and the configured timeout interval
• Lock escalation information - Details about the escalation event including the database object, lock state, number of locks escalated, and the transaction that triggered the escalation
• Temporal information - Timestamps showing when events occurred to help identify patterns or correlate with application activity
• Resource details - Information about the database objects involved (tables, indexes, tablespaces) to help focus tuning efforts

Data format

Information is returned as conversational text with event details presented in structured format for easy analysis. Multiple events are organized chronologically or by frequency to help identify patterns.

You can use the suspension analysis information to identify problematic applications, tune locking parameters, or optimize database design. The Assistant chat can provide additional details about specific events, explain the significance of particular patterns, or suggest remediation steps for recurring issues.