Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17

    Unanswered: Temporary tables owners

    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: Click image for larger version. 

Name:	output.JPG 
Views:	426 
Size:	130.9 KB 
ID:	6291

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

    Thanks
    Last edited by intarsplienis; 08-21-06 at 16:19.

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  3. #3
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •