Part of our batch work requires exclusive access to a livedb, so
we need a UNIX script to remove all user sessions from the database.

We currently have a script that runs iinamu to get the server id's
then iimonitor to get the session id's,
then runs 'remove session' in iimonitor.

This all works swimmingly, but the code is quite complicated...
I was wondering if there is a better, simpler way to remove user
sessions?

Env is Ingres 2.5 & 2.6 on AIX 5.2

TIA Steve

"Steve McElhinney" <stevem (AT) 141 (DOT) com> wrote

Connect to imadb then EXECUTE PROCEDURE IMA_DROP_SESSIONS;

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

Roy Hann wrote:
Hi,

I have an Ingres report to produce the needed execute statements.

This report must be loaeded into imadb with sreport:
---snip---
..name removesessions
..output removesessions.sql
..query
select server, session_id from
ima_server_sessions
where db_name = 'fepa404'
..header report
.noff
.nl

.print "set autocommit on \\g" .nl
.print "\\sql" .nl
..detail
.print "execute procedure" .nl
.print "ima_remove_session(server_id = '" .print server .print "'," .nl
.print "session_id = '" .print session_id .print "')" .nl
.print "\\p\\g" .nl
---snip---

You will have to tweak the line line "where db_name..." to match your
environment.


A batch file removesessions.bat can be used to run it as a daily
scheduled task:
---snip---
report imadb removesessions > logfile.log
sql imadb < removesessions.sql >> logfile.log
---snip---

Regards
Gerhard

Hi

Like you, i need to remove all user sessions from the database.
I make a UNIX script but it also shuts down the DBMS server.

Please, could you give me your script or look at mine and correct it.

Thanks.

Stephane

Hi Stephane (and all the others),
I send you 2 examples :
- via a query on imadb (you can get the list of session_ptr by querying the
ima_server_sessions table in imadb)
- via iimonitor (sorry for english people, script comments are in french)
Perhaps you need to customize the following :
- path of grep and awk (I usualy use egrep and nawk)
- path of ksh (1st line of each shell)
- ensure those scripts are running with Ingres env variables
It should work "has it" on windows with cygwin interface.
Hope this help,
Jean-Pierre
Here is an example with IMADB :
*** SCRIPT BEGIN ***
#!/usr/bin/ksh
#
# Remove sessions from IMA

CONNECT_ID=${1:?"Give DBMS connect_id"}
SESSION_PTR=${2:?"Give a session ptr"}

export II_DBMS_SERVER=${CONNECT_ID}

echo "\
UPDATE
ima_mib_objects
SET
value = '1'
WHERE
server = dbmsinfo('ima_server')
AND
classid = 'exp.scf.scs.scb_remove_session'
AND
instance = '${SESSION_PTR}'
\p\g" | sql imadb

unset II_DBMS_SERVER
*** SCRIPT END ***
Here is the script with iimonitor
*** SCRIPT BEGIN ***
#!/usr/bin/ksh

#
# Obtenir la liste des PID des DBMS
LISTE_DBMS=$(ps -fuingres \
#
# Pour chaque PID de DBMS
for DBMS_PID in ${LISTE_DBMS}
do
#
# On recherche le connect_id
CONNECT_ID=$(csreport | grep ${DBMS_PID} | awk '{ print $7 }' | sed 's/,//')

#
# On liste les sessions (echo show sessions)
# Du dbms CONNECT_ID
# Et on isole le numero de session
# Que l'on ajoute a la chaine remove
# Que l'on repasse au meme DBMS (meme CONNECT_ID)
# ce qui permet de les detruires ... (et voila !!! ;-)
echo show sessions \
echo "Les sessions du DBMS ${CONNECT_ID}/${DBMS_PID} ont ete detruites."

done
*** SCRIPT END ***
2005/9/27, stéphane lepeltier <lepeltier.stephane (AT) free (DOT) fr>: