How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2?
我如何使用DB2的Explain函数？ - 既可以运行它，也可以用它来优化查询。有没有更好的DB2工具？
I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal.
我之前已经建立过查询，但是我必须告诉他们需要花多长时间的唯一方法就是运行它们并计算时间 - 这几乎不是理想的。
Edit: The answer for me turned out to be "You can't. You don't have and cannot get the access." Don't you love bureaucracy?
编辑：我的答案结果是“你做不到。你没有，也无法获得访问权限。”你不爱官僚吗？
4 个解决方案
#1
18
What you're looking for is covered by two DB2 utilities:
您正在寻找的是两个DB2实用程序：
- The explain utility, which shows the optimizer's access plan and estimated cost for a specific query (based on current RUNSTATS statistics)
- explain实用程序，显示优化程序的访问计划和特定查询的估计成本（基于当前的RUNSTATS统计信息）
- The design advisor, which recommends structural changes to improve the performance of one or more queries
- 设计顾问，建议进行结构更改以提高一个或多个查询的性能
Both utilities require specialized tables to be created in the database.
两个实用程序都需要在数据库中创建专用表。
I tend to use the explain utility more than the advisor, especially if I am able to change the SQL for the query to influence a better access plan. I use the db2expln command-line utility to explain different versions of a query I'm tuning and compare their costs. What's most important is that your table and index statistics are up to date when running explain or the design advisor.
我倾向于使用explain实用程序而不是顾问程序，特别是如果我能够更改查询的SQL以影响更好的访问计划。我使用db2expln命令行实用程序来解释我正在调优的查询的不同版本并比较它们的成本。最重要的是，在运行explain或设计顾问时，您的表和索引统计信息是最新的。
#2
9
IBM offers Data Studio as a free tool built on eclipse, which among other benefits includes a GUI for running visual explain, as well as providing tuning help through a query adviser. I highly recommend using Data Studio.
IBM提供Data Studio作为基于eclipse构建的免费工具，其中包括用于运行可视化解释的GUI，以及通过查询顾问提供调优帮助。我强烈建议使用Data Studio。
It is relatively easy to set up the correct resources (the explain tables that need to be built, and the bind that need to be done) by right clicking a connected data source, and choosing
通过右键单击连接的数据源并选择正确的资源（需要构建的解释表和需要完成的绑定）相对容易
analyze and tune > configure for tuning > guided configuration.
分析和调整>配置调整>引导配置。
To generate the explain graph - simply highlight your query, right click, and choose "Open Visual Explain":
要生成解释图 - 只需突出显示您的查询，右键单击，然后选择“打开Visual Explain”：
To use the query advisor, choose "start tuning" instead. It will take you through a process which will generate the explain, as well as recommend any tuning opportunities it can determine.
要使用查询顾问程序，请选择“开始调整”。它将引导您完成一个过程，该过程将生成解释，并建议它可以确定的任何调整机会。
#3
5
db2expln -database sample -t -g -f "sql query"
db2expln -database sample -t -g -f“sql query”
This worked fine for me.
这对我来说很好。
#4
3
I'\ve always found running the explain utitlity from the command line with '-g' mode turned on has helped me pinpoint problems.
我总是发现从命令行运行解释权限，打开'-g'模式帮助我查明问题。
I can't recall the options now, and IIRC, db2expln is a little fussy about the ordering {i.e you can't put username after password!!} - but it works well.
我现在不记得这些选项了，IIRC，db2expln对排序有点挑剔{即你不能在密码后输入用户名!!} - 但效果很好。
#1
18
What you're looking for is covered by two DB2 utilities:
您正在寻找的是两个DB2实用程序：
- The explain utility, which shows the optimizer's access plan and estimated cost for a specific query (based on current RUNSTATS statistics)
- explain实用程序，显示优化程序的访问计划和特定查询的估计成本（基于当前的RUNSTATS统计信息）
- The design advisor, which recommends structural changes to improve the performance of one or more queries
- 设计顾问，建议进行结构更改以提高一个或多个查询的性能
Both utilities require specialized tables to be created in the database.
两个实用程序都需要在数据库中创建专用表。
I tend to use the explain utility more than the advisor, especially if I am able to change the SQL for the query to influence a better access plan. I use the db2expln command-line utility to explain different versions of a query I'm tuning and compare their costs. What's most important is that your table and index statistics are up to date when running explain or the design advisor.
我倾向于使用explain实用程序而不是顾问程序，特别是如果我能够更改查询的SQL以影响更好的访问计划。我使用db2expln命令行实用程序来解释我正在调优的查询的不同版本并比较它们的成本。最重要的是，在运行explain或设计顾问时，您的表和索引统计信息是最新的。
#2
9
IBM offers Data Studio as a free tool built on eclipse, which among other benefits includes a GUI for running visual explain, as well as providing tuning help through a query adviser. I highly recommend using Data Studio.
IBM提供Data Studio作为基于eclipse构建的免费工具，其中包括用于运行可视化解释的GUI，以及通过查询顾问提供调优帮助。我强烈建议使用Data Studio。
It is relatively easy to set up the correct resources (the explain tables that need to be built, and the bind that need to be done) by right clicking a connected data source, and choosing
通过右键单击连接的数据源并选择正确的资源（需要构建的解释表和需要完成的绑定）相对容易
analyze and tune > configure for tuning > guided configuration.
分析和调整>配置调整>引导配置。
To generate the explain graph - simply highlight your query, right click, and choose "Open Visual Explain":
要生成解释图 - 只需突出显示您的查询，右键单击，然后选择“打开Visual Explain”：
To use the query advisor, choose "start tuning" instead. It will take you through a process which will generate the explain, as well as recommend any tuning opportunities it can determine.
要使用查询顾问程序，请选择“开始调整”。它将引导您完成一个过程，该过程将生成解释，并建议它可以确定的任何调整机会。
#3
5
db2expln -database sample -t -g -f "sql query"
db2expln -database sample -t -g -f“sql query”
This worked fine for me.
这对我来说很好。
#4
3
I'\ve always found running the explain utitlity from the command line with '-g' mode turned on has helped me pinpoint problems.
我总是发现从命令行运行解释权限，打开'-g'模式帮助我查明问题。
I can't recall the options now, and IIRC, db2expln is a little fussy about the ordering {i.e you can't put username after password!!} - but it works well.
我现在不记得这些选项了，IIRC，db2expln对排序有点挑剔{即你不能在密码后输入用户名!!} - 但效果很好。