FWD » Core Development » Database

I could not capture the raised condition via ON ERROR/QUIT/STOP/END-KEY - at least for mpro, it seems to just terminate the client. For appservers, we need to find what errors (if any) are logged in its log file.

define var h as handle.
define var h2 as handle.
define var h3 as handle.
define var h4 as handle.
define var h5 as handle.
create server h.
create server h2.
create server h3.
create server h4.
create server h5.
message "start".
message h:CONNECT("-URL http://localhost:8810/apsv").
message h2:CONNECT("-URL http://localhost:8810/apsv").
message h3:CONNECT("-URL http://localhost:8810/apsv").
message h4:CONNECT("-URL http://localhost:8810/apsv").
message h5:CONNECT("-URL http://localhost:8810/apsv").

define variable async as handle.
DO :
    run procedures/limit.p  on server h asynchronous.
    run procedures/limit.p  on server h2 asynchronous.
    run procedures/limit.p  on server h3 asynchronous.
    run procedures/limit.p  on server h4 asynchronous.
    run procedures/limit.p  on server h5 asynchronous.
end.

message ERROR-STATUS:ERROR.
h:DISCONNECT () no-error.
delete object h.

h2:DISCONNECT () no-error.
delete object h2.

h3:DISCONNECT () no-error.
delete object h3.

h4:DISCONNECT () no-error.
delete object h4.

h5:DISCONNECT () no-error.
delete object h5.

• The server connection does not automatically reserve a db connection.
• The connection to the db is attempted when a MSA session is started as evidenced in the logs below:
  

  @02:17:16.958-1000] P-002088 T-006936 2 AS-13 AS Application Server connected with connection id: EAAAFD0B1F99325E8CBCD68CB3A2E5C4020FA2A68BA8.oepas1. (8358)
  [26/02/03@02:17:16.962-1000] P-002088 T-006936 2 AS-18 AS Starting MSAS Session for oepas1.
  [26/02/03@02:17:16.962-1000] P-002088 T-006936 2 AS-18 AS MSAS Session Startup. (5473)
  [26/02/03@02:17:17.008-1000] P-002088 T-006936 1 AS-18 -- The server or the system has no more resources. Please contact Progress Technical Support. (748)
  [26/02/03@02:17:17.008-1000] P-002088 T-006936 1 AS-18 -- Error initializing the application server. (5479)
  [26/02/03@02:17:17.008-1000] P-002088 T-006936 1 AS-18 MSAS Unable to initialize session!
  [26/02/03@02:17:17.008-1000] P-002088 T-006936 1 AS-Aux MSAS ERROR: Could not get and/or initialize 'Stateless' session! Cannot process request.
  [26/02/03@02:17:17.008-1000] P-002088 T-006936 1 AS-Aux MSAS Error handling request! Status=-1003
  [26/02/03@02:17:17.010-1000] P-002088 T-006936 1 AS-Aux MSAS Worker Thread exiting. Number: 5, Status: -14
  

  
  In this case a session attempted to acquire a db connection, but they were exhausted by other sessions, hence the errors.
• The errors cannot be caught, nor is the ERROR-STATUS set, at least with async execution.

In fwd the connection to the DB is not as straightforward, the connections can be depleted in the process of executing a session, they are not reserved, so replicating this exact behavior is not going to be possible.

The certain thing is that the client does not know about this, I suspect that the run just aborts the execution.

Did you configure the database to be automatically connected in the PASOE config, and set the -n argument at proserve? We can check also the CONNECT statement and do not automatically connect the DB.

Constantin Asofiei wrote:

Did you configure the database to be automatically connected in the PASOE config, and set the -n argument at proserve?

Yes.

Connecting with

define var connString as char.
connString = "-db C:\DB\tstcases.db -ld tstcases -H localhost -S 7777".
CONNECT VALUE(connString).

It is a bit tricky with the connect statement and appservers, the client cannot connect to a db and use the connection to run the procedures on, but what I found to be working is the following structure:

client:
runs procedure conn.p

conn.p
connects to the database and runs prog.p

prog.p
uses the db.

in this case the results are the same as the appserver connected through a config.

