• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Chasing Random Disconnects in DB Server to Client RDS Envionment

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Chasing Random Disconnects in DB Server to Client RDS Envionment

  1. Issue
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Tuesday, 4 April 2023 16:35 PM UTC

We have a PowerBuilder app PB17 LTR which talks to SAP SQL Anywhere 17.

The app is running on a VM Server running Remote Desktop Services with upwards of 50+ users.

The database is running on a separate VM Server dedicated to the Database

The IT department which manages the operation has reported intermittent cases where users are disconnected mid app with the -308 disconnect error .

With the help of SAP IT they have configured Server and Client Logs.

Searching the logs they found the following

“The number of prefetch rows has been reduced to 5 due to the prefetch buffer limit. Increasing the PrefetchBuffer connection parameter may improve performance.”

Does anyone have experience with dealing with disconnects - and did modifying the prefetch buffer change anything?

Are there any other techniques we can use to eliminate disconnects or trap the disconnect when it occurs?

 

 

 

  1. ODBC
  2. PowerBuilder
  3. SQL Anywhere
Microsoft 2010 C++ Redistributables
dw.SaveAs PDF composite report with blank pages
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Saturday, 8 April 2023 19:22 PM UTC
  2. PowerBuilder
  3. # 1

Hi Glenn;

  Another long term fix could be to implement a DB "Heartbeat" feature that always keeps your DB connection(s) alive.

FYIhttp://chrispollach.blogspot.com/2018/02

Food for thought. HTH

Regards ... Chris 

Comment
Glenn Barber
  1. Glenn Barber
  2. Sunday, 9 April 2023 01:43 AM UTC
Do you have any sample code for the heartbeat? I am still looking for some sample code for recovering from a disconection without crashing.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 9 April 2023 02:26 AM UTC
Hi Glenn;

The code is in my framework but the framework is free & open source under the Apache 2.0 license agreement. You're free to use the framework or extract any code for your framework or your application's use.

The main code is implemented in the SQLCA base ancestor "ns_sqlca_master" located in the std_fc_base.pbl library. Please let me know if you need any help implementing the code in your environment.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Saturday, 8 April 2023 18:07 PM UTC
  2. PowerBuilder
  3. # 2

After watching the database connection logs turned on both at the client and server as recommended by SQL Anywhere we discovered at least one source of disconnection - this is where the user sat in an app for more than 4 hours before performing any database activity.

Apparently, by default there was a 4 hour timeout for each connection.  We were able to modify this to a longer duration.  we may also want to connfigure remote access to logoff after long periods of inactivity.

BTW still look for some best practices and same code for cleanly handling disconnects without the app crashing.

Thanks

 

 

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Wednesday, 5 April 2023 06:24 AM UTC
  2. PowerBuilder
  3. # 3

Hi.

I never had to do any specific setting in sql anywhere for prefetch buffer. As stated in help file, it's default value is 512 KB. Values may vary between 64 KB and 8 MB.

By the way, there are two setting PrefetchRows and PrefetchBuffer. Those seem to be related. PrefetchRows has a default value of 10 (which for ADO.NET differentiates to 200). Maximum allowed value for PrefetchRows is 10000.

Now as I initially stated, I never had to use - set those values. In SQL Anywhere 17 documentation for PrefetchRows I see the following remark:

By default, the client library dynamically increases the number of prefetch rows for cursors that would gain a performance benefit from such an increase. Because of this behavior, increasing the PrefetchRow default does not significantly improve the performance of most applications.

Increasing the PrefetchRow default may improve the performance of ODBC, OLE DB, and SQL Anywhere JDBC applications that use STATIC or FORWARD ONLY FOR UPDATE cursor types but do mostly fetch next operations and very rare positioned updates or deletes, particularly if the client and database server are communicating over a slow or wide area network.

The number of prefetched rows is limited by the PrefetchBuffer (PBUF) connection parameter, which limits the memory available for storing prefetched rows.

The maximum number of rows that can be prefetched is 10000.

As you are using remote desktop, users are working remotely? Were you able to find if upon disconnection a specific query - command was executed?

Andreas.

Comment
Glenn Barber
  1. Glenn Barber
  2. Wednesday, 5 April 2023 19:18 PM UTC
Thank you for this - the main problem is the random disconnect and so far I dont have a clue as to whether or which query is triggering it - still waiting for more info from logs.



Do you have any suggestions for how best to trap this error - so at least the app doesnt crash and we can record the query that caused it. Something easy like a reconnect, rollback and relaunching the window after a message - rather than trying to recover mid transaction?
  1. Helpful
Glenn Barber
  1. Glenn Barber
  2. Thursday, 13 April 2023 19:44 PM UTC
After further testing - the random disconnects only came from network connection timeout. Chaning the timeout seems to have solved this issue!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.