threads still running?

This involves ids9.4.tc2 on windows 2000 server. I have been trying to find out why so many connections are open on a server that has at most 20 users on it and the onstat -u may show something like: 2053 active, 2688 total, 2648 maximum concurrent. If I do "onstat -g ses" and look at sessions with alot of threads and then choose one and execute "onstat -g ses 643", I get this : IBM Informix Dynamic Server Version 9.40.TC2 -- On-Line -- Up 08:06:15 -- 971968 Kbytessession #RSAM total used dynamic id user tty pid hostname threads memory memory explain 643 informix BOB 4712 bob.chee 21 671744 667488 off tid name rstcb flags curstk status1289 sqlexec 2a99d0f4 Y--P--- 740 cond wait(netnorm)2713 join_1.0 30fda2b8 Y------ 552 cond wait(await_MC1)2714 join_1.1 30fd847c Y------ 552 cond wait(await_MC1)2715 join_2.0 30fd9cac Y------ 552 cond wait(await_MC2)2716 join_2.1 30fd96a0 Y------ 552 cond wait(await_MC2)2717 scan_3.0 30fd6034 Y------ 552 cond wait(await_MC3)2718 join_1.0 30fda8c4 Y------ 552 cond wait(await_MC1)2719 join_1.1 30fdaed0 Y------ 552 cond wait(await_MC1)2720 join_2.0 30fdb4dc Y------ 552 cond wait(await_MC2)2721 join_2.1 30fdbae8 Y------ 552 cond wait(await_MC2)2722 scan_3.0 30fdc0f4 Y------ 552 cond wait(await_MC3)2723 join_1.0 30fdc700 Y------ 552 cond wait(await_MC1)2724 join_1.1 30fdcd0c Y------ 552 cond wait(await_MC1)2725 join_2.0 30fdd318 Y------ 552 cond wait(await_MC2)2726 join_2.1 30fdd924 Y------ 552 cond wait(await_MC2)2727 scan_3.0 30fddf30 Y------ 552 cond wait(await_MC3)2728 join_1.0 30fde53c Y------ 552 cond wait(await_MC1)2729 join_1.1 30fdeb48 Y------ 552 cond wait(await_MC1)2730 join_2.0 30fdf154 Y------ 552 cond wait(await_MC2)2731 join_2.1 30fdf760 Y------ 552 cond wait(await_MC2)2732 scan_3.0 30fdfd6c Y------ 552 cond wait(await_MC3) Memory pools count 1name class addr totalsize freesize #allocfrag #freefrag 643 V 2d2a8020 671744 4256 2209 12 name free used name free used overhead 0 1648 mtmisc 0 800 resident 0 160 scb 0 360 opentable 0 64128 filetable 0 12704 ru 0 224 log 0 88368 temprec 0 121344 keys 0 2248 ralloc 0 153888 gentcb 0 13152 ostcb 0 2536 sort 0 56 sqscb 0 63184 sql 0 40 rdahead 0 1808 xchg_desc 0 13648 xchg_port 0 9864 xchg_packet 0 8504 xchg_group 0 880 xchg_priv 0 1704 hashfiletab 0 5904 osenv 0 784 sqtcb 0 52824 fragman 0 40736 shmblklist 0 4248 udr 0 1584 sqscb infoscb sqscb optofc pdqpriority sqlstats optcompind directives2cb9c0b8 2bb4f018 0 0 0 0 1 Sess SQL Current Iso Lock SQL ISAM F.E.Id Stmt type Database Lvl Mode ERR ERR Vers Explain 643 EXEC PROCEDURE cheer CR Wait 10 0 0 9.03 Off Current statement name : sql_cur111765 Current SQL statement : execute function PriceBrk(159983,2) Last parsed SQL statement : execute function PriceBrk(159983,2) This proc does not run but a heartbeat. What do all the "cond wait..." messages mean? Are those threads still alive? Or does onstat -t ses nnn report things that have terminated (like -g sql). The function is called from a c++ COM dll, so perhaps the object is not closing the connection? But it seems like the threads would die when the function returned the last row. Not so?