As far as I can understand, in OE, the db connection is reserved when starting a MSA session, whereas in FWD, the connection is reserved on orm session start, which is much smaller scale ans short lived, this also happens at a later point in the MAS task execution. This is relevant because we cannot drop the request at the same point, but we still need to drop it and log this event, rather than to fail abruptly.

• 1 PASOE agent with 300 executor threads
• the session manager config for the appserver:
  

  [AppServer.SessMgr]
      maxABLSessionsPerAgent=200
      numInitialAgents=1
      maxAgents=10
      agentStartupParam=-T "${catalina.base}/temp" #-db C:\DB\sports2000.db -ld sports2000 -H localhost -S 7777
      maxConnectionsPerAgent=16
  

  
  where

• maxAgents
  Maximum number of concurrent multi-session agents that can run on the
  AppServer.

• maxABLSessionsPerAgent
  Maximum number of concurrent sessions a multi-session agent can run
  concurrently.

• maxConnectionsPerAgent
  Maximum number of tcp connections between session manager and multi-
  session agent.

• A database started manually with:
  

      proserve -H localhost -S 7777 -n 5 C:\DB\tstcases.db
  

In my understanding an ABL session is connecting to the database, so with my config, I would need a maximum of 200 db connections.
To test this theory I reduced maxABLSessionsPerAgent=2, this will result in 2 sessions with 2 connections that get reused for the 5 async runs.
Indeed The server or the system has no more resources. was solved.
I will investigate on a lower level to confirm this, but in OE, it seems, nr db connections = nr agents * maxABLSessionsPerAgent + batch processes and super connections.

I checked the OE database logs, on agent startup there is no activity in the database, in the agent logs there are no entries indicating an intention to connect to the database.
When running the procedures on the appserver:
agent.log

[26/02/04@23:10:42.068-1000] P-004624 T-001532 2 AS-11 AS Application Server connected with connection id: 58729F273604595915079C647EE3E8DB1C435F1A9365.oepas1. (8358)
[26/02/04@23:10:42.074-1000] P-004624 T-001532 2 AS-11 AS Application Server connected with connection id: 501B0CF3D88D683AB37C643E768F030F52151D6B7B3F.oepas1. (8358)
[26/02/04@23:10:42.080-1000] P-004624 T-001532 2 AS-11 AS Application Server connected with connection id: DA40D4E2EE291147DD74ACC62CB9E089B01DC328169B.oepas1. (8358)
[26/02/04@23:10:42.084-1000] P-004624 T-001532 2 AS-11 AS Application Server connected with connection id: 69739EDC09F63B9878E1A59B10500A68E5B72BBE41B1.oepas1. (8358)
[26/02/04@23:10:42.087-1000] P-004624 T-001532 2 AS-11 AS Application Server connected with connection id: 92174EADD8B632ECC3439F0B8538E1025C2AE3BC7E33.oepas1. (8358)
[26/02/04@23:10:42.089-1000] P-004624 T-005280 1 AS-Listener MSAS Spawning New Worker Thread. Number: 4
[26/02/04@23:10:42.090-1000] P-004624 T-005280 1 AS-Listener MSAS Spawning New Worker Thread. Number: 5
[26/02/04@23:10:42.091-1000] P-004624 T-005280 1 AS-Listener MSAS Spawning New Worker Thread. Number: 6
[26/02/04@23:10:42.091-1000] P-004624 T-005280 1 AS-Listener MSAS Spawning New Worker Thread. Number: 7
[26/02/04@23:10:42.100-1000] P-004624 T-005292 1 AS-4 -- (Procedure: 'start2.p' Line:3) The server or the system has no more resources. Please contact Progress Technical Support. (748)
[26/02/04@23:10:42.101-1000] P-004624 T-005492 1 AS-10 -- (Procedure: 'start2.p' Line:3) The server or the system has no more resources. Please contact Progress Technical Support. (748)
[26/02/04@23:10:42.107-1000] P-004624 T-001532 2 AS-11 CONN           Database tstcasesdb Options:  (12699)
[26/02/04@23:10:42.108-1000] P-004624 T-003780 2 AS-13 CONN           Database tstcasesdb Options:  (12699)
[26/02/04@23:10:42.108-1000] P-004624 T-001552 2 AS-12 CONN           Database tstcasesdb Options:  (12699)
[26/02/04@23:10:42.109-1000] P-004624 T-001532 2 AS-11 CONN           Connected to database tstcasesdb, user number 8. (9543)
[26/02/04@23:10:42.110-1000] P-004624 T-003780 2 AS-13 CONN           Connected to database tstcasesdb, user number 7. (9543)
[26/02/04@23:10:42.110-1000] P-004624 T-001552 2 AS-12 CONN           Connected to database tstcasesdb, user number 6. (9543)
[26/02/04@23:10:42.131-1000] P-004624 T-005292 1 AS-10 -- The client's code page in unknown. No code page conversions will be made. (8031)
[26/02/04@23:10:42.131-1000] P-004624 T-005292 2 AS-10 AS Application Server disconnected with connection id: 58729F273604595915079C647EE3E8DB1C435F1A9365.oepas1. (8359)
[26/02/04@23:10:42.131-1000] P-004624 T-001552 1 AS-12 -- Connection failure for host 10.0.2.15 port 49537 transport TCP. (9407)
[26/02/04@23:10:42.131-1000] P-004624 T-001552 1 AS-12 MSAS Error handling request! Status=-4004
[26/02/04@23:10:42.133-1000] P-004624 T-001552 2 AS-12 CONN           Disconnecting from database tstcasesdb, user number 6. (9545)
[26/02/04@23:10:42.137-1000] P-004624 T-005492 2 AS-11 AS Application Server disconnected with connection id: 501B0CF3D88D683AB37C643E768F030F52151D6B7B3F.oepas1. (8359)
[26/02/04@23:10:42.143-1000] P-004624 T-005292 2 AS-11 AS Application Server disconnected with connection id: DA40D4E2EE291147DD74ACC62CB9E089B01DC328169B.oepas1. (8359)
[26/02/04@23:10:42.149-1000] P-004624 T-001532 2 AS-13 AS Application Server disconnected with connection id: 69739EDC09F63B9878E1A59B10500A68E5B72BBE41B1.oepas1. (8359)
[26/02/04@23:10:42.154-1000] P-004624 T-005492 2 AS-13 AS Application Server disconnected with connection id: 92174EADD8B632ECC3439F0B8538E1025C2AE3BC7E33.oepas1. (8359)
[26/02/04@23:10:42.244-1000] P-004624 T-001552 1 AS-Aux MSAS Worker Thread exiting. Number: 5, Status: -14
[26/02/04@23:10:42.244-1000] P-004624 T-005956 1 AS-ResourceMgr MSAS Spawning New Worker Thread. Number: 9

database.log

[2026/02/04@23:10:42.098-1000] P-2932       T-1400  I SRV     2: (742)   Login usernum 8, userid SYSTEM client type PASN, on ASIVM2 using TCP/IP IPV4 address 127.0.0.1. 
[2026/02/04@23:10:42.098-1000] P-2932       T-1400  I SRV     2: (7129)  Usr 8 set name to SYSTEM. 
[2026/02/04@23:10:42.099-1000] P-2932       T-1400  I SRV     2: (17961) User 8 set tty to ASIVM2. 
[2026/02/04@23:10:42.099-1000] P-2972       T-3636  I SRV     3: (742)   Login usernum 7, userid SYSTEM client type PASN, on ASIVM2 using TCP/IP IPV4 address 127.0.0.1. 
[2026/02/04@23:10:42.100-1000] P-2972       T-3636  I SRV     3: (7129)  Usr 7 set name to SYSTEM. 
[2026/02/04@23:10:42.100-1000] P-5264       T-5384  I BROKER  0: (1154)  No servers are available.  Try again later. 
[2026/02/04@23:10:42.100-1000] P-2972       T-3636  I SRV     3: (17961) User 7 set tty to ASIVM2. 
[2026/02/04@23:10:42.100-1000] P-2404       T-5056  I SRV     4: (742)   Login usernum 6, userid SYSTEM client type PASN, on ASIVM2 using TCP/IP IPV4 address 127.0.0.1. 
[2026/02/04@23:10:42.100-1000] P-2404       T-5056  I SRV     4: (7129)  Usr 6 set name to SYSTEM. 
[2026/02/04@23:10:42.100-1000] P-2404       T-5056  I SRV     4: (17961) User 6 set tty to ASIVM2. 
[2026/02/04@23:10:42.101-1000] P-5264       T-5384  I BROKER  0: (1154)  No servers are available.  Try again later. 
[2026/02/04@23:10:42.108-1000] P-2932       T-1400  I SRV     2: (7129)  Usr 8 set name to SYSTEM. 
[2026/02/04@23:10:42.109-1000] P-2972       T-3636  I SRV     3: (7129)  Usr 7 set name to SYSTEM. 
[2026/02/04@23:10:42.110-1000] P-2404       T-5056  I SRV     4: (7129)  Usr 6 set name to SYSTEM. 
[2026/02/04@23:10:42.133-1000] P-2404       T-5056  I SRV     4: (739)   Logout usernum 6, userid SYSTEM, on ASIVM2. 

I think this is conclusive evidence that the db connection is established on session start and is then held by that connection.
In this case there were 3 available db connection and 3 users/sessions running on 3 different threads connected to the database, the other 2 got disconnected on account of connection starvation.

I think this design is quite similar to ours, except for the moment when the db connection is requested.

Eric,
to answer your questions from the meeting.

• what happens when c3p0 runs out of connections?
  The requesting thread is put in a queue and waits until one is available or wait time exceeds the timeout (no timeout by default).

• does OE establish a connection for every session executing on a PASOE agent?
  Yes, even if the procedures executed on the appserver do 0 database work, a db connection is established, if there are connections available.
  I checked with the auto-connect feature of the agent, no explicit connects.

With the current infrastructure, handling database connection starvation remains challenging

Key Challenges:

1. Logging and Observability

• Harder to interpret in our context
• Noisy
• Inconsistent with the rest of the application logging approach

2. Handling Data Changes Prior to Connection Failure

During a single MSA session, a database connection may be requested multiple times.
A failure can therefore occur at different stages of execution:

• Unbound (first) session
  The appropriate action is to roll back the current session only.

• Bound session
  The situation is more complex. Since work may already have been performed in previous bound sessions, it is unclear whether we should:
  • Roll back only the current session, or
  • Roll back the current session and all previously bound sessions.

Comparison with OE Behavior

• A database connection is requested.
• If available, it is bound to that session.
• That same connection is reused for the entire duration of the session, including all bound sessions.
• If no connection is available, the initial session is dropped immediately.
• No work is performed on the appserver.

This fail-fast model prevents partial work and eliminates ambiguity around rollback handling.

Constantin, please advise.

In OE, failure happens early.

What about a communications/network failure that happens later?

I was talking strictly about insufficient database connections, if the connection is interrupted during a session, I suppose appropriate errors are raised and, if in active transaction, it is rollbacked. I will test a connection interruption in OE.

Artur Școlnic wrote:

In OE, failure happens early.
At the beginning of an unbound session:

• A database connection is requested.
• If available, it is bound to that session.

Just to get this out of the way, replicating this behavior in FWD would be a non-optimal solution, the jdbc connections are a precious resource that cannot the bound to a session which can be idle, it is a poorly scalable approach.

When it comes to database shutdown during an open session:
1. program executed on the server - is terminated, error cannot be caught, even as a stop condition, the database closes the connections and AVM terminates the program.
database log

