Re: SQL to find out temp tables created by a session

The Informix (at least for SE 4.x) temp tables are created with a few letters (which seem to be date/time dependent) followed by the process id of the sqlexec process which created it. There will be a .dat and .idx pair of files for each table. So, temp tables for process 123456 will be something like staaa123456.dat and staaa123456.idx. ps -p shows processes by proc id, so to find the owner of temp file staaa00123 use ps -p 123. The following script (not elegant, but it works and should be simple to follow!) will list all processes running on terminals running sqlexec which have created a temp file. If your Informix temp files are created in a directory other than /tmp then change the two occurrences of "/tmp" to the appropriate directory. Note that the "`" characters are required (you cannot use "'"). for a in `ps -ef | grep sqlexec | grep -v grep | tr -s " " | cut -d" " -f 3` do echo echo Process List z=`ps -fp $a | grep -v UID | tr -s " " | cut -d" " -f 7` ps -ft $z l /tmp/*${a}.dat l /tmp/*${a}.idx done Steve sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote in article <67p6t9$pk9@cssun.mathcs.emory.edu>... > > Does anybody know of an SQL script that would give me the names, size etc of all > the temp tables that a session generates ?? One of our processess is really > eating up temp space and I need to weed it out. > > Thanks > > Sujata > > **************************************************************************** **** > ***** > Sujata Soman > > > E-Mail: ssoman@omm.com > > Database Administrator ( Informix ) > > O'Melveny & Myers, Los Angeles > > **************************************************************************** **** > ****** > > > > > > > > > >