DB2 SQL执行计划

在oracle数据库中可以在sqlplus中打开trace选项后查看sql的执行计划，在DB2数据库中同样也有类似的功能，DB2提供了一个比较简单的工具db2expln工具来查看SQL的执行计划
可以通过输入db2expln来查看其帮助信息。

C:/Documents and Settings/liufeng>db2expln

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool


A database name must be specified.



SQL and XQUERY Explain Tool describes the access plan selection for static SQL s
tatements
in the packages stored in the DB2 Universal Database system catalogs.
Given a database name, package name, package creator, and section number,
SQL and XQUERY Explain Tool will interpret and describe the information in these
catalogs.

The syntax is:

.-----------.
V |
>>--db2expln----<option>--+--><

where <option> and <parameter> are taken from the list below. Each <option>
may appear only once, and they may be specified in any order.


Connection Options:
-database = Connect to the database named .
-d

-user = Connect as user with password .
-u

A database name must be specified.


Output Options:
-terminal = Send output to the terminal.
-t

-output <file> = Write output to the file named <file>.
-o <file>

Either terminal or file output must be specified.


Help Options:
-help = Display this help text.
-h
-?


Package Options:
-schema = The package creator must match .
-c

-package = The package name must match .
-p

-version = The package version must match . If not
specified, then the package with the version ''
(the empty string) will be explained.

-section = The section number is . Use 0 (zero) for
-s all sections in the package.

-escape = Use <character> as the escape character when
-e <character> matching patterns.

-noupper = Do not upper case creator, package and version
-l before matching.

The creator and package information must be specified unless dynamic SQL is
being explained. If the section information is not specified, then all
sections will be displayed.

The for creator, package, and version is in LIKE predicate form,
which allows the percent sign (%%) and underscore (_) as pattern matching
characters. This allows multiple packages to be explained with one
invocation of db2expln. The escape character can be used to force the %%
and _ characters to be treated literally. (See the SQL Reference for more
information on the LIKE predicate.) If multiple packages may be matched,
the section number is automatically set to 0 (all sections).


Dynamic Statement Options:
-statement <statement> = The dynamic statement <statement> will be
-q <sql> explained.

-stmtfile <file> = The dynamic statements contained in the file
-f <file> <file> will be explained. <File> must exist at
the client.

-cache ,,,
= Retrieve the statement identified by the given IDs
from the dynamic SQL cache. (The IDs can be
obtained by running db2pd with the -dynamic
option.

-noenv = By default, each statement that affects the compile

environment will be invoked after it has been
explained. This option prevents the execution of
these statements.


Explain Options:
-setup <file> = The SQL statements in <file> will be invoked
before any sections or statements are explained.
The SQL statements in <file> will not be
explained. Errors in the setup script are reported
but ignored.

-terminator <character> = Each SQL statement for -setup, -statement, and
-z <character> -stmtfile ends at <character>. If this option is
not specified, then each statement is assumed to
be one line long

-graph = Reconstruct the original optimizer plan graph (as
-g presented by Visual Explain). Note that the
reconstructed graph may not exactly match the
original plan.

-opids = Show the operator ID numbers.
-i

-nostats = Do not show compiler statistics in the graph.


Event Monitor Options:
-actevm = The name of the activities event monitor whose
activitystmt logical grouping contains the section
environments to be explained.
-actevm must be specified if -appid, -uowid,
-actid or -actid2 are to be specified.
-appid = The application identifier uniquely identifying
the application that issued the activities whose
section environments are to be explained.
-uowid = The unit of work ID whose section environments are
to be explained. The unit of work ID is unique
only within a given application.
-actid = The activity ID whose section environments are to
be explained. The activity ID is only unique
within a given unit of work.
-actid2 = The activity secondary ID whose section
environments are to be explained. This defaults
to zero if not specified.


The specific options available may vary by database server.
Use "db2expln -help -database " to get the options available for
a specific server.

从以上信息中可以看出该工具可以查看package以及sql的执行计划
以下是某个sql的执行计划的例子：
C:/Documents and Settings/liufeng>db2expln -d mydb -u sde esrichina -statement "
select db2gse.st_astext(shape) from sde.streets where objectid_1>10 and objectid
_1<100" -terminal -g

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"SDE"


Statement:

select db2gse.st_astext(shape)
from sde.streets
where objectid_1>10 and objectid_1<100


Section Code Page = 1208

Estimated Cost = 259.877533
Estimated Cardinality = 88.970406

Access Table Name = SDE.STREETS ID = 2,280
| Index Scan: Name = SDE.R11_RK1 ID = 2
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: OBJECTID_1 (Ascending)
| #Columns = 0
| Compressed Table
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| #Key Columns = 1
| | Start Key: Exclusive Value
| | | | 1: 10
| | Stop Key: Exclusive Value
| | | | 1: 100
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 89.000000
| | | | Row Width = 20
| | | Piped
| | | Duplicate Elimination
Sorted Temp Table Completion ID = t1
List Prefetch Preparation
| Access Table Name = SDE.STREETS ID = 2,280
| | #Columns = 1
| | Compressed Table
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | RID List Fetch Scan
| | Fetch Using Prefetched List
| | | Prefetch: 6 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 2
Nested Loop Join
| Piped Inner
| Access Table Name = DB2GSE.GSE_SPATIAL_REFERENCE_SYSTEMS ID = 4,5
| | Index Scan: Name = DB2GSE.GSE_SRS_ID ID = 2
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: SRS_ID (Ascending)
| | #Columns = 8
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | Evaluate Predicates Before Locking for Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

Rows
Operator
(ID)
Cost

88.9704
n/a
RETURN
( 1)
259.878
|
88.9704
n/a
NLJOIN
( 2)
259.866
/--------/ /-------/
88.9704 1
n/a n/a
FETCH FETCH
(--) ( 7)
47.3244 15.1409
/ / /-------/ /
88.9704 2.72109e+06 1 326
n/a n/a n/a n/a
RIDSCN Table: IXSCAN Table:
( 4) SDE ( 8) DB2GSE
15.2035 STREETS 7.57664 GSE_SPATIAL_REFERENCE_SYSTEMS
| |
88.9704 326
n/a Index:
SORT DB2GSE
( 5) GSE_SRS_ID
15.2032
|
88.9704
n/a
IXSCAN
( 6)
15.1872
|
2.72109e+06
Index:
SDE
R11_RK1




DB2的SQL执行计划输出还是比oracle的详细的多。至于以上的各项信息可以查看DB2的帮助文档。

文章来源：http://blog.csdn.net/liufeng1980423/article/details/6340477