Sessions which are not disconnected properly

From: kurt@beacondesign.com (Kurt Umholtz)
Subject: Re: Attempts at removing rogue sessions fail!

In article , 
bhoylma@advtech.USWest.COMi says...
>
>
>OS: Sun Solaris 2.3
>INGRES: 6.4/05 (su4.us5/02)
>
>For some reason certain client session disconnects tend to leave
>unattached database sessions connected to the DBMS server, as shown by
>ipm and iimonitor.  Here is a representative sample from an ipm listing
>of sessions:
>
>IPM
>Server List
> Name            Type     Sessions  Active  Connecting to database(s)
> 32811           INGRES         33       0  ALL
>Session List
>ID       Name           TTY    Database    State Facil Query
>
>cbb740   user           pts/10 prod_db     BIO
>c931c0   user           pts/10 prod_db     BIO
>cdff80   user           pts/12 prod_db     BIO
>cec880   user           pts/12 prod_db     BIO
>cfa040   user           pts/1  prod_db     BIO
>d0c880   user           pts/1  prod_db     BIO
>d12300   user           pts/4  prod_db     BIO
>d56880   user           pts/4  prod_db     BIO
>d5e380   user           pts/3  prod_db     BIO
>
>This particular user is NOT connected to database nor does s/he have any
>login connections to the server machine or any running processes in the
>process table.  This indicates to me these sessions are not connected to
>any viable running processes.
>
>This frequently happens to other users as well.  I believe it has
>something to do with they way they shutdown their login sessions from a
>Mac Telnet application.  Pure supposition ...
>
>I am unable to delete these sessions using the iimonitor or the ipm
>delete session facilities.  Attempting to do so *appears* to delete the
>session (i.e. a message is returned stating the session has been
>deleted) but refreshing the session list or restarting the utility
>indicates the session(s) are still attached.
>
>With these sessions present I am unable to do any type of DBMS server
>shutdown, a process done routinely in support of database maintenance
>purposes.
>
>What do you suggest as a way of removing these sessions outside of the
>above named utilities and short of rebooting the machine.  The latter
>does in fact clean up the sessions but is approx. a 45 - 60 minute
>solution.  With active users, it's not a solution at all.  Any
>troubleshooting suggestions?
>
>I have a tech support call in but do not anticipate an answer before
>Monday or Tuesday.  I intend to post here then browse the Advisor
>archives to see if I can find any relevant information.
>
>Any comments/suggestions are welcome.  I'll gladly post a summary of any
>email I might receive regarding this problem if it proves valuable.
>
>Peace.
>-- 
>     Bruce W. Hoylman (303-541-6557) -- bhoylma@advtech.USWest.COM
>   -     __o    Speaking for myself...        /\/\    /\
>-  - -  -\<,    "Please saw my legs off".    /~/~~\/\/~~\
> -  ____O/_O_______________________________/\ /    \ \/\ \______

Sometimes lost tcp/ip connections can go undetected indefinitely.  I've
seen this most often with crashed PC clients.  Preventing awkward shutdowns
of the Ingres client is the best way to stop this problem.  But it may
be out of your hands so here is best method for removing 'sticky' session. 

1)	run iimonitor
2)	suspend the session
3)	remove the session
4)	resume the session

This works well on most but NOT ALL sticky sessions.  

If this doesn't work, it's lucky you are using a dbms with robust recovery 
features.  Ingres does a good job of surviving crashes, especially controlled
ones.  Rather than rebooting, a controlled kill/shutdown of Ingres.  This 
will help improve your already decent chances of keeping your database(s)
consistent.  Tech support may help you to get the sequence of such a shutdown
right.  If they can't, email me and I'll try to help you.

Good luck.

Kurt Umholtz
Senior Consultant
Beacon Design Group, Inc.
kurt@beacondesign.com
Ingres Q & A
To William's Home Page
Email William