[2026/02/25@03:37:27.896-1000] P-768        T-4692  I SHUT    6: (542)   Server shutdown started by Administrator on CON:. 
[2026/02/25@03:37:28.061-1000] P-3484       T-4160  I BROKER  0: (15193) The normal shutdown of the database will continue for 10 Min 0 Sec if required. 
[2026/02/25@03:37:28.062-1000] P-3484       T-4160  I BROKER  0: (2248)  Begin normal shutdown 
[2026/02/25@03:37:29.063-1000] P-3484       T-4160  I BROKER  0: (2263)  Resending shutdown request to 1 user(s). 
[2026/02/25@03:37:57.961-1000] P-1568       T-5304  I ABL     5: (5403)  Terminating user Administrator on tty CON:. 
[2026/02/25@03:37:58.081-1000] P-3484       T-4160  I BROKER  0: (2527)  Disconnecting dead user 5. 
[2026/02/25@03:38:02.105-1000] P-3484       T-4160  I BROKER  0: (15109) At Database close the number of live transactions is 0. 
[2026/02/25@03:38:02.105-1000] P-3484       T-4160  I BROKER  0: (15743) Before Image Log Completion at Block 28726 Offset 4392. 
[2026/02/25@03:38:02.106-1000] P-3484       T-4160  I BROKER  0: (453)   Logout by Administrator on CON:. 
[2026/02/25@03:38:02.107-1000] P-3484       T-4160  I BROKER   : (16869) Removed shared memory with segment_id: 48365568 
[2026/02/25@03:38:02.107-1000] P-3484       T-4160  I BROKER   : (334)   Multi-user session end. 
[2026/02/25@03:38:02.916-1000] P-768        T-4692  I SHUT    6: (453)   Logout by Administrator on CON:. 

[26/02/25@03:33:27.168-1000] P-004640 T-003512 2 AS-10 CONN           Disconnecting from database tstcasesdb, user number 12. (9545)
[26/02/25@03:33:27.169-1000] P-004640 T-001956 2 AS-ResourceMgr MSAS Incremented connection count for ResrcMgrConnection_8 (tstcasesdb): 1
[26/02/25@03:33:27.171-1000] P-004640 T-001956 2 AS-ResourceMgr CONN           Database ResrcMgrConnection_8 Options:  (12699)
[26/02/25@03:33:27.172-1000] P-004640 T-001956 2 AS-ResourceMgr CONN           Connected to database ResrcMgrConnection_8, user number 5. (9543)
[26/02/25@03:33:27.172-1000] P-004640 T-003512 2 AS-10 MSAS Incremented connection count for ResrcMgrConnection_8 (tstcasesdb): 2
[26/02/25@03:33:27.173-1000] P-004640 T-003512 2 AS-10 CONN           Database tstcasesdb Options:  (12699)
[26/02/25@03:33:27.174-1000] P-004640 T-003512 2 AS-10 CONN           Connected to database tstcasesdb, user number 6. (9543)
[26/02/25@03:33:30.177-1000] P-004640 T-003512 2 AS-10 MSAS Disconnecting db tstcasesdb.
[26/02/25@03:33:30.177-1000] P-004640 T-001956 2 AS-ResourceMgr MSAS Decremented connection count for ResrcMgrConnection_8 (tstcasesdb): 1
[26/02/25@03:33:30.177-1000] P-004640 T-001956 2 AS-ResourceMgr MSAS Disconnecting resource manager ResrcMgrConnection_8 (tstcasesdb).
[26/02/25@03:33:30.177-1000] P-004640 T-001956 2 AS-ResourceMgr CONN           Disconnecting from database ResrcMgrConnection_8, user number 5. (9545)
[26/02/25@03:33:30.177-1000] P-004640 T-001956 2 AS-ResourceMgr MSAS Removed connection ResrcMgrConnection_8 from session 2.

database log

[2026/02/25@03:33:28.487-1000] P-5920       T-6012  I SHUT    7: (452)   Login by Administrator on CON:. 
[2026/02/25@03:33:29.728-1000] P-5920       T-6012  I SHUT    7: (542)   Server shutdown started by Administrator on CON:. 
[2026/02/25@03:33:29.856-1000] P-5760       T-4740  I BROKER  0: (15193) The normal shutdown of the database will continue for 10 Min 0 Sec if required. 
[2026/02/25@03:33:29.857-1000] P-5760       T-4740  I BROKER  0: (2248)  Begin normal shutdown 
[2026/02/25@03:33:30.177-1000] P-4640       T-3512  I PASOESSN6: (453)   Logout by SYSTEM on AS-10. 
[2026/02/25@03:33:30.177-1000] P-4640       T-1956  I PASOESSN5: (453)   Logout by SYSTEM on AS-ResourceMgr. 
[2026/02/25@03:33:30.858-1000] P-5760       T-4740  I BROKER  0: (2263)  Resending shutdown request to 0 user(s). 
[2026/02/25@03:33:34.883-1000] P-5760       T-4740  I BROKER  0: (15109) At Database close the number of live transactions is 0. 
[2026/02/25@03:33:34.883-1000] P-5760       T-4740  I BROKER  0: (15743) Before Image Log Completion at Block 28726 Offset 4392. 
[2026/02/25@03:33:34.884-1000] P-5760       T-4740  I BROKER  0: (453)   Logout by Administrator on CON:. 
[2026/02/25@03:33:34.885-1000] P-5760       T-4740  I BROKER   : (16869) Removed shared memory with segment_id: 44236800 
[2026/02/25@03:33:34.886-1000] P-5760       T-4740  I BROKER   : (334)   Multi-user session end. 
[2026/02/25@03:33:35.732-1000] P-5920       T-6012  I SHUT    7: (453)   Logout by Administrator on CON:. 

