Relating temp tables with its sessions 
Author Message
 Relating temp tables with its sessions

<html><div style='background-color:'><DIV></DIV>
<DIV></DIV>
<P>Hi everyone! </P>
<P>?I hope somebody can help me on this. </P>
<P>?I am working on IDS 9.30.FC2 and a need an onstat command or sysmaster query that allows me to know which sessions created which temp tables on the server. </P>
<P>Thanks very much in advance, </P>
<P>Ftima Caleya. <BR><BR><BR></P>
<DIV></DIV>
<DIV>-- Fatima Caleya </DIV>
<DIV></DIV>
<DIV>-- IBM Consultant</DIV>
<DIV></DIV>

<DIV></DIV>
<DIV></DIV></div><br clear=all><hr>Express yourself with cool emoticons.  <a href=" http://www.***.com/ ;>Get MSN Messenger today.</a> </html>



Sat, 16 Jul 2005 20:31:16 GMT
 Relating temp tables with its sessions

Hi Fatima,

onstat -g mem shows session memory usage; when a session needs extra memory
for sorting, system backup, and etc, this shows in the report as
action_SID, and sometime as SID_action

I recreated a small script to group Informix memory usage, and you can feel
free to use it if you like, but keep in mind that there is no warranties,
guarantees, and etc included with it's use.  If you like it, please share
it and enhance it and have fun!

I call it dbmem.db,
------------------------------- snip
-----------------------------------------
#!/usr/bin/ksh
onstat -g mem | sort -fb -k 2,2 -k 1,1 | awk '
BEGIN {
printf("\nshm  location    kb-total  kb-free  kb-used\n");
printf("---  --------    --------  -------  -------\n");

Quote:
}

{
  if(NF==7 && $1!="name")
  {
    if($2=="V" && $1~"^[0-9]*$")
    {
      totalS+=$4; freeS+=$5;
    }
    else
    {
      printf(" %1s   %-12s %7d  %7d  %7d\n", $2, $1, $4/1024, $5/1024,
($4-$5)/1024);
      total+=$4; free+=$5;
    }
  }

Quote:
}

END {
      printf(" V   SESSIONS     %7d  %7d  %7d\n", totalS/1024, freeS/1024,
(totalS-freeS)/1024);
      total+=totalS; free+=freeS;
      printf("                  -------  -------  -------\n");
      printf("                  %7d  %7d  %7d\n\n", total/1024, free/1024,
(total-free)/1024);
Quote:
}'

------------------------------- snip
-----------------------------------------

Produces a report like the following:

dbmem.db

shm  location    kb-total  kb-free  kb-used
---  --------    --------  -------  -------
 M   shmcon           648       15      632
 R   res-buff     2800008       13  2799994
 R   resident      436136        9   436126
 V   aio            12000     1135    10864
 V   arc_1020       52960     2319    50640
 V   aslogflush        16        1       14
 V   btclean           16        0       15
 V   condition       2264      703     1560
 V   dictpool       32664      339    32324
 V   global          9680      191     9488
 V   gls               96        3       92
 V   lgflushpool        8        7        0
 V   main_loop()     1960     1348      611
 V   mt              5960      297     5662
 V   mutex           1976       60     1915
 V   onmode_mon        16        1       14
 V   ovrfl-buff        24       13       10
 V   procpool         896      415      480
 V   rsam           57560     4876    52683
 V   XTF_mem          512       16      495
 V   SESSIONS     2759168   340414  2418753
                  -------  -------  -------
                  6174568   352188  5822379

From the output, you can gather which session is performing sorting and the
like, because it will start with SID_action or action_SID.  From there you
can run 'onstat -g ses SID' to obtain the TID's.  Afterwards, you may use
the TID's in association with 'onstat -g opn TID' to find which partition
numbers are opened to the thread.  The partitions (6th column) can be
related to a specific tables by running a simple query as follows:

echo "select tabname from systabnames where partnum = '0x00100067'" |
dbaccess sysmaster

Of course, if you are running a backup, you can obtail the partition number
from the 6th column of the 'onstat -g arc' command.

Good luck,
-Tim

                    "Fatima Caleya"                                                                            

                    om>                      cc:                                                              
                    Sent by:                 Subject:     Relating temp tables with its sessions              
                    owner-informix-lis                                                                        

                    01/28/2003 07:31                                                                          
                    AM                                                                                        

Hi everyone!

 I hope somebody can help me on this.

 I am working on IDS 9.30.FC2 and a need an onstat command or sysmaster
query that allows me to know which sessions created which temp tables on
the server.

Thanks very much in advance,

Ftima Caleya.

-- Fatima Caleya
-- IBM Consultant

Express yourself with cool emoticons. Get MSN Messenger today.



Sat, 16 Jul 2005 22:41:58 GMT
 Relating temp tables with its sessions

There is a script in the IIUG website (find_temp_tables.ksh) doing exactly
you need ...

If you cannot find it, let me know, I'll forward you the script.

Thanx much,

Rajib Sarkar
Advisory Support Engineer (Wells Fargo Bank)
IBM Data Management Group
Ph :  (602)-217-2100
Fax:  (602)-217-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi

                      "Fatima Caleya"                                                                                                  

                      com>                      cc:                                                                                    
                      Sent by:                  Subject: Relating temp tables with its sessions                                        
                      owner-informix-li                                                                                                

                      01/28/2003 05:31                                                                                                
                      AM                                                                                                              

Hi everyone!

 I hope somebody can help me on this.

 I am working on IDS 9.30.FC2 and a need an onstat command or sysmaster
query that allows me to know which sessions created which temp tables on
the server.

Thanks very much in advance,

Ftima Caleya.

-- Fatima Caleya
-- IBM Consultant

Express yourself with cool emoticons. Get MSN Messenger today.



Sat, 16 Jul 2005 23:08:06 GMT
 Relating temp tables with its sessions

Quote:

><html><div style='background-color:'><DIV></DIV>
><DIV></DIV>
><P>Hi everyone! </P>
><P>&nbsp;I hope somebody can help me on this. </P>
><P>&nbsp;I am working on IDS 9.30.FC2 and a need an onstat command or sysmaster query that allows me to know which sessions created which temp tables on the server. </P>
><P>Thanks very much in advance, </P>
><P>Ftima Caleya. <BR><BR><BR></P>
><DIV></DIV>
><DIV>-- Fatima Caleya </DIV>
><DIV></DIV>
><DIV>-- IBM Consultant</DIV>
><DIV></DIV>

><DIV></DIV>
><DIV></DIV></div><br clear=all><hr>Express yourself with cool emoticons.  <a href="http://g.msn.com/8HMUENUK/2746">Get MSN Messenger today.</a> </html>

That is how I see your message. :-(((((((

        onstat -g sql session_id

will show user created temp tables but I can't find relation in sysmaster
between sessions and tables (partitions).

Nebojsa
------------------------------------
Remove spam block (DELETE) to reply



Sun, 17 Jul 2005 00:33:16 GMT
 Relating temp tables with its sessions

Quote:

>There is a script in the IIUG website (find_temp_tables.ksh) doing exactly
>you need ...

>If you cannot find it, let me know, I'll forward you the script.

Where are those "onstat -g dmp" structures described?

Nebojsa
------------------------------------
Remove spam block (DELETE) to reply



Sun, 17 Jul 2005 01:27:29 GMT
 Relating temp tables with its sessions

Quote:

>There is a script in the IIUG website (find_temp_tables.ksh) doing exactly
>you need ...

>If you cannot find it, let me know, I'll forward you the script.

There is small bug in the script. When you run it with -b[ief] you get :
        "no closing quote"

because the line # 303:
    print_stmt=`echo "DBSpace Information:`
should be:
    print_stmt=`echo "DBSpace Information:"`

I run it on RedHat Kernel 2.4.7-10  with Informix 9.30UC2.
Where are those "onstat -g dmp" structures you are using in your script
described?

Nebojsa

------------------------------------
Remove spam block (DELETE) to reply



Sun, 17 Jul 2005 19:51:34 GMT
 Relating temp tables with its sessions


Quote:

> There is a script in the IIUG website (find_temp_tables.ksh) doing exactly
> you need ...

> If you cannot find it, let me know, I'll forward you the script.

ftp://ftp.iiug.org/pub/informix/pub/find_tmp_tbls.zip

PS I used to work with Fatima so be nice to her!



Mon, 18 Jul 2005 07:02:41 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Relating temp tables with its sessions

2. temp-table-related failure in regression tests

3. drop all temp tables in QA session

4. Problem with Temp Tables timing out in Access session

5. Temp table (#) for a session?

6. SQL to list Temp tables for specific Session Id

7. Finding Temp Table For Specific Session Id

8. session id of temp table

9. Temp Table not clear after the informix session has terminated

10. SQL to find out temp tables created by a session

11. Session Temp table unpleasantness

12. Temp table vs Global Temp table


 
Powered by phpBB® Forum Software