db2 Optimizing queries:db2 优化查询

luzl

db2 -tvf test.sqldb2 runstats on table mytab with distribution and detailed indexes all shrlevel change
上面的db2 -tvf是最普通不过的命令了，就是执行脚本文件，下面的这个命令就是收集表在数据库中的统计信息，收集统计信息的作用就是优化查询计划，查询计划好了，SQL就快了，不过在runstats之前最好先执行以下reorg命令.
db2 reorg table mytab

有些时候会提示让你加上表的模式名，假设你的模式名是:schema:
db2 reorg table schema.mytab
这个方法对于runstats也是适用的。

visual explain A graphical tool available in the db2  Command Center (or Control Center) - to examine single queries, whether static or dynamic. Shows a color-coded tree. Clicking on a node allows you to view the arguments, statistics, and cost estimate of the node. You can also jump directly to DB2's documentation on that node type. Visual Explain can also be run from the command line as db2vexp.exe, though it then lacks static SQL ability.
db2expln The "bare bones" tool, giving text output from static SQL packages only. This can optionally include a character mode graph.
dynexpln Gives a text-mode analysis of a dynamic SQL query. It actually packages the dynamic query and calls db2expln to do the work.  From unix command line invoke it as:
  dynexpln -d mydb -f test.sql
  dynexpln -h
db2exfmt  A formatter for previously stored explain data.

执行计划查看工具:
db2vexp.exe db2自带的图形化执行计划查看工具
db2expln    文本输出的静态SQL执行计划查看工具
dynexpln    文本输出的动态SQL查询计划
dynexpln -d mydb -f test.sql
dynexpln -h
db2exfmt    前面其它工具获得的执行计划格式化工具