[Openroad-users] App Server - Timeout Interval

Wed Oct 3 18:14:47 EST 2007

Interesting subject.
We have implemented a small housekeeper service running from NT_Services.
This will read the housekeeping interval from VASA for each application server. Zero means no housekeeping.
We have implemented a database disconnect time, so if the database is not accessed say within the last 30 minutes then the housekeeper will ask the application server to close the DB connection.
Each SQL call is checking a local flag to see if the app server thinks it is connected to the database. If not it will make a connection.
If an SQL statement fails because the db connection is accidentally lost, it will automatically disconnect and reconnect (a bit like Neil's example).
The problem here is that the SQL statement that failed will not be rerun.

But by automatically disconnect databases after a certain time of inactivity helps a lot, because inactive appservers have no db-connection after 30 mins.
So appservers do not get in the way for maintenance unless they are busy.
If they are busy at that time there is some planning that needs to be done.

We have struggled a lot with lost db-connections, but the above has almost solved the problem.

It would be nice with a very inexpensive way to check for valid db-connection.

Kim Ginnerup

-----Oprindelig meddelelse-----
Fra: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] På vegne af Durwin Wright
Sendt: 2. oktober 2007 15:09
Til: International OpenROAD Users
Emne: Re: [Openroad-users] App Server - Timeout Interval

Hello Jonathon,

What Gary has provided you will work very well.  This will add less than
30 mSec per request but it will make the lost of a database connection
virtually invisible.

If the attempt to reconnect fails then you should return an error for
the SCP request but leave the ASO running.  This check will attempt to
reconnect to the server each time the SCP call is made.

Separately, you may want to have either a ghostframe application (or
some other time driven application) make a health check SCP call to make
sure that the database is still running.  If the test fails (after
serveral failed attempts), then you could have the application send an
email notifying your support organization than something is wrong.

Once the problem is corrected, then the logic in your ASO will
automatically reconnect the database connections as each new SCP call is
processed.

Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com | Ingres | 500
Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA  +1
650-587-5523 | fax: +1 650-587-5550 

-----Original Message-----
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Gary
Hansford
Sent: Tuesday, October 02, 2007 1:18 AM
To: International OpenROAD Users
Subject: App Server - Timeout Interval

Jonathon,

   Handling a DBMS connection dropping in an ASO...

   We do this all the time - to allow our backups to get in and do their
work...

   You'll need to create a 4GL proc with code something like this: -

   ------------------------
   l_i_errorno = 0;

   IF Curprocedure.DBSession.State = DS_CONNECTED
   THEN

       // Do some SQL to prove DBMS connection is really OK...

       CurSession.DBMSErrorPrinting = EP_NONE;
       SELECT DBMSINFO('DATABASE') AS :l_v_database;
       INQUIRE_QSL (l_i_errorno = DBMSERROR);
       COMMIT;

       CurSession.DBMSErrorPrinting = EP_OUTPUT
   ENDIF;

   IF Curprocedure.DBSession.State != DS_CONNECTED
   OR l_i_errorno                  != 0
   THEN

       l_i_status = CurProcedure.DBSession.Disconnect();

       l_i_status = CurProcedure.DBSession.Connect(
                           database = :GC_V_DBNAME,
                           flags    = :GC_V_DBFLAGS
                           );

       // if l_i_status != ER_OK - we've had it !!

       l_i_status = YOUR_4GLPROC_WITH_SETUP_SQL(); // e.g. SET
SESSION... lockmode...id...
   ENDIF;
   ------------------------

   After you've created the procedure, you'll need to ensure every SCP
calls this right at the beginning - before any SQL is used. Give your
4GL procedure a meaningful name (e.g Check_Reconnect_DBMS) so its
obvious what its doing there...

   You should also change your standard error handler (invoked after
every SQL statement), to handle a DBMS connection dropped issue - by
returning an error to the front end - BUT resetting the error count and
NOT issuing an EXIT statement...

   Doing this will allow your ASO to continue working with a DBMS
connection problem being invisible to the caller unless it occurs during
an SCP call (in which case the caller will see the error occurring)..

   Hope this makes sense...

   Cheers

Gary

E-Mail Notice and disclaimer.

This e-mail (including any attachment) is intended for the addressee
named above. As this e-mail may contain confidential or privileged
information, if you are not the named addressee, or the person
responsible for delivering the message to the named addressee, please
notify us immediately.
Every effort has been made to ensure that this e mail and any attachment
is free from viruses but we give no warranty to that effect and can
accept no responsibility for any losses resulting from infected e-mail.
The internet cannot guarantee the integrity of this message. Indesit
Company UK Ltd (and its subsidiaries) shall not be liable for the
message if modified. Please note that any views expressed in this e-mail
may be those of the author and do not necessarily reflect those of this
organisation.
Indesit Company UK Limited
Registered Office: Morley Way, Peterborough, PE2 9JB.
Company Number 106725. EEE Reg. Number WEE/DH0057TS

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe 

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe 