Territory code of database: 1

  Server instance name: DB2

  Database Name: SAMPLE

  Database Path: C:\DB2\NODE0000\SQL00001\

  First connection timestamp: 02 01 2004 13:25:43.028006

  Event Monitor Start time:   02 06 2003 09:51:57.663712

3) Deadlock Event ...

  Deadlock ID:   4

  Number of applications deadlocked: 2

  Deadlock detection time: 02 06 2003 09:53:11.952919

  Rolled back Appl participant no: 2

  Rolled back Appl Id: *LOCAL.DB2.010686063633

  Rolled back Appl seq number: : 0005

[...]

Visual Explain

Visual Explain

 is used to capture and view information about the access plan 

chosen by the 

DB2 optimizer

 for SQL statements as a graph. An access plan is 

a cost estimation of resource usage for a query, which is based on the available 

information such as statistics for tables and indexes, instance and database 

configuration parameters, bind options and query optimization level, and so on. 

An access plan also specifies the order of operations for accessing the data.

The access plan acquired from Visual Explain helps to understand how individual 

SQL statements are executed. The information available from the Visual Explain 

graph can be used to tune the SQL queries for better performance.

To start Visual Explain, launch the 

Control Center

, right click the database name 

and select either the 

Explain SQL 

or 

Show Explained Statements History

option. You can input an SQL statement manually or import the SQL statement 

through the 

Get 

button available in the Explain SQL window. You can also specify 

the optimization class for the SQL statement in the same window. The 

optimization class implies the effort the DB2 optimizer will spend on preparing 

the execution plan (higher value means more sophisticated optimization). 

Figure 9 5 shows an example of an access plan graph. 

 Chapter 9. Testing and tuning 

295