.Net iSeries 提供商提供的 AS 400 性能
首先，我根本不是 AS 400 人。所以请原谅我在这里问任何新手问题。
基本上，我正在开发一个需要访问 AS400 以获得一些实时数据的 .Net 应用程序。尽管我的系统正在运行，但我在查询之间得到了截然不同的性能结果。通常，当我对 AS400 上的 SPROC 发出第一个请求时，我发现大约需要 14 秒才能获取完整的数据集。在初始调用之后，任何后续调用通常只需约 1 秒即可返回。此性能改进持续约 20 分钟左右，然后再次需要 14 秒。
有趣的是，如果直接在 iSeries Navigator 上执行存储过程，它总是在几毫秒内返回（响应时间没有变化）。
我想知道这是否是缓存/执行计划问题，但我只能将 SQL SERVER 逻辑应用到 AS400，这并不总是匹配。
有什么建议可以帮助我获得更一致的响应时间，或者简单地了解当我使用 iSeries Data Provider for .Net 时 AS400 为何以这种方式运行？我应该使用更好的访问方法吗？
为了以防万一，这是我用来连接到 AS400 编辑的代码
Dim Conn As New IBM.Data.DB2.iSeries.iDB2Connection(ConnectionString)
Dim Cmd As New IBM.Data.DB2.iSeries.iDB2Command("SPROC_NAME_HERE", Conn)
Cmd.CommandType = CommandType.StoredProcedure
Using Conn
Conn.Open()
Dim Reader = Cmd.ExecuteReader()
Using Reader
While Reader.Read()
'Do Something
End While
Reader.Close()
End Using
Conn.Close()
End Using
：在查看了一些关于这个问题并使用下面的一些评论之后，我开始怀疑我是否由于连接池的收益而遇到了这种情况？想法？
First off, I am not an AS 400 guy - at all. So please forgive me for asking any noobish questions here.
Basically, I am working on a .Net application that needs to access the AS400 for some real-time data. Although I have the system working, I am getting very different performance results between queries. Typically, when I make the 1st request against a SPROC on the AS400, I am seeing ~ 14 seconds to get the full data set. After that initial call, any subsequent calls usually only take ~ 1 second to return. This performance improvement remains for ~ 20 mins or so before it takes 14 seconds again.
The interesting part with this is that, if the stored procedure is executed directly on the iSeries Navigator, it always returns within milliseconds (no change in response time).
I wonder if it is a caching / execution plan issue but I can only apply my SQL SERVER logic to the AS400, which is not always a match.
Any suggestions on what I can do to recieve a more consistant response time or simply insight as to why the AS400 is acting in this manner when I was using the iSeries Data Provider for .Net? Is there a better access method that I should use?
Just in case, here's the code I am using to connect to the AS400
Dim Conn As New IBM.Data.DB2.iSeries.iDB2Connection(ConnectionString)
Dim Cmd As New IBM.Data.DB2.iSeries.iDB2Command("SPROC_NAME_HERE", Conn)
Cmd.CommandType = CommandType.StoredProcedure
Using Conn
Conn.Open()
Dim Reader = Cmd.ExecuteReader()
Using Reader
While Reader.Read()
'Do Something
End While
Reader.Close()
End Using
Conn.Close()
End Using
EDIT: after looking about a bit on this issue and using some of the comments below, I am beginning to wonder if I am experiencing this due to the gains from connection pooling? Thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我找到了红皮书 Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET 对于诊断此类问题很有用。
具体查看客户端和服务器端跟踪以帮助隔离问题。不要害怕致电 IBM 软件支持。他们可以帮助您设置分析以找出问题。
I've found the Redbook Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET useful for diagnosing issues like these.
Specifically look into the client and server side traces to help isolate the issue. And don't be afraid to call IBM software support. They can help you set up profiling to figure out the issue.
您可能想要尝试不同的驱动程序来连接到 AS400-DB2 系统。我用过2个选项。
显然，第一个选项是两个选项中较慢的一个，但这是免费的方式做事。
You may want to try a different driver to connect to the AS400-DB2 system. I have used 2 options.
Obviously the first option would be the slower of the two, but thats the free way of doing things.
每个与 iSeries 的连接都由一项作业支持。第一次连接时，iSeries 驱动程序需要创建连接池、启动作业并将该作业与连接对象关联。当您关闭连接时，驱动程序会将该对象返回到连接池，但不会结束服务器上的作业。
您可以打开跟踪来确定第一次连接尝试时发生的情况。为此，请将“Trace=StartDebug”添加到连接字符串中，并在运行代码的机器上启用跟踪日志记录。您可以使用客户端访问程序目录中的“cwbmptrc”工具来执行此操作：
跟踪输出将让您深入了解驱动程序正在执行哪些操作以及每个操作需要多长时间才能完成。只是不要忘记在完成后关闭跟踪（cwbmptrc.exe -a）
如果您不想扰乱跟踪，可以通过添加以下命令来禁用它，以确定连接池是否落后于延迟“Pooling=false”到您的连接字符串。如果连接池是您第二次尝试速度更快的原因，则禁用连接池应该使每个请求的执行速度与第一个请求一样慢。
Each connection to the iSeries is backed by a job. Upon the first connection, the iSeries driver needs to create the connection pool, start a job, and associate that job with the connection object. When you close a connection, the driver will return that object to the connection pool, but will not end the job on the server.
You can turn on tracing to determine what is happening on your first connection attempt. To do so, add "Trace=StartDebug" to your connection string, and enable trace logging on the box that is running your code. You can do this by using the 'cwbmptrc' tool in the Client Access program directory:
The trace output will give you insight into what operations the driver is performing and how long each operation takes to complete. Just don't forget to turn tracing off once you are done (cwbmptrc.exe -a)
If you don't want to mess with the tracing, a quick test to determine if connection pooling is behind the delay is to disable it by adding "Pooling=false" to your connection string. If connection pooling the is reason that your 2nd attempt is much faster, disabling connection pooling should make each request perform as slowly as the first.
几年来，我已经看到 iSeries SQL (ODBC) 查询具有类似的性能。我认为这是野兽本质的一部分——OS/400 在访问数据时动态地将数据从磁盘移动到内存。
FWIW，我还注意到 iSeries 更像是拖拉机而不是赛车。它可以更好地处理大负载。在一个案例中，我将大约十几个短查询合并为一个巨大的查询，并将执行时间从大约 20 秒减少到大约 2 秒。
I have seen similar performance from iSeries SQL (ODBC) queries for several years. I think it's part of the nature of the beast-- OS/400 dynamically moves data from disk to memory when it's accessed.
FWIW, I've also noticed that the iSeries is more like a tractor than a race car. It deals much better with big loads. In one case, I consolidated about a dozen short queries into a single monstrous one, and reduced the execution time from something like 20 seconds to about 2 seconds.
过去我不得不从 AS/400 中提取数据，基本上有几件事对我有用：
1）每晚将数据转储到 SQL Server 表中，我可以在其中控制索引，本机 SqlClient 击败了 IBM每次都使用 DB2 .NET 客户端
2) 与您的一位 AS400 程序员交谈，并确保您使用的命令是击中逻辑文件而不是物理文件（他们世界中的逻辑 v/s 物理类似于我们的表 v/的意见）
3) 使用 SQL Server 上的链接服务器创建视图并查询您的视图。
I have had to pull data from the AS/400 in the past, basically there were a couple of things that worked for me:
1) Dump data into a SQL Server table nightly where I could control the indexes, the native SqlClient beats the IBM DB2 .NET Client every time
2) Talk to one of your AS400 programmers and make sure the command you are using is hitting a logical file as opposed to a physical (logical v/s physical in their world is akin to our tables v/s views)
3) Create Views using a Linked Server on SQL server and query your views.
当从 Websphere Application Server (WAS) 上托管的 Java 解决方案以及 IIS 上托管的 .Net 解决方案连接到 Iseries 数据时，我观察到了相同的行为。一天中的第一个电话总是比第二个电话“贵”。
第一次调用的延迟是由 Iseries 设置作业来服务请求的“设置”时间造成的（子系统 QUSRWRK 中的作业名称为 QZDASOINIT）。后续调用将重用保持活动状态以等待更多传入请求的现有作业。
QZDASOINIT 作业的数量以及它们保持活动状态的时间可以在 Iseries 上进行配置。
一份有关如何调整预启动作业条目的文档：
http://www.ibmsystemsmag.com/ibmi/tipstechniques/系统管理/调整-Prestart-Job-Entries/
我想这是一个合理的假设，WAS 和 IIS 上的“当天的第一次调用”也有一些开销。
I have observed the same behavior when connecting to Iseries data from Java solutions hosted on Websphere Application Server (WAS) as well as .Net solutions hosted on IIS. The first call of the day is always more "expensive" than the second.
The delay on the first call is caused by the "setup" time for the Iseries to set up the job to service the request, (job name is QZDASOINIT in subsystem QUSRWRK). Subsequent calls will reuse the existing jobs that stay active waiting for more incoming requests.
The number of QZDASOINIT jobs and how long they stay active is configurable on the Iseries.
One document on how to tune your prestart job entries:
http://www.ibmsystemsmag.com/ibmi/tipstechniques/systemsmanagement/Tuning-Prestart-Job-Entries/
I guess it would be a reasonable assumption that there is also some overhead to the "first call of the day" on both WAS and IIS.
尝试创建一个存储过程。这将使用存储过程创建并缓存您的访问计划，因此优化器不必查看 SQL 缓存或重新优化。
Try creating a stored procedure. This will create and cache your access plan with the stored procedure, so optimizer doesn't have to look in the SQL cache or reoptimize.