Temp Table association with sessionid

we have an canned application that has suddenly decided to fill temp space :-( Currently I'm unsure if is natural growth or a lovely user who is using a 'fill the form' search to select everything from everything... (The perfect database has no users... ;-) I have sql to find the largest temp table (in fact all temp tables) but would like to associate a temp table with a user (=sessionid) and hence their sql... That way I can tune the sql ( or do the post-sql on the user ;-) ) There is an association between temp table and sessionid - because the database drops the table on session termination... so where do I find it? IDS 10 currently Can you help? (I'm sure Lester is going to embarrass me with a 'Not again - posted on the 21 march 199x' Sorry Lester - searching iiug didn't find it) Robetr

Robert, Try: SELECT t.tabname ,t.dbsname,t.owner, DECODE(d.is_logging,1,"Y","N") AS db_with_log, s.name AS dbspace, DBINFO("UTC_TO_DATETIME",ti_created) AS created, DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS table_using_log,ti_npused AS num_usedpages, ti_nptotal AS num_pages FROM sysmaster:systabnames t, sysmaster:systabinfo i, sysmaster:sysdbspaces s, sysmaster:sysdatabases d WHERE t.partnum=ti_partnum AND d.name=t.dbsname AND s.dbsnum=TRUNC(t.partnum/1048576) AND hex(mod(ti_flags,256)/16) IN ( 6,2 ) Best regards, Javier Gómez ids-bounces@iiug.org wrote on 24/02/2009 11:06:23: > we have an canned application that has suddenly decided to fill tempspace :-( > > Currently I'm unsure if is natural growth or a lovely user who is using a > 'fill the form' search to select everything from everything... (The perfect > database has no users... ;-) > > I have sql to find the largest temp table (in fact all temp tables) but would > like to associate a temp table with a user (=sessionid) and hence > their sql... > That way I can tune the sql ( or do the post-sql on the user ;-) ) > > There is an association between temp table and sessionid - because the > database drops the table on session termination... so where do I find it? > > IDS 10 currently > > Can you help? > > (I'm sure Lester is going to embarrass me with a 'Not again - postedon the 21 > march 199x' Sorry Lester - searching iiug didn't find it) > > Robetr > > > ********************************************************************** ********* > Forum Note: Use "Reply" to post a response in the discussion forum. > Salvo indicado de otro modo más arriba / Unless stated otherwise above: International Business Machines, S.A. Santa Hortensia, 26-28, 28002 Madrid Registro Mercantil de Madrid; Folio 1; Tomo 1525; Hoja M-28146 CIF A28-010791

I don't quite understand... Lets say I log in to the database twice... as 'fred'. I create a temp table via a sort / join etc in each session... how do i associate which 'fred' and hence query the syssqlcurses for the sql statement that is associated with the temp tables? I think I must be misunderstanding something here.. Can you clarify