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

Hello Dan,

I have the script in Unix maybe serves to yuo, this is

{
dbaccess sysmaster 2>/dev/null << EOF
SELECT trim(n.dbsname) type,
trim(n.owner) users,
trim(n.tabname) table,
trim(dbinfo('DBSPACE', i.ti_partnum)) dbspace,
round(i.ti_nptotal*4,0) kb -- 4 for AIX, 2 for HP-UX
FROM systabnames n, systabinfo i
WHERE (bitval(i.ti_flags, 32) = 1
OR bitval(i.ti_flags, 64) = 1
OR bitval(i.ti_flags, 128) = 1)
AND i.ti_partnum = n.partnum
into temp ggtmp with no log;
SELECT type[1,12], users, table, dbspace[1,12], kb
from ggtmp
order by 1,2,3,4
EOF
} |more

Gustavo.

THANK YOU Gustavo. I tried the query and it works great! This will satisfy my most important requirement.

Do you know where I can find the documentation for the systabinfo table, expecially the bit flag values? I found the systabnames documented in Chapter 34 of the Administrator's Guide, but no documentation for the systabinfo table.

Dan

Hi, DAN

Congratulation !!!

The information to all tables of sysmaster this in $INFORMIXDIR/etc/sysmaster.sql.

a hug !!!

Gustavo.

Gustavo,

Thank you for your time and assistance. The information is very helpful.

Dan Dodge
Arizona, USA

Run this Informix SQL to see temporary tables:

SELECT partnum, dbsname, owner, tabname, dbinfo('UTC_TO_DATETIME',ti_created) created, ti_nrows
from sysmaster:systabnames s,sysmaster:systabinfo i
where i.ti_partnum = s.partnum
and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
and owner !="informix"
order by created asc