[Openroad-users] App Server - Timeout Interval
Jonathan Barton
What happens to calls that are currently being executed?
JONATHAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
”University of the West of England"
+ Work Email: jonatha...@uwe.ac.uk
( Work No: 0117 3281075
P Please consider the environment before printing.
This email was independently scanned for viruses by McAfee anti-virus software and none were found
Durwin Wright
I normaly set my Timeout Interval value to 60 minutes for stateless ASOs. This is an inactivity interval. If the Dispatcher Manager detects that an ASO has been inactive for this period of time then the ASO is scheduled for shutdown. You will see an entry in the SPOTrace file that indicates that the ASO was shutdown due to a Timeout. This timeout counter is reset to zero each time a new request is processed.
After an ASO has been shutdown due to ta Timeout, the next time a Call4GL request is scheduled for the shutdown ASO, an implicit Initiate will be done. This will result in a longer Call4GL execution time since the Initiate time is now considered part of the Call4GL request.
While the ASO is processing a request, the timeout interval does not have any affect.
I also typically set my Transaction Limit to 100,000 for stateless ASOs. This will shutdown the ASO after a fixed number of requests have been processed. An entry in the SPOTrace file will indicate that this action has been take. When the next Call4GL request is sent to the ASO, an imiplicit Initiate will be down on behalf of the requestor.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
Jonathan Barton
Sent: 28 September 2007 16:35
To: International OpenROAD Users
Subject: Re: [Openroad-users] App Server - Timeout Interval
This incoming email to UWE has been independently scanned for viruses by McAfee anti-virus software and none were detected
Durwin Wright
Hello Jonathan,
Let me dig out an example of how to detect a database lost and reconnect in the AppServer. I have it in my archives and should be able to find it today.
I will have to confirm this but I believe that the Timeout Inter\val affects each Slave individually. The Timeout Interval is probably enforced at the time that the Call4Gl is initially made. This means that the last Call4GL request will end in an error. When ths request is made again, this will cause the ASO to be restarted.
If you are running with SPOTrace set to on and appending the log, I would be interested in reviewing the logs. If you are interested, then contact me directly.
Neil Warnock
Typed painfully slowly on my BlackBerry® wireless device
-----Original Message-----
From: Durwin Wright <Durwin...@ingres.com>
Date: Mon, 1 Oct 2007 12:28:22
To:International OpenROAD Users <openroa...@peerlessit.com>
Subject: Re: [Openroad-users] App Server - Timeout Interval
Hello Jonathan,
Let me dig out an example of how to detect a database lost and reconnect in the AppServer. I have it in my archives and should be able to find it today.
I will have to confirm this but I believe that the Timeout Inter\val affects each Slave individually. The Timeout Interval is probably enforced at the time that the Call4Gl is initially made. This means that the last Call4GL request will end in an error. When ths request is made again, this will cause the ASO to be restarted.
If you are running with SPOTrace set to on and appending the log, I would be interested in reviewing the logs. If you are interested, then contact me directly.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
----------------
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Jonathan Barton
Sent: Monday, October 01, 2007 3:24 AM
To: International OpenROAD Users
Subject: Re: [Openroad-users] App Server - Timeout Interval
Many thanks for this Durwin.
This is exactly what I am after - I will implement the timeout interval asap. The reason I want it is because the ASO connection to the database has been lost and so it needs replacing. Would 30 minutes be unreasonable bearing in mind the afore-mentioned issue we face?
With regards to the transaction limit:
Does the purge affect all slaves at the same time?
And what happens to any requests that have not been completed?
We actually stop and start the OR AppServer service every morning anyway, which would be before way before 100000 transactions (max 20000).
Thanks again.
Jonathan
----------------
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Durwin Wright
Sent: 28 September 2007 16:35
To: International OpenROAD Users
Subject: Re: [Openroad-users] App Server - Timeout Interval
I normaly set my Timeout Interval value to 60 minutes for stateless ASOs. This is an inactivity interval. If the Dispatcher Manager detects that an ASO has been inactive for this period of time then the ASO is scheduled for shutdown. You will see an entry in the SPOTrace file that indicates that the ASO was shutdown due to a Timeout. This timeout counter is reset to zero each time a new request is processed.
After an ASO has been shutdown due to ta Timeout, the next time a Call4GL request is scheduled for the shutdown ASO, an implicit Initiate will be done. This will result in a longer Call4GL execution time since the Initiate time is now considered part of the Call4GL request.
While the ASO is processing a request, the timeout interval does not have any affect.
I also typically set my Transaction Limit to 100,000 for stateless ASOs. This will shutdown the ASO after a fixed number of requests have been processed. An entry in the SPOTrace file will indicate that this action has been take. When the next Call4GL request is sent to the ASO, an imiplicit Initiate will be down on behalf of the requestor.
Durwin Wright | Sr. Architect | Durwin...@ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
----------------
From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of Jonathan Barton
Sent: Friday, September 28, 2007 6:09 AM
To: International OpenROAD Users
Subject: [Openroad-users] App Server - Timeout Interval
Hi
Has anyone had experience in using the timeout Interval for a signature on the OR App Server?
What is the recommended level?
What happens to calls that are currently being executed?
Currently we have this set to 0, but due to a current issue with network connections dropping out the ASO slave connection becomes useless.
So much to learn... :-)
Many thanks.
JONATHAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
”University of the West of England"
+ Work Email: jonatha...@uwe.ac.uk <mailto:Jonatha...@uwe.ac.uk>
( Work No: 0117 3281075
P Please consider the environment before printing.
----------------
This email was independently scanned for viruses by McAfee anti-virus software and none were found
----------------
This incoming email to UWE has been independently scanned for viruses by McAfee anti-virus software and none were detected
----------------
This email was independently scanned for viruses by McAfee anti-virus software and none were found ________________________________________________________________
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-user...@peerlessit.com&subject=unsubscribe
To subscribe click on this link
mailto:openroad-use...@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-user...@peerlessit.com&subject=unsubscribe
To subscribe click on this link
mailto:openroad-use...@peerlessit.com&subject=subscribe
Gary Hansford
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
Durwin Wright
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...@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-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of Gary
Hansford
Sent: Tuesday, October 02, 2007 1:18 AM
To: International OpenROAD Users
David Tondreau
Joe Kronk will be giving the following webcast on October 10:
The open source community is about to gain a powerful new software
development environment that can be used alone or integrated with Java,
.NET, SOA and other technology to amplify developer productivity and
accelerate time to market for enterprise business applications.
Thousands of organizations across the globe including the likes of
Infor, Bording Data and the Irish Revenue Commissioners are already
using OpenROAD to deliver mission critical and market leading solutions.
Join OpenROAD’s Product Development manager Joseph Kronk as he
explores the plans for putting the "Open" into OpenROAD.
Time: 6am - 7am Pacific Daylight Time:
Time: 5pm - 6pm Pacific Daylight Time:
Register here: http://www.ingres.com/customers/vip-program.php
Regards,
David
David Tondreau
Architect, Ingres Corp.
http://community.ingres.com/
Jonathan Barton
Durwin Wright
Hello Jonathan,
I got the following from Neil Warnock. This is similar to what Gary posted.
From: Neil.W...@luminary.co.uk
Sent: Tuesday, October 02, 2007 10:32 AM
To: Durwin Wright
Subject: db reconnect
Here is a low overhead and slimmed down version of an error handler that reconnects
to the database on SQL error. In this version, the failing call is a casualty -
not always appropriate, I know. Happy for you to share this. You call it after
each SQL statement, and also call it from the startGhost passing p_i_setup=TRUE
If you wish you can override optional param 'exit_on_fatal' (defaults to TRUE)
so that the call doesn't terminate when an SQL error occurs.
Neil
/*----------------------------------------------------------------------------*/
/* */
/* Global Procedure: SQL Error Check */
/* */
/* Procedure Name : sql_errorcheck */
/* */
/* System Name : ASA_Library */
/* */
/* Author : Rich Smith (Luminary Solutions) */
/* */
/* Date : 23/03/04 */
/* */
/* Description : A simple error check function for the Application Server.*/
/* All errors are fatal and the application will attempt to */
/* reconnect to the database if the rollback of a */
/* transaction fails. */
/* */
/* Version History */
/* */
/* Version Date Who Description */
/* ------- -------- ----- ----------- */
/* 1.0 23/03/04 RIS Original version. */
/* */
/*----------------------------------------------------------------------------*/
PROCEDURE sql_errorcheck
(
/*----------------------------------------------------------------------------*/
/* Parameters passed by value */
/*----------------------------------------------------------------------------*/
p_i_setup = INTEGER NOT NULL DEFAULT FALSE,/* Control flag */
p_i_exit_on_fatal = INTEGER NOT NULL DEFAULT TRUE, /* Exit on fatal error?*/
/*----------------------------------------------------------------------------*/
/* Parameters passed BYREF */
/*----------------------------------------------------------------------------*/
b_i_errorno = INTEGER NOT NULL, /* SQL errorno */
b_v_errortext = VARCHAR(512) NOT NULL, /* SQL errortext */
b_i_rowcount = INTEGER NOT NULL, /* Rows affected */
)=
DECLARE
/*----------------------------------------------------------------------------*/
/* Variables local to this procedure */
/*----------------------------------------------------------------------------*/
l_i_errorno = INTEGER NOT NULL,
/*----------------------------------------------------------------------------*/
/* Procedures local to this global procedure */
/*----------------------------------------------------------------------------*/
/* none */
ENDDECLARE
BEGIN
/*------------------------------------------------------------------------*/
/* This procedure is called from the starting ghost frame with the setup */
/* flag set to TRUE. We will remember the current database */
/* and connection flags, to allow reconnection to the database in case we */
/* lose connection. */
/*------------------------------------------------------------------------*/
IF p_i_setup = TRUE
THEN
GV_DATABASE = CurProcedure.DBSession.Database;
GV_FLAGS = CurProcedure.DBSession.Flags;
CurSession.DBMSErrorPrinting = EP_OUTPUT;
SET SESSION WITH ON_ERROR = ROLLBACK TRANSACTION;
SET LOCKMODE SESSION WHERE READLOCK=NOLOCK;
COMMIT;
RETURN;
ENDIF;
INQUIRE_SQL( b_i_errorno = ERRORNO,
b_v_errortext = ERRORTEXT,
b_i_rowcount = ROWCOUNT );
IF b_i_errorno <> 0
THEN
/*------------------------------------*/
/* Rollback all errors. */
/*------------------------------------*/
ROLLBACK;
INQUIRE_SQL( l_i_errorno = ERRORNO );
/*------------------------------------*/
/* A failed rollback could suggest a */
/* loss of database connection. As a */
/* failover measure, we will close */
/* the current database session and */
/* reopen it. */
/*------------------------------------*/
IF l_i_errorno <> 0
THEN
CurProcedure.DBSession.Disconnect();
CurProcedure.DBSession.Connect( database = GV_DATABASE,
flags = GV_FLAGS );
SET SESSION WITH ON_ERROR = ROLLBACK TRANSACTION;
SET LOCKMODE SESSION WHERE READLOCK=NOLOCK;
COMMIT;
ENDIF;
IF p_i_exit_on_fatal = TRUE
THEN
OSCA.ReturnWithFatalError( p_i_error_no = b_i_errorno,
p_v_msg_txt = b_v_errortext );
/* No return from the above call */
ENDIF;
ENDIF;
END
/*----------------------------------------------------------------------------*/
/* --------------------- End of Global Procedure Script --------------------- */
/*----------------------------------------------------------------------------*/
Kim Ginnerup
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-us...@peerlessit.com [mailto:openroad-us...@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
David Tondreau
A long time ago, we wrote an OpenROAD application that was used in over
5000 locations across the country all dialing in to a central server in
Washington, DC. Dropped phone lines which resulted in lost networking
resulting in lost database connectivity was a big problem. We perfected
the art of SQL retry, building a wrapper that managed database sessions
which could automatically reconnect if the session was lost much like
the suggestions already posted.
However, to solve the "lost" SQL statements when a database session goes
away problem, our wrapper has two methods: "PrepareSession()" and
"CheckSession()". Then, we place all SQL in a while loop like this:
CurMethod.DbSession = CurObject.PrepareSession(
IsMST=IsMST, RequiredRows=RequiredRows, Status=Status);
while (CurObject.CheckSession(Status=Status) = ER_CONTINUE) do
//SQL
endwhile;
return Status.GetStatus();
The PrepareSession() simply sets an internal counter that says a SQL
statement is about to be executed and returns the managed database
session. The CheckSession() method actually checks the state of the
database session, reconnecting if necessary. On the first call, it
knows to simply return an ER_CONTINUE and increment the call counter.
On subsequent calls, it checks the error status of the statement and the
state of the session. If the statement executes successfully, it simply
returns ER_OK and puts a success code in the status object. If the
database session fails, it can reconnect and simply return an
ER_CONTINUE which will cause the statement to be retried. It can also
handle other things. For instance, if the number of "required" rows
(programmer defined) does not match, it can throw an error. If the MST
flag is false, it commits. If you want to test for deadlock errors or
any other specific database errors and handle them generically, it
provides a place to do that. Etc., etc.
In the next week or so I'll be publishing my Data Access Object
Generator utility on the community site. It generates OpenROAD user
classes that wrap tables in the database to provide a simple
object-relational infrastructure for building OpenROAD client and server
applications. The default template for the code generation in the tool
uses this construct and has all the wrapper classes as well.
Regards,
David
David Tondreau
Architect, Ingres Corp.
http://community.ingres.com/
________________________________________________________________
Jonathan Barton
-----Original Message-----
From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of David
Tondreau
Sent: 03 October 2007 12:25
To: International OpenROAD Users
//SQL
endwhile;
return Status.GetStatus();
Regards,
David
________________________________________________________________
OpenROAD-Users mailing list
This incoming email to UWE has been independently scanned for viruses by
McAfee anti-virus software and none were detected
This email was independently scanned for viruses by McAfee anti-virus software and none were found
________________________________________________________________