14

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?

Joel Anair
  • 13,832
  • 3
  • 31
  • 36
  • I'd suspect that what you have is that there was always a connection leak in the code, but due to changes in the environment, resulting in fewer GCs/app pool recycles, that the leak is now making itself apparent. – Damien_The_Unbeliever Dec 13 '13 at 15:46
  • "_...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..._" - kill which connections on which box? Are you talking about the QZDASOINIT jobs on the IBM i? They should NOT be killed by your app (and you app shouldn't even be able to kill them). – Benny Hill Dec 13 '13 at 15:47
  • @Benny Hill, prior to the system update, restarting IIS would clear the QZDASONIT jobs on the iSeries. We aren't doing anything explicit in the app to end or attempt to manage those jobs beyond enabling connection pooling in the connection string. – Joel Anair Dec 13 '13 at 15:56
  • Those server jobs are intended to live for a long time - until the IBM i gets restarted. The system will prestart a certain (configurable) number and will then create more as demand goes beyond the prestarted number. If demand drops, the jobs will stay on the system waiting to be reused. While waiting for a connection, these jobs don't consume system resources; they will only do that for an active connection. WRKACTJOB will tell you if these jobs are consuming resources or just waiting. – Buck Calabro Dec 13 '13 at 16:09
  • Thanks, @BuckCalabro. The issue is that before the switch to 64-bit Windows servers, that number was reasonable (~2000). After the update, we've seen that number of QZDASOINIT jobs increase to as many as 200,000. That and the fact that **every** connection creates new QZDASOINIT jobs leads us to believe that there is an issue with the connection pooling. Or something. – Joel Anair Dec 13 '13 at 16:18
  • Concur with your analysis. Wanted to point out that the problem probably isn't related to closing, but rather to opening these connections. – Buck Calabro Dec 13 '13 at 16:25
  • When you made the switch to 64-bit is there any chance the underlying MSFT libraries have a different default value for the number of ODBC connections made for each thread? Perhaps in the 32-bit world a thread used (for example) 64 connections but in the 64-bit world a thread uses (again, just an example) 512 connections? Something like that would explain the much higher number of `QZDASOINIT` jobs. – Benny Hill Dec 13 '13 at 16:28
  • @BennyHill good suggestion -- thanks. The ODBC drivers come in 32 and 64-bit varieties, so any number of things could be different. – Joel Anair Dec 13 '13 at 16:35
  • Joel - I'd appreciate it if you let us know if you find the problem regarding the proliferation of `QZDASOINIT` jobs. We are currently dealing with a vendor whose product was hammering our IBM i pretty hard and one of the suggestions made was for them to limit the number of threads/ODBC connections. – Benny Hill Dec 16 '13 at 14:47
  • 2
    Just a note for @BuckCalabro and anyone else that comes across this question - the `QZDASOINIT` jobs will be destroyed after 200 uses by default and of course ending the subsystem or performing an IPL. `QZDASOINIT` jobs that get started in `QSERVER` or `QSYSWRK` (this can happen if `QUSRWRK` subsystem isn't started) will likely live a very long time because it takes a long time for them to get used 200 times. – Benny Hill Dec 18 '13 at 16:08
  • 2
    The number of reuses is configurable and is determined by the value specified for the maximum number of uses (MAXUSE) value of the ADDPJE or CHGPJE CL commands. – Buck Calabro Dec 18 '13 at 16:13
  • @BennyHill we're still working on this with IBM support and haven't had any luck yet. I'll let you know if we do! – Joel Anair Jan 07 '14 at 19:15
  • @BuckCalabro that's the first time I've heard about MAXUSE. Thanks for the info! – Joel Anair Jan 07 '14 at 19:17
  • 1
    Can you have one of the iSeries folks run the following command and report back to us what is seen? `DSPACTPJ SBS(QUSRWRK) PGM(QZDASOINIT)` Also, you can limit the number of `QZDASOINIT` jobs by issuing the command `CHGPJE SBSD(QUSRWRK) PGM(QZDASOINIT) MAXJOBS(9999)` Change the 9999 to whatever is tolerable 1-9999. By default the value is `*NOMAX`. This would be a hack - it doesn't fix the real problem but it might make things "work". – Benny Hill Jan 08 '14 at 05:07
  • Also, here's some [documentation for prestart jobs](http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzaii%2Frzaiiodbcadm.htm) like `QZDASOINIT`. – Benny Hill Jan 08 '14 at 05:08
  • 1
    The interesting thing to me is that connections are not getting dropped when IISRESETing the server. Can you install TCPTrace for SysInternals on the 2008 Server, run it and check the connections that are there? They should be from the IIS Process to whatever endpoint (the remote iSeries IP address I'd guess). Then do an IISReset and see what happens to them? This won't definitively tell you what the problem is but might help isolate it. – simon at rcl Jan 09 '14 at 16:18
  • @BennyHill very interesting. We're passing along your request to the iSeries guys. Also, thanks for the docs. You've been a big help. – Joel Anair Jan 09 '14 at 19:51
  • @simonatrcl interesting suggestion. I'm going to look into that. Thanks. – Joel Anair Jan 09 '14 at 19:53
  • I notice in your connection string `MaximumPoolSize=-1` which I'm betting means there is no maximum. Any chance there is a setting somewhere that tells your Windows application to NOT reuse a connection? Or perhaps you've got some code that creates it's own connection rather than getting one from the pool? How many 2008 servers are in your server farm? Have you tried changing the `MaximumPoolSize` value to 50 (for instance)? That might be another "hack" to get things to work, find the highest value the iSeries can tolerate and limit your Windows application... – Benny Hill Jan 09 '14 at 22:39
  • Did you guys ever make any progress on this problem Joel? – Benny Hill Jan 24 '14 at 19:44

3 Answers3

6

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.

Benny Hill
  • 6,191
  • 4
  • 39
  • 59
1

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

Charles
  • 21,637
  • 1
  • 20
  • 44
0

Turning off connection pooling might solve your problem. In the connection string include: Pooling=false;

Jeroen Kransen
  • 1,379
  • 3
  • 19
  • 45
Jamir Déa Júnior
  • 1