It looks like the database sends a message to the client that it has been shut down and the client process terminates, but I am fairly certain that this behavior is possible only with the shared memory mode.

Looks like in the appserver case the signal to terminate the program execution is much faster and is not resend, not sure if this is relevant.

In our past testing, the STOP condition in disconnect cases can be caught, but only by a block that is outside of and enclosing the block in which the CONNECT statement was made. If you are testing with connections that exist before a session starts running code, then you will miss this case.

Please make sure that you explicitly use a CONNECT statement to establish the database connection. Do it in a separate procedure within a DO ON STOP UNDO, LEAVE: block. This should show that code after that block will execute.

Maybe my tests are wrong, I can't seem to register the STOP
connect.p

CONNECT VALUE("-db C:\DB\tstcasesdb").

DO ON STOP UNDO, LEAVE:
   run main.p.
end.

message "done connect".

main.p

DO ON STOP UNDO, LEAVE:
    for each book transaction:
        pause.
    end.
end.
message "done main".

Messages are not reached.

Do nested blocks like this:

do on error undo, leave:
  do on stop undo, leave:
     ... other conditions
     message "STOP not raised".
  end.
  message "ERROR not raised".
end.

Keep in mind that 4GL may raise some un-catchable condition which just terminates the client (or appserver agent), after logging something or showing the error.

The CONNECT must be in a separate .p. Call the <connect_program>.p from within the 4GL code block that tries to catch the STOP.

Greg Shah wrote:

The CONNECT must be in a separate .p. Call the <connect_program>.p from within the 4GL code block that tries to catch the STOP.

I believe this is the case in #11161-21.

I came back to 11161a. Without actually running the new code, I am not sure I fully understand the changes, so this is more like a discussion.

On JdbcDataSource:
The current solution is to let the data source break in case of failure of acquiring a new connection and close and reopen it fresh 3 times before giving up and reporting a PersistenceException(DBUtils.JDBC_CONNECTION_ERROR). Indeed, there is no temporisation to deal with a temporary database outages or network 'hiccup'.

• instead of specific ResourcePoolException check, the InterruptedException is used. Why (and who) is expected the interrupt the thread is the acquiring of a new connection is not possible?
• why is the current thread marked as interrupted? Since it caught InterruptedException, the flag should be already set and interrupted() method is not called.
• the interruption of timeout can be caused by different reasons, it does not seem logical to me to throw new PersistenceException(DBUtils.JDBC_CONNECTION_ERROR) because the sleep was not able to finish cleanly.

Note: the lastException local variable is not used and can be dropped.

On TenantManager:
I think using the JdbcDataSource for obtaining the connection in readDomains() is the right choice. The problem I see is that the connection is not returned to pool by closing it. Or by using try with resources as in original code.

In getConnection() method, things are more complicated. This method is called in two distinct scenarios: with and without auto-commit (selected by the trans parameter). We cannot alter the connection from normal pool because we will interfere with transaction implementation (which must not auto-commit). Unlike the above connection factory, this has a temporisation and bit different management. The exceptions caught here will not propagate to normal user/ABL code, but rather to REST responses. This needs to be tested individually.

Instead of PersistenceException, shouldn't we be using a StopConditionException (e.g. a legacy-compatible condition). In the 4GL, when the database connection is lost, the code gets a STOP condition. This seems like the best fit.