AS 400 Performance from .Net iSeries Provider

Question

Hey all,

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?

Answer 1

Hi Nathan. 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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

Hi Nathan.. You may want to try a different driver to connect to the AS400-DB2 system. I have used 2 options.

1. the standard jt400.jar driver to create a simple java web service to get my data
2. the drivers from the company called HIT software (www.hitsw.com)

Obviously the first option would be the slower of the two, but thats the free way of doing things.