Thanks to all the suggestions.

I think my issue might relate to PFC framework platform, which required the SQLCA maintain living status all the time (which is pretty fine for client/server accessing).

Take below sales order screen for example, when clicking on 1 line, system will count the timing started from search result(previous DB access), and if in-between there is no database access, higher chance to hit DB transaction timeout.

Go for per transaction ProcedureInTransaction = 0/1 would not be applicable which will involve revamping of the whole application functionalities.

Right now I have set transaction timeout to be 3600 per Logan suggested and seems much better already.

At the same time, I am working on the timer approach to trigger DB access if hitting idle timeout - the way per Chris suggested - I was doing so by setting/logging the session time - currently the application is lunching both from Client/Server(with RDS publishing) and Cloud deployment, Client/Server session information has to be handled by using a dedicated session table.

Regards, JX

Hi JX,

Based on your PB project, if there is less code that calls Stored Procedure to update the database, you can also work around this issue by the ProcedureInTransaction option.  

First set ProcedureInTransaction=0. When the procedure or cursor requires to start the transaction, dynamically set ProcedureInTransaction=1 in the script to start the transaction and then set autocommit = true or execute Commit or RollBack to commit/close the transaction in time (see the example code below). In this case, you can set a reasonable value for the transaction timeout according to the business logics. And don't need to choose the LongConnnection solution.

Refer to: https://docs.appeon.com/pb2022/connection_reference/ProcedureInTransaction.html

Regards,
Logan

Armeen is correct:  Figure out why your transaction is timing out. And it IS the transaction as evidencd by the fact you keep hitting this error regarless of the value of the connection timeout of the database.

Hi Zhang;

  The problem with the suggestion of extending the Transaction Timeout is that in a mixed App system where there are various Apps in progress, the lower timeout is more desirable to catch runaway and/or stalled Apps so that critical resources can be freed. This is especially critical in high volume based transactional systems!

  The technique that I have been using for a few decades now is based on what I developed in Distributed PB and later on EAServer and that is a "heartbeat" feature where the App sends a DML command just before the DBMS timeout occurs. That will keep a native, PowerClient and / or PowerServer App active without the DBMS dropping the connection.

   I added the HeartBeat feature to my STD framework as a standard configurable feature over a decade ago since my use of this approach was so successful in the PB 5.0 => PB 12.x time frame for preventing such an issue as you described. Basically, a "preventative" approach.  FYI ...

Food for thought!  HTH  ;-)

Regards ... Chris

try have a look at this

https://docs.appeon.com/pb2022/connection_reference/LongConnection.html

also, have you tried to set Timeout and session to 0?  Not sure but i guess it should disable timeout

why not change the timeout to a longer value so you don't have to reconnect