Temp Space Not Getting Freed

IDS Gurus, I am seeing some unusual behavior in several instances on a single server. We are running IDS 7.31.UD7 on AIX 5.2. This is a test server which our application testing group uses. The application in question runs a process that reads from the database and builds some sort of cache on the application server. Some of the reads are against views with several outer joins. These reads cause the process to use space in the temp dbspace. As the process proceeds, we can see the amount of free space fall in the temp dbspace - which should be normal. However, when the process completes, the temp space is not freed - even when there are no user sessions in the instance. I always assumed that temp space used by a session was freed at the end of a transaction - at the very least when the session disconnects. All databases involved have unbuffered logging. The application was experiencing incomplete caches on the application server because the temp space would be completely consumed. We worked around the incomplete cache issue by adding space to the temp dbspace. This provided enough space for the cache to complete, but the problem of not freeing the temp space remains. I tried to force a checkpoint, perform a fake Level 0 archive, move to the next logical log, and several combinations of these things. Nothing freed up the space unless I bounced the instance. Obviously, this is not a solution for the application. There are no messages in the online.log file, other than the normal checkpoints. I should also mention that this instance is involved in Enterprise Replication; however, the read operations only go against one instance and it is the "secondary", or receiving side, of all the replicates. ER should not come into play with this, but I thought I'd best mention it. One more thing to mention: This server has eight instances. There are four pairs of instances, with each pair comprising a testing environment. Each pair has a "master" or "primary" instance, and a "secondary" or "regional" instance with four regional databases. The masters are named ma1, ma2, ma3, and ma4. The regionals are named reg1, reg2, reg3, and reg4. These read operations are performed only on the regional databases. The application folks first noticed incomplete caches when reading from reg2. The databases in reg2 had recently been refreshed from production so they initially thought it was a bad refresh. However, the data in the regional databases proved to be complete and accurate. We then noticed that other testing environments, which had not been refreshed for some time, were also exhibiting this same behavior. This exonerates the very recent growth in data volume we have seen. Whatever is affecting testing environment 2 now affects all four environments. This leads me to believe it might be a server level issue. Has anyone seen this sort of behavior? Thanks in advance for any help and advice. Rob Schmitz Embarq Data Management rob.b.schmitz@embarq.com

Not sure I'm an IDS guru, but here are some thoughts on your issue. Explicit temp tables are freed when (a) the temp table is explicitly dropped or (b) the database session is disconnected. Implicit temp tables are freed when (a) the query results are fully returned to the calling program or (b) the database session is disconnected. Have you reviewed oncheck -pe output for your temp dbspace? The only explanation I know for an Informix reboot clearing temp space is the instance stopage killing sessions. Are you certain all database sessions are disconnected before you reboot? Application server software which I'm accustomed to maintains persistent database connections even when no users are connected to the app server. Are your app servers maintaining persistent connections? Does rebooting the app servers have any impact on temp usage?

Thanks to all who replied regarding this issue. Here is an update. We opened a case with IBM. They do not have anything concrete and they suggest changing some onconfig parameters (NUMCPUVPS, NETTYPE, etc.). The sqlhosts file and the onconfig files have not changed in 2 years and this problem just started happening a few weeks ago - in our test environment. I was just told that the problem is now happening in production. As with our test environment, production configuration has not changed in 2 years. The production problem began happening toward the end of last week. This leads me to believe the application has begun doing something different. However, we must know the underlying cause before we can correct the situation. Unfortunately, this is a critical application in our company. I believe the problem lies with how implicitly created temp tables are released. I would imagine that some change in the way the application connects to (and more importantly, disconnects from) the instance, has caused some releasing mechanism to fail in the database engine. IBM provided a script which shows the temp tables in the temp dbspaces, along with their size and partnums. I have not been able to determine which session is associated these temp tables, but they are owned by informix. These temp tables exist in the temp dbspace even though there are no user sessions in the instance. Does anyone have any ideas or suggestions as to where we can focus our effort on solving this problem? Any help would be greatly appreciated. Thanks Rob Schmitz Embarq Data Management rob.b.schmitz@embarq.com www.embarq.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE GRIFFEN Sent: Tuesday, October 07, 2008 2:06 PM To: ids@iiug.org Subject: Re: Temp Space Not Getting Freed [13634] Not sure I'm an IDS guru, but here are some thoughts on your issue. Explicit temp tables are freed when (a) the temp table is explicitly dropped or (b) the database session is disconnected. Implicit temp tables are freed when (a) the query results are fully returned to the calling program or (b) the database session is disconnected. Have you reviewed oncheck -pe output for your temp dbspace? The only explanation I know for an Informix reboot clearing temp space is the instance stopage killing sessions. Are you certain all database sessions are disconnected before you reboot? Application server software which I'm accustomed to maintains persistent database connections even when no users are connected to the app server. Are your app servers maintaining persistent connections? Does rebooting the app servers have any impact on temp usage? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.