On Aug 20, 7:04 pm, Bill Hamilton <garage_...@hotmail.com> wrote: > This involves ids9.4.tc2 on windows 2000 server. > > I have been trying to find out why so many connections are open on a server that has at most 20 users on it > and the onstat -u may show something like: > 2053 active, 2688 total, 2648 maximum concurrent. > > If I do "onstat -g ses" and look at sessions with alot of threads and then choose one and > execute "onstat -g ses 643", I get this : > IBM Informix Dynamic Server Version 9.40.TC2 -- On-Line -- Up 08:06:15 -- 971968 Kbytes> session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 643 informix BOB 4712 bob.chee 21 671744 667488 off > tid name rstcb flags curstk status1289 sqlexec 2a99d0f4 Y--P--- 740 cond wait(netnorm)2713 join_1.0 30fda2b8 Y------ 552 cond wait(await_MC1)2714 join_1.1 30fd847c Y------ 552 cond wait(await_MC1)2715 join_2.0 30fd9cac Y------ 552 cond wait(await_MC2)2716 join_2.1 30fd96a0 Y------ 552 cond wait(await_MC2)2717 scan_3.0 30fd6034 Y------ 552 cond wait(await_MC3)2718 join_1.0 30fda8c4 Y------ 552 cond wait(await_MC1)2719 join_1.1 30fdaed0 Y------ 552 cond wait(await_MC1)2720 join_2.0 30fdb4dc Y------ 552 cond wait(await_MC2)2721 join_2.1 30fdbae8 Y------ 552 cond wait(await_MC2)2722 scan_3.0 30fdc0f4 Y------ 552 cond wait(await_MC3)2723 join_1.0 30fdc700 Y------ 552 cond wait(await_MC1)2724 join_1.1 30fdcd0c Y------ 552 cond wait(await_MC1)2725 join_2.0 30fdd318 Y------ 552 cond wait(await_MC2)2726 join_2.1 30fdd924 Y------ 552 cond wait(await_MC2)2727 scan_3.0 30fddf30 Y------ 552 cond wait(await_MC3)2728 join_1.0 30fde53c Y------ 552 cond wait(await_MC1)2729 join_1.1 30fdeb48 Y------ 552 cond wait(await_MC1)2730 join_2.0 30fdf154 Y------ 552 cond wait(await_MC2)2731 join_2.1 30fdf760 Y------ 552 cond wait(await_MC2)2732 scan_3.0 30fdfd6c Y------ 552 cond wait(await_MC3) > Memory pools count 1name class addr totalsize freesize #allocfrag #freefrag 643 V 2d2a8020 671744 4256 2209 12 > name free used name free used overhead 0 1648 mtmisc 0 800 resident 0 160 scb 0 360 opentable 0 64128 filetable 0 12704 ru 0 224 log 0 88368 temprec 0 121344 keys 0 2248 ralloc 0 153888 gentcb 0 13152 ostcb 0 2536 sort 0 56 sqscb 0 63184 sql 0 40 rdahead 0 1808 xchg_desc 0 13648 xchg_port 0 9864 xchg_packet 0 8504 xchg_group 0 880 xchg_priv 0 1704 hashfiletab 0 5904 osenv 0 784 sqtcb 0 52824 fragman 0 40736 shmblklist 0 4248 udr 0 1584 > sqscb infoscb sqscb optofc pdqpriority sqlstats optcompind directives2cb9c0b8 2bb4f018 0 0 0 0 1 > Sess SQL Current Iso Lock SQL ISAM F.E.Id Stmt type Database Lvl Mode ERR ERR Vers Explain 643 EXEC PROCEDURE cheer CR Wait 10 0 0 9.03 Off > Current statement name : sql_cur111765 > Current SQL statement : execute function PriceBrk(159983,2) > Last parsed SQL statement : execute function PriceBrk(159983,2) > > This proc does not run but a heartbeat. What do all the "cond wait..." messages mean? > Are those threads still alive? Or does onstat -t ses nnn report things that have terminated (like -g sql). > > The function is called from a c++ COM dll, so perhaps the object is not closing the connection? > But it seems like the threads would die when the function returned the last row. Not so? cond wait means that the threads are still alive but are not currently running.

