Sometimes I run scripts against remote servers which can take quite a long time to run. i.e. something like:

EXECUTE IMMEDIATE
'
BACKUP DATABASE "XXX" ....
';

EXECUTE IMMEDIATE
'
COPY FILE "XXXBU.EDBBkp" IN STORE ....
';

EXECUTE IMMEDIATE
'
COPY FILE "YYYBU.EDBBkp" IN STORE ....
';

There are times when I do this & EDBMgr just hangs. It states regularly that the connection with the server needs to be refreshed, and asks whether I want to reconnect, but otherwise it just hangs.

If I start another instance of EDBMgr I can see that the script appears to have fully completed. i.e. all the relevant backup files have been created / copied or whatever.

--

1. If I stop the hanging version of EDBMgr whilst the script appears to be running but the files have been created might the engine on the server do anything untoward?

2. Is there anything I might change in my setup either on the cloud or locally with EDBMgr which could improve things?

Adam,

There are a few things you can try.

On the client session:
1) Do you have "Enable Pinging" turned on? It defaults to 60 seconds. Lower it to 30 seconds. I'm thinking maybe the server is too busy backing up files that it misses some pings and thinks your session has expired.

If 1) doesn't solve the problem,

2) On the server:
Session Timeout: Increase it from 180 secs to 540 sec.

Change them one at a time to see if that solves the problem.

3) Does your application really need to wait for the stored procedure to complete? Why not just execute a job that runs the script. Then poll the log file (I assume the script writes to a log file to indicate success or failure) every 30 seconds? The client can enable the job by altering its run time to the current time. This way the client can disconnect and later check the job's success or failure by examining the log file.

Barry

Thanks very much for these practical suggestions Barry I will have a go with them. I think it is very likely that the CPU is too busy on my cloud server to respond in time so the session timeout may well be it.

I have quite a few of these scripts set up as jobs, I was partly raising the question to get a more rounded idea of what EDB does in situations where sessions drop out.

I assume once the "execute" is called the EDBSRVR "receives" and runs the whole SCRIPT, regardless of whether the Session is maintained or not.

i.e. if a SCRIPT consists of 10 EXECUTE IMMEDIATE statements these will _all_ get actioned even if the session drops out after (say) the 5th one.

Adam,

>Thanks very much for these practical suggestions Barry I will have a go with them. I think it is very likely that the CPU is too busy on my cloud server to respond in time so the session timeout may well be it.<

BTW, I get time outs (socket exceptions saying connection to server has been lost) when debugging an application if the program is stopped too long at a break point when connected to the server. But the program will reconnect automatically to the server when I press F9 and control returns to the grid/form. MySQL didn't do that and was a real PIA.

>I have quite a few of these scripts set up as jobs, I was partly raising the question to get a more rounded idea of what EDB does in situations where sessions drop out.

I assume once the "execute" is called the EDBSRVR "receives" and runs the whole SCRIPT, regardless of whether the Session is maintained or not.

i.e. if a SCRIPT consists of 10 EXECUTE IMMEDIATE statements these will _all_ get actioned even if the session drops out after (say) the 5th one.<

That was my interpretation too. Tim will have to confirm that or you can experiment by running time consuming scripts with a shorter timeout defined. I don't think the server will have a problem until it tries to communicate the results of the script back to the client (when the script completes). Since jobs don't communicate information back to the client, I think it would be be better using that if a task takes longer than a minute or two to complete. After all, why hang the client program for that long anyways waiting for the script to finish?

If you need the job to report information back to the client, then you could pass a GUID to a script that runs a job, and the job would output the results to a log file with that GUID as part of the key (or create a table using that GUID as the name after removing the "{}-" of course. I use CLOB field in a log file to store results. I suppose I could also store JSON in the CLOB but I haven't gotten that far yet. I prefer to roll my own at the moment using the parser I wrote.

(Temporary tables won't work because the session will be lost when the client connection is lost)  The GUID is generated using "Select Current_GUID()" on the client and that is passed to the server (the server doesn't generate the GUID and pass it back to the client because the client may never get it!). The client could store these GUID's that it generates to a local status table allowing the client program to quit. The client can then check back later to see if these jobs have completed by comparing the GUID's in the status table to the GUID's in the log table on the server. This allows the connection to the server to be lost after the job has been started, and the client rebooted, and you'll still have confirmation that the job has started and completed successfully or not.

Anyways, this is how I see it being done. There are probably other ways too, and other users can chime in too.

Barry

Adam,

<< There are times when I do this & EDBMgr just hangs. It states regularly
that the connection with the server needs to be refreshed, and asks whether
I want to reconnect, but otherwise it just hangs. >>

That doesn't sound like it's hanging.  Is it asking if you want to
*reconnect*, or is it asking if you want to *stay connected* ?  There's a
big difference between the two.

Tim Young
Elevate Software
www.elevatesoft.com