Temporary tables owners

intarsplienis · #1 (**permalink**) 08-21-06, 14:34

Hello!

Does anybody can submit SQL script, which outputs relation between active user id and its used temporary table names?

"onstat -g ses" output alternative is not appropriate for situation.

One of my scripts:

select systabnames.dbsname,systabnames.tabname,f.txt[1,4] flags,count(*)
locks, syssessions.sid, syssessions.username,
syssessions.hostname,syssqlstat.sqs_sqlerror,
syssqlstat.sqs_isamerror,syssqlstat.sqs_statement from
syslocktab,systabnames, syssessions,sysuserthreads,flags_text f,outer
(syssqlstat) where syslocktab.lk_partnum = systabnames.partnum and
syslocktab.lk_owner = sysuserthreads.us_txp and sysuserthreads.us_sid =
syssessions.sid and systabnames.dbsname!="sysmaster" and
syssessions.sid=syssqlstat.sqs_sessionid and syslocktab.lk_flags=f.flags
and f.tabname = 'syslcktab' group by 1,2,3,5,6,7,8,9,10 order by
systabnames.dbsname, 4 desc,systabnames.tabname

output:

shows associations with user ids and normal tables, but temporary tables ar shown very strange!!!???

Thanks

Tyveleyn · #2 (**permalink**) 08-21-06, 15:07

Code:

onstat -g sql sessionid

displays the last parsed SQL statement for the session. And sessionid's can be retrieved with:

Code:

onstat -g ses

So if you would put something together like:

Code:

for i in `onstat -g ses | \
awk '$1 !~ /[^0-9]/ && $2 != "informix"{print $1}'`; do
    onstat -g sql $i
done

you'll see in the output a remark like this concerning temp tables:

Code:

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
11392 -              pharmabase         CR  Not Wait   0    0    9.03
                                                                     
Last parsed SQL statement :                                          
  select schema_id from inl_schema into temp schema                  
                                                                     
User-created Temp tables :                                           
  partnum  tabname            rowsize                                
  100056   schema             4

Regards

intarsplienis · #3 (**permalink**) 08-21-06, 15:25

There must be possibility to get the info from SMI meta data interface! I try to believe, that onstat -g ses gets info from sysmaster system tables probably...

Internet Services

Web Development

Digital Marketing

Consumer Tech