> tid      name     rstcb    flags    curstk   status1289     sqlexec  2a99d0f4 Y--P---  740      cond wait(netnorm)2713     join_1.0 30fda2b8 Y------  552      cond wait(await_MC1)2714     join_1.1 30fd847c Y------  552      cond wait(await_MC1)2715     join_2.0 30fd9cac Y------  552      cond wait(await_MC2)2716     join_2.1 30fd96a0 Y------  552      cond wait(await_MC2)2717     scan_3.0 30fd6034 Y------  552      cond wait(await_MC3)2718     join_1.0 30fda8c4 Y------  552      cond wait(await_MC1)2719     join_1.1 30fdaed0 Y------  552      cond wait(await_MC1)2720     join_2.0 30fdb4dc Y------  552      cond wait(await_MC2)2721     join_2.1 30fdbae8 Y------  552      cond wait(await_MC2)2722     scan_3.0 30fdc0f4 Y------  552      cond wait(await_MC3)2723     join_1.0 30fdc700 Y------  552      cond wait(await_MC1)2724     join_1.1 30fdcd0c Y------  552      cond wait(await_MC1)2725     join_2.0 30fdd318 Y------  552      cond wait(await_MC2)2726     join_2.1 30fdd924 Y------  552      cond wait(await_MC2)2727     scan_3.0 30fddf30 Y------  552      cond wait(await_MC3)2728     join_1.0 30fde53c Y------  552      cond wait(await_MC1)2729     join_1.1 30fdeb48 Y------  552      cond wait(await_MC1)2730     join_2.0 30fdf154 Y------  552      cond wait(await_MC2)2731     join_2.1 30fdf760 Y------  552      cond wait(await_MC2)2732     scan_3.0 30fdfd6c Y------  552      cond wait(await_MC3) >scb      sqscb    optofc   pdqpriority sqlstats optcompind  directives2cb9c0b8 2bb4f018 0        0           0        0           1 > This proc does not run but a heartbeat. What do all the "cond wait..." messages mean? > Are those threads still alive? Or does onstat -t ses nnn  report things that have terminated (like -g sql). > > The function is called from a c++ COM dll, so perhaps the object is not closing the connection? > But it seems like the threads would die when the function returned the last row. Not so? Hi Bill, this probably has to do something with PDQPRIORITY, although the onstat -g ses says it's running at PDQPRIORITY=0.It may be the case you started the engine in the environment where PDQPRIORITY env. var. was exported as greater than 0. Also, you may try to update stats for all your procedures making sure PDQ is indeed set to zero. HTH Davorin

Unused threads left around are usually a symptom of having run a stored procedure that was compiled under a non-zero PDQPRIORITY setting. All SPL routines should be compiled with PDQPRIORITY zero except for specific routines that are known to require PDQPRIORITY set. Art On Thu, Aug 21, 2008 at 10:00 AM, Davorin Kremenjas < davorin.kremenjas@gmail.com> wrote: > > tid name rstcb flags curstk status1289 sqlexec > 2a99d0f4 Y--P--- 740 cond wait(netnorm)2713 join_1.0 30fda2b8 > Y------ 552 cond wait(await_MC1)2714 join_1.1 30fd847c Y------ > 552 cond wait(await_MC1)2715 join_2.0 30fd9cac Y------ 552 > cond wait(await_MC2)2716 join_2.1 30fd96a0 Y------ 552 cond > wait(await_MC2)2717 scan_3.0 30fd6034 Y------ 552 cond > wait(await_MC3)2718 join_1.0 30fda8c4 Y------ 552 cond > wait(await_MC1)2719 join_1.1 30fdaed0 Y------ 552 cond > wait(await_MC1)2720 join_2.0 30fdb4dc Y------ 552 cond > wait(await_MC2)2721 join_2.1 30fdbae8 Y------ 552 cond > wait(await_MC2)2722 scan_3.0 30fdc0f4 Y------ 552 cond > wait(await_MC3)2723 join_1.0 30fdc700 Y------ 552 cond > wait(await_MC1)2724 join_1.1 30fdcd0c Y------ 552 cond > wait(await_MC1)2725 join_2.0 30fdd318 Y------ 552 cond > wait(await_MC2)2726 join_2.1 30fdd924 Y------ 552 cond > wait(await_MC2)2727 scan_3.0 30fddf30 Y------ 552 cond > wait(await_MC3)2728 join_1.0 30fde53c Y------ 552 cond > wait(await_MC1)2729 join_1.1 30fdeb48 Y------ 552 cond > wait(await_MC1)2730 join_2.0 30fdf154 Y------ 552 cond > wait(await_MC2)2731 join_2.1 30fdf760 Y------ 552 cond > wait(await_MC2)2732 scan_3.0 30fdfd6c Y------ 552 cond > wait(await_MC3) > >scb sqscb optofc pdqpriority sqlstats optcompind > directives2cb9c0b8 2bb4f018 0 0 0 0 1 > > > > This proc does not run but a heartbeat. What do all the "cond wait..." > messages mean? > > Are those threads still alive? Or does onstat -t ses nnn report things > that have terminated (like -g sql). > > > > The function is called from a c++ COM dll, so perhaps the object is not > closing the connection? > > But it seems like the threads would die when the function returned the > last row. Not so? > > > Hi Bill, > > this probably has to do something with PDQPRIORITY, although the > onstat -g ses says it's running at PDQPRIORITY=0.> It may be the case you started the engine in the environment where > PDQPRIORITY env. var. was exported as greater than 0. Also, you may > try to update stats for all your procedures making sure PDQ is indeed > set to zero. > > HTH > > Davorin > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.