AS400 Data Connection in ASP.NET

Question

I have an application that will reside within a business2business network that will communicate with our AS400 in our internal network environment. The firewall has been configured to allow the data request through to our AS400, but we are seeing a huge lag time in connection speed and response time. For example what takes less than a half second in our local development environments is taking upwards of 120 seconds in our B2B environment.

This is the function that we are utilizing to get our data. We are using the enterprise library application blocks, so the ASI object is the Database...

/// <summary>
/// Generic function to retrieve data table from AS400
/// </summary>
/// <param name="sql">SQL String</param>
/// <returns></returns>
private DataTable GetASIDataTable(string sql)
{
    DataTable tbl = null;

    HttpContext.Current.Trace.Warn("GetASIDataTable(" + sql + ") BEGIN");
    using (var cmd = ASI.GetSqlStringCommand(sql))
    {
        using (var ds = ASI.ExecuteDataSet(cmd))
        {
            if (ds.Tables.Count > 0) tbl = ds.Tables[0];
        }
    }
    HttpContext.Current.Trace.Warn("GetASIDataTable() END");
    return tbl;
}

I am trying to brainstorm some ideas to consider as to why this is occurring.

Answer 1

Sorry but I can't tell you what is going on but I just have a couple comments... First I would output the sql, see if it has a lot of joins and/or is hitting a table (file) with a large amount of records. If you really want to dig in fire up your profiler of choice (I use Ants Profiler) and try to find a profiler for the 400 - see what the server resources are as well as actual query after it goes thru the odbc driver.

I have worked with asp.net and as400 a few times and the way I have been most successful is actually using sql server with a linked server to AS400. I created a view to make it simpler to work with - hiding the oddities of as400 naming. It worked well in my scenario because the application needed to pull information from sql server anyway.

I thought I would mention it in case it helps... best of luck

Answer 2

Have never used ASP.NET or AS400 in anger, but I have seen this kind of behaviour before and it usually indicated some kind of network problem, typically a reverse DNS lookup that is timing out.

Assuming you have ping enabled through your firewall, check that you can ping in both directions.

Also run traceroute from each machine to try and diagnose where a delay might be.

Hope that helps.

Answer 3

Check the size of your iSeries system as well. Depending on the size of the query and if the system is undersized for the applications running on it, this may take time. While it shouldn't be thrown out as a posibility, I have seen a similar behavior in the past. But of course more likely is a network issue.

The other idea if you can solve the speed issue or is a sizing problem is to store it in an MS SQL Server then write the records from SQL Server to the iSeries from there.