How to identify a run away process in Database

Hi All, I want to identify what are run away process still running in the database. I can do "onstat -g ses <sessionid> and get the pid of the session. However, i can't validate that it is a runaway job. Is there a way that i can select from a database? or onstat, how would i know, what are the indication? Thanks,

If i have this example: => onstat -g ses 33351 IBM Informix Dynamic Server Version 7.31.FD8X5 -- On-Line -- Up 6 days 00:48:19 -- 4074880 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 33351 user1 - 133652 phiupr06 1 278528 272032 tid name rstcb flags curstk status 344979 sqlexec 7000000855ecf68 ---PR-- 8496 running Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 33351 V 700000084b62028 278528 6496 656 4 name free used name free used overhead 0 176 scb 0 144 opentable 0 19808 filetable 0 3544 ru 0 280 log 0 4216 temprec 0 1720 keys 0 1512 ralloc 0 191912 gentcb 0 13568 ostcb 0 3408 sqscb 0 12008 rdahead 0 1120 hashfiletab 0 552 osenv 0 2272 sqtcb 0 5104 fragman 0 10384 shmblklist 0 304 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 33351 - users_db DR Not Wait 0 0 7.31 => onstat -u|grep 33351 7000000855ecf68 ---PR-- 33351 user1 - 0 0 840 4322078 0

It appears you're asking four different questions, each with a different answer: 1. How to identify a runaway client process, which is an OS question 2. How to identify a runaway DBMS session or thread 3. How to identify a zombie client process 4. How to identify a zombie DBMS session or thread I'm guessing that by "runaway", you mean threads or processes using far too much CPU time. For OS processes, you'll have to use OS commands or tools. You didn't tell us the platform, so I'll guess. On Linux or Unix, ps -ef is what I normally use. A defunct process is normally labelled as such, and by watching the time use, you can usually find the processing chewing up inordinate time. That does mean you have to understand which processes normally do use a lot of CPU cycles. The equivalent on Windows servers is different, and I'm not sure of exactly how to do it. A runaway thread or session in the DBMS is often found by examining the nreads and nwrites columns of onstat -u and onstat -g ses for suspect sessions. Again, you have to know the magnitude of the expected values. Large reporting queries may to millions of reads for example, and that would not constitute a runaway session. But looking up a customer's balance due should take something under 100 reads in all probablilty. Zombie sessions are trickier. I start by matching up the process id of each client (from onstat -g ses) with OS processes. If some sessions have no client process, then that's suspect as a defunct session. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "JACK PAPA" <informix2009@gmail.com> To: ids@iiug.org Date: 08/20/2010 02:50 AM Subject: How to identify a run away process in Database [20973] Sent by: ids-bounces@iiug.org Hi All, I want to identify what are run away process still running in the database. I can do "onstat -g ses <sessionid> and get the pid of the session. However, i can't validate that it is a runaway job. Is there a way that i can select from a database? or onstat, how would i know, what are the indication? Thanks, ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, I've used ps -ef|grep pid, or ps -ef|grep uid on the OS level. I'm asking if there's any significant (like tty is equal "-" ) command to identify from the DB. I'm asking this because a shell script can embed multiple shell script that calls SQLs to run on the DB. Now, since on the OS level, i cannot kill any process as it is Admin task, I can kill processes inside the DB. one question, is there a way to identify a zombie or defunct process in DB?. The user cannot give me the PID (OS Level) as the application was not properly exited. This left some sessions inside the database. Example: a ControlM job is calling Multiple scipt shell script and these calls SQLs. onstat ?

Hello! Here, I only monitor the OS processes, grepping for the '?' character, excluding the services, or users, that I are regularly (normally runs). The db sessions of any os process like this "blacklist" is inside de Informix 4gl program that called it. So when we kill the OS process that was blocked, the database session(s) are even dead. The other trouble is when the connection is external to the database (.net app for example). Then there is no OS process running, just the database session. On that case, I monitor the sessions threads with most cpu time, most memory used, and so on. (Of course it´s a linux machine, ok?) Hope this helps. Best regards. Em 20/08/2010 09:12, JACK PAPA escreveu: > Hi, > > I've used ps -ef|grep pid, or ps -ef|grep uid on the OS level. I'm asking if > there's any significant (like tty is equal "-" ) command to identify from the > DB. I'm asking this because a shell script can embed multiple shell script > that calls SQLs to run on the DB. Now, since on the OS level, i cannot kill > any process as it is Admin task, I can kill processes inside the DB. > > one question, is there a way to identify a zombie or defunct process in DB?. > The user cannot give me the PID (OS Level) as the application was not properly > exited. This left some sessions inside the database. > > Example: a ControlM job is calling Multiple scipt shell script and these calls > SQLs. > > onstat ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Informix Dynamic Server Certified Professional V10 / V11

I user onperf a gui tool that works well this will very quickly show you whats top of the heap very quickly. Have have to have xwindows running though. It is An old tool that i run . Comes standard with informix