db2expln参数详解

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.