Re: Dynamic identification of Informix temp tables

ddodgeaz wrote: > 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. > It may be possible, but I enforce my previous opinion: don't do it! If you like to hurt yourself: I've been looking at sysmaster... Specially at 9.40... you can begin by: select * from sysmaster:sysptnhdr where sysmaster:bitval(flags, 32) = 1; - This should give you partnum for temp tables (all from all users) - You could link this to the "owner" at systabnames (very bad idea if a user has more than one session) xpg4_is.sql at $INFORMIXDIR/etc (in 9.40) has a mapping between coltype and data types. Then you can have colzise. This can be seen at sysmaster:sysptncol (join by partnum). Be carefully because if I remeber well a table can have several partnum's. This can be caused by temp tables fragmentation. I'm not sure, but it may depend on your DBSPACETEMP environment variable. Now: 1- This type of queries can be very slow 2- Sometimes they seem to give erroneous results due to the kind of pseudo-tables and constant change they suffer. 3- these can differ from version to version Do you need more reasons not to do it? I think a lot of people here can think about several others. DON'T DO IT! Besides, moving data from temp tables into filesystem and back don't seem to me a very good idea. I think that if you have and maintain the source code you should take another path.... but it's your decision... Regards.