IDS Forum

RE: Temp Space Not Getting Freed

Posted By: Schmitz, Rob B [EQ]
Date: Monday, 13 October 2008, at 5:50 p.m.

In Response To: Re: Temp Space Not Getting Freed (DAVE GRIFFEN)

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.

Messages In This Thread

• Temp Space Not Getting Freed
  Schmitz, Rob B [EQ] -- Monday, 6 October 2008, at 10:57 a.m.
  • Re: Temp Space Not Getting Freed
    DAVE GRIFFEN -- Tuesday, 7 October 2008, at 3:05 p.m.
    • RE: Temp Space Not Getting Freed
      Schmitz, Rob B [EQ] -- Monday, 13 October 2008, at 5:50 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.