AS/400 查询响应时间
我正在做什么......
我正在 C#/.Net 应用程序中通过 ODBC 连接到 AS400（目前我必须使用 ODBC）。 查询速度太慢了。我运行 DBmonitor &查询它返回的文件，其中 QQIDXA = 'Y'，并且我被告知创建已经存在的索引。原因代码是 I1 & I3.
我已经按照与 where 子句相同的顺序重建了索引，但没有成功。
我可以做什么来改进我的 AS400 查询？？？
阿希亚， 拉里·R...
What I'm doing....
I am connecting to an AS400 via ODBC (currently I have to use ODBC) in a C#/.Net application.
The queries are dog slow. I run DBmonitor & query the file that it returns where QQIDXA = 'Y' and I'm being told to create indexes, that already exist. The reason codes are I1 & I3.
I have rebuilt the indexes, in the same order that the where clauses are, with no luck.
What can I do to improve my AS400 queries???
AHIA,
LarryR...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我在使用 DB2/400 查询和索引时遇到的一个常见问题与字段属性有关。例如，如果您使用不具有相同属性的字段连接两个表，则数据库可能必须在派生临时字段后创建一个特殊的即时索引。例如，Numeric(8,0) 与 Decimal(11,3) 不同。尽管您可以将它们连接在一起，但通常会创建临时索引。可以肯定的是，这很烦人。我经常使用的修复方法是在连接语句中强制转换字段，以便我始终使用正确的属性将字段连接在一起。下面是一个示例：
对于 WHERE 子句，有时（尽管不那么常见）也会出现同样的情况。如果强制转换连接没有帮助，请尝试根据您的选择标准使用 CAST。
这是查询性能优化的另一个技巧。通过 ODBC 连接之后、执行查询之前，在调试会话中使用断点启动 C# 程序。然后使用 WRKACTJOB JOB(QZDASOINIT) 在 AS/400 (iSeries) 上查找 ODBC 作业。查找包含您的用户个人资料的职位。或者至少是连接时使用的用户配置文件。当您找到该作业时，使用以下两个命令将其置于调试模式（从绿屏）：（
注意：作业编号将根据您找到的 ODBC 作业而变化）。
然后从 C# 程序运行查询并检查 AS/400 上所选 QZDASOINIT 作业的作业日志。当 SQL 在处于调试模式的作业中运行时，您会在作业日志中获得各种有用的内容。完成后，请记住运行 ENDDBG，然后运行 ENSRVJOB。
A common problem I get with DB2/400 queries and indexes has to do with field attributes. If, for example, you're joining two tables using fields that do not have the same attributes, then the database may have to create a special, on-the-fly index after deriving a temporary field. For example, a Numeric(8,0) is not the same as a Decimal(11,3). Even though you can join them together, a temporary index is often created. This is an annoyance, to be sure. The fix I often use is to cast my fields in the join statement so that I'm always joining the fields together so with the correct attributes. Here is an example:
The same can sometimes--though not as often--be true for your WHERE clause. If casting the joins does not help, try using CASTs with your selection criteria.
Here's another tip for query performance optimization. Start your C# program in a debug session with a breakpoint after you connect via ODBC and before you execute your query. Then find your ODBC job on the AS/400 (iSeries) by using WRKACTJOB JOB(QZDASOINIT). Look for the job that has your user profile on it. Or at least the user profile used when connecting. When you find that job, put it in debug mode with these two commands (from a green screen):
(Note: the job number will change depending on the ODBC job you find).
Then run your query from the C# program and examine the job log of the selected QZDASOINIT job on the AS/400. When an SQL is run in a job that is in debug mode, you get all sorts of helpful stuff in the job log. When you're finished, remember to run ENDDBG and then ENDSRVJOB.
首先，一些术语。 AS/400 是一台非常非常非常老的机器。现在有些人出于习惯坚持将后继型号称为“AS/400”。当您搜索网络时，这可能会对您不利。 IBM 在 AS/400 之后生产了几代中端机器。尝试 iSeries、System i 和 IBM i。
这些中型机器上的数据库是 DB2 的变体，因此调整起来与任何其他数据库非常相似。正确的索引通常是数据库性能问题的首要解决方案。不要只看 WHERE 子句；还要考虑任何 JOIN ON 和 ORDER BY。
IBM 有一个名为 Client Access（iSeries Access、IBM i Access）的软件，该软件有一个名为 Navigator 的免费组件。 Navigator 有一个非常好的数据库工具，称为 VisualExplain。让中端管理员在您的 PC 上安装 Navigator。
IBM 发布了几本有关数据库调优的红皮书，可能会有所帮助：
iSeries 通用数据库
First, some nomenclature. AS/400 is a very, very, very old machine. Now some people insist on calling successor models 'AS/400' out of habit. This may work against you when searching the web. IBM made several generations of midrange machines after AS/400. Try iSeries, System i, and IBM i.
The database on these midrange machines is a variant of DB2, and as such is tuned pretty much like any other database. The proper indexes are usually the first solution to database performance problems. Don't look only at the WHERE clause; consider any JOIN ON and ORDER BY as well.
IBM has software called Client Access (iSeries Access, IBM i Access) which has a free component called Navigator. The Navigator has a very good DB tool called Visual Explain. Have the midrange admin install Navigator on your PC.
IBM published several Redbooks on database tuning that might help:
Universal Database for iSeries
您可能还需要考虑在 MIDRANGE-L 邮件列表上发布您的问题。
那里有很多 DB2 专家。
You might also want to consider posting your question on the MIDRANGE-L mailing list.
Lots of DB2 experts there.