We use IDS 9.4 on AIX. We recently discovered that products such Crystal Reports and MS Access are not dropping tables created in tempdbs. This only came to light when a particularly large table was being created (300m rows) 1.9gig on a 2 gig tempdbs. This effectively filled up the tempdbs.

Can anyone provide details of how to identify the table owners? The database associated is not SORTTEMP but a recognisable database.

Also can anyone give me commands to clear these tables without having to restart IDS as we did?

Can I drop and recreate tempdbs without stopping IDS?

Please note that there were no session connected to the database and therefore the calling process orphaned the tables instead of dropping them.

http://www-1.ibm.com/support/docview...=utf-8&lang=en
How to find out the temporary tables that are currently created

And check your parameter DBSPACETEMP in your ONCONFIG... create more than on dbspace to Temp...

The temp tables must be dropped by application... if they don't do it ...when conection is closed the IDS automaticly droped and clean the temp tables...

Thanks for the link. This part answers what I want.

When I ran the query it gave me the following.

tabname dbsname owner db_with_log dbspace created table_using_log num_usedpages num_pages
---------- ---------- -------- -------------- ---------- ---------------- ------------------ ---------------- ------------
_temptable moisys informix Y tempdbs 23/07/2007 09:40 N 118646 118960

The process has been killed but has left the table behind. The only way we know how to remove this is to restart Informix.

what process? the application? or the user thread into informix??

check if the session still open ... if yes, kill them with onmode -z

maybe this can be a bug...

I'm killing the session using AGS Server Studio. The session was started by a Crystal Report that obviously decided that it needed a temporary sort file. This file filled our tempdbs and hence caused problems in IDS. I then terminated the session but it did not clear up the file in tempdbs.

I have logged this as a bug with IBM but was hoping for help from any quarter in the meantime.

Your query has been a great help as it will identify users who are leaving tables behind. We will change the report login ID's so we can track failed temp table clear ups.

My suggestion... for now... is create another temp dbspace and set in DBSPACETEMP in ONCONFIG... OR create this temp dbspace and change the enviroment from Crystal Report , setting the DBSPACETEMP variable to this dbspace, in this way only the Crystal will be use this temp dbspace and not affect another users...


Sorry my poor english...