Dynamic identification of Informix temp tables

I would like to develop a process which can determine if there were temp tables created during my current database connection. I am the system architect for a very large application which establishes a database connection at user startup, and that connection persists while the user navigates from screen to screen. We have a problem with too many concurrent database connections and would like to reduce the number of active connections by disconnecting after a fixed period of inactivity. However, there are processes in this application which retain data in temp tables for use within the same connection by other screens or processes. If I can dynamically determine what temp tables exist, and what the column names and datatypes are, we can develop a process which would write these out to the file system and disconnect from the database. At a later point (on request by the user) the system would reestablish a database connection and use the file system to recreate the temp tables. My problem is determining what temp tables exist and the specific structure of those tables. We operate in a multi IBM AIX RS/6000 environment running Informix 7.31.UD5. Any ideas? Dan Dodge -- Posted via http://dbforums.com

ddodgeaz wrote: > If I can dynamically determine what temp tables exist, and what the > column names and datatypes are, we can develop a process which would > write these out to the file system and disconnect from the database. At > a later point (on request by the user) the system would reestablish a > database connection and use the file system to recreate the temp tables. > My problem is determining what temp tables exist and the specific > structure of those tables. > Any ideas? What is the problem of having a lot of connections? License use? Why should a connection maintain temporary tables between operations that can be left to sleep for so long? You can find a way to know what temp tables were created. And probably the column types. But it is not easy and it can change with a version change. You can of course log the creation of temp tables in an unique well knonw temp table per session. But I would question every bit of your application logic before trying to solve this in technical terms. The temp tables can be found and identified by querying sysmaster database. But the sysmaster is the less documented peace of DML I know ;) And this is for a very good reason: It's an internal "database". One should not use it lightly. Regards.

Thanks for your reply Fernando. Yes, the issue is the license. We have hundreds of users, and the application has over a hundred screens. It is not feasible to redesign the application at this point. We could manage the temp tables in any number of ways, one of which is the "well known table" that you refer to. We had considered that, and may take that approach if we need to. However, that will require an ongoing maintenance effort. With a half dozen developers maintaining and enhancing this system all the time, it seemed to me far simpler to handle the issue generically by developing a "temp table handler" that executes when a user reached the pre-determined timeout. If, at the very least, there were a simple way just of determining the current temp table NAMEs, we could cleanly implement a subset of the desired result. A "sledge hammer" approach to get the temp table names is to have the process execute a shell script that executes an onstat command (onstat -g ses <nnn>) which lists the temp tables, and parse the response. It seemed to me that if onstat can get the info, we should be able to write a function that would accomplish the same result in a cleaner method - and if we could get the structure of the temp table at the same time, even better. Dan Dodge -- Posted via http://dbforums.com