Why aren't connections being reused in my iSeries/ASP.NET MVC 4 app?

Question

We are running an MVC 4 web application on a Windows 2008 server farm. We have been trying to upgrade our server farm to Windows 2008 R2 64-bit servers but have run into an issue with connection pooling on an iSeries (running V7R1). We frequently call DB2 java stored procedures and have enabled connection pooling to reduce the time it takes to establish connections. Below is an example of the connection string we’re using.

<add name="DB2" connectionString="ConnectionTimeout=45;Pooling=true;MinimumPoolSize=1;MaximumPoolSize=-1;MaximumUseCount=100;CheckConnectionOnOpen=true;DataSource=XXX;Naming=SQL;DataCompression=True;UserID=username;password=pwd;DefaultCollection=XXX" />

Since going to 2008R2, the number of connections (QZDASOINIT jobs) increases on the iSeries steadily, hurting performance on the iSeries and thus in our application. The code base is exactly the same as it was on the 2008 32-bit servers. We have the target platform set for Any CPU and have “Enable 32-bit Applications” set to True in IIS. We tried to upgrade to these servers earlier in the month and resetting IIS didn’t automatically kill the connections on the box like it’s supposed to and would not create any new ones until we completely reverted to our older servers.

It almost seems like it is not picking up connections that have been made and continuously creates new ones. Does anyone know if there is a step we missed when upgrading to 32-bit to 64-bit when it comes to connection pooling with an iSeries?

Answer 1

Recap of the problem: when moving an ASP.NET MVC 4 web application from a 32-bit Windows 2008 server farm to Windows 2008 R2 64-bit servers the number of QZDASOINIT jobs created on our IBM i jumps from around 2,000 to around 200,000. The code base is unchanged, the only thing that has changed is the execution environment.

Some relevant things to know about QZDASOINIT jobs... By default these jobs are created in the subsystem QUSRWRK (though they can also crop up in QSYSWRK and QSERVER if the QUSRWRK subsystem is not active when an ODBC connection is requested). QUSRWRK is configured to create one of these QZDASOINIT jobs as soon as the subsystem is started. If an ODBC request is made and no QZDASOINIT jobs are available, 2 more QZDASOINIT jobs will be started. Each QZDASOINIT job will process 200 ODBC requests before ending. All of these defaults and more can be changed using the CHGPJE or CHGSBSD commands.

QZDASOINIT jobs have one or two “classes”. Execution of these jobs can be fine-tuned using these classes.

You can find out information about your currently active QZDASOINIT jobs by using the DSPACTPJ command.

Resources:

CHGPJE – Change Prestart Job Entry

CHGSBSD – Change Subsystem Description

CRTCLS – Create Class

DSPACTPJ – Display Active Prestart Jobs

ENDHOSTSVR – End Host Server

ENDSBS – End Subsystem

WRKCLS – Work with Classes

Performance considerations with QZDASOINIT jobs

Possible “solution” #1:

In your connection string on the Windows side, change MaximumPoolSize=-1 to MaximumPoolSize=XXX where XXX is some number that allows your ASP.NET application to function reasonably well but doesn't degrade performance on the IBM i. I would suggest using 2,000 since that seemed to be tolerable when the application was running on 32-bit servers.

Possible “solution” #2:

Have an IBM i administrator make some changes on the IBM i – since you know the IP address range of your server farm the administrator can set up a new subsystem that will do nothing but service your applications ODBC connections. Change the maximum number of allowed QZDASOINIT jobs using the CHGPJE command – again, I would suggest starting at 2,000 and adjusting it as needed to satisfy your application's performance and impact on the IBM i. If necessary, the administrator could set up a job that will kill all QZDASOINIT jobs in the new subsystem – either by ending that subsystem (ENDSBS) or ENDHOSTSVR SERVER(*DATABASE) ENDACTCNN(*DATABASE) (I would end the subsystem but your administrator will know what will work best in your environment).

Some other suggestions that aren't solutions themselves but may be helpful:

Limiting the number of concurrently executing threads in your ASP.NET application. Obviously not a quick or easy thing to do but something to put in the notes for the next iteration of the application.

Changing the MaximumUseCount=100 to match whatever use count you're using with the QZDASOINIT jobs.

I put quotes around “solutions” because while they might allow you to get your application moved to the new environment they don't actually fix the problem.

Answer 2

I've seen issues before when an app using connection pools works fine as a stand-a-lone, but when run in an app server you end up with both the app and the app server doing connection pooling. The problem is that the app server never releases the connection.

I'm not familiar enough with ASP.NET or IIS to tell you where to look, but perhaps the above is enough.

If not, it would help to know

• what version of IBM iAccess you've loaded and rather it's 32 or 64 bit and what service pack level you are on.

• the driver being used. I'd assume the .NET driver, but I know MS provides functionality to use ODBC or OLEDB drivers with .NET

Charles