db2是通过 db2expln命令来查看sql执行计划,来做sql优化的,比如:
db2expln -d appdb -q " select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988" -i -t -g -o exptest.out
执行后会得到这样的结果:
Section Code Page = 1208
Estimated Cost = 28232.197266 执行代价越大,表示该sql执行比较耗时,需要优化
Estimated Cardinality = 0.748132
。。。。。。
下面给出db2expln各个参数的详解。
[db2inst1@localhost ~]$ db2expln -help
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
SQL Explain describes the access plan selection for static SQL statements
in the packages stored in the DB2 Universal Database system catalogs.
Given a database name, package name, package creator, and section number,
SQL Explain 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 <db> = Connect to the database named <db>.
-d <db>
-user <name> <pw> = Connect as user <name> with password <pw>.
-u <name> <pw>
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 <pattern> = The package creator must match <pattern>.
-c <pattern>
-package <pattern> = The package name must match <pattern>.
-p <pattern>
-version <pattern> = The package version must match <pattern>. If not
specified, then the package with the version ''
(the empty string) will be explained.
-section <number> = The section number is <number>. Use 0 (zero) for
-s <number> all sections in the package.
-escape <charater> = 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 <pattern> 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.
-noenv = By default, db2expln will invoke each dynamic SET
statement 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 -statement and -setup ends
-z <character> 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
The specific options available may vary by database server.
Use "db2expln -help -database <db>" to get the options available for
a specific server.