Hello, So I’m using Integration Server 10.3, and Postgresql jdbc sometimes giving this error on Production Environment, but not on Development
[ADA.1.316] Cannot execute the SQL statement "
(08003/0) This connection has been closed."
This connection has been closed.

the full error that i get from WMERROR is this

2021-09-28T19:08:41.04Z         2021-09-28T19:08:41.04Z         [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service mandiri.sams.adapter.bulk:getFtReqHdr.
[ADA.1.316] Cannot execute the SQL statement "SELECT   t1.batchid,t1.trxheaderid,t1.sp2d_filename,t1.spn_status,t1.spn_priority,t1.filename,t1.total_record,t1.total_amount,t1.process_status,t1.process_description,t1.inq_va_flag,t1.trx_ftbulk_flag,t1.trx_va_flag,t1.insert_date,t1.channel_id  FROM public.sams_bulk_ft_req_hdr t1 WHERE (t1.process_status = ?) ". "
(08003/0) This connection has been closed."
This connection has been closed.
Caused by: com.wm.adk.error.AdapterServiceException: [ADA.1.316] Cannot execute the SQL statement "SELECT   t1.batchid,t1.trxheaderid,t1.sp2d_filename,t1.spn_status,t1.spn_priority,t1.filename,t1.total_record,t1.total_amount,t1.process_status,t1.process_description,t1.inq_va_flag,t1.trx_ftbulk_flag,t1.trx_va_flag,t1.insert_date,t1.channel_id  FROM public.sams_bulk_ft_req_hdr t1 WHERE (t1.process_status = ?) ". "
(08003/0) This connection has be...<truncated>org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:786)
at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1604)
at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:410)
at com.wm.adapter.wmjdbc.services.Select.execute(Select.java:184)
at com.wm.adk.cci.interaction.WmInteraction.execute(WmInteraction.java:76)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode.java:362)
at com.wm.pkg.art.ns.ARTNSService.baseInvoke(ARTNSService.java:53)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java:751)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(ReservationProcessor.java:46)
at com.wm.app.b2b.server.invoke.StatisticsProcessor.process(StatisticsProcessor.java:54)
at com.wm.app.b2b.server.invoke.ServiceCompletionImpl.process(ServiceCompletionImpl.java:250)
at com.wm.app.b2b.server.invoke.ValidateProcessor.process(ValidateProcessor.java:49)
at com.wm.app.b2b.server.invoke.PipelineProcessor.process(PipelineProcessor.java:171)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:299)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:34)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:399)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:629)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:443)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:401)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:253)
at com.wm.app.b2b.server.BaseService.invoke(BaseService.java:236)
at com.wm.lang.flow.FlowInvoke.invoke(FlowInvoke.java:267)
at com.wm.lang.flow.FlowState.invokeNode(FlowState.java:649)
at com.wm.lang.flow.FlowState.step(FlowState.java:515)
at com.wm.lang.flow.FlowState.invoke(FlowState.java:484)
at com.wm.app.b2b.serve...<truncated>           

Need help for this issue, maybe we can discuss for the root cause and solution, Thanks!

Below are the image of the adapter settings
image|690x94

First thing, I would give a non zero number for the minPoolsize if possible.
Also is your request very long running ?

You might want to play with the pool parameters, expire timeout and heartbeat ?
Either the request is taking much longer on your production box and causing the server to think that the connection is no longer responsive and hence closing it, or perhaps you have different setting is production concerning how long a connection can live for. Perhaps in your production environment connections are being automatically closed after a certain interval, in which case the heartbeat value may be of use.

I would try and first determine if there are differences in the DB setup between dev and prod.
regards,
John.

Hello, Thanks for the reply, based on what i read from other case min Pool Size is best at zero, and select query can be a long running request? Any else causing connection close?

My point wasn’t whether long running queries are possible. I am trying to determine the differences between your dev environment and production. Will the query be slower in production compared to dev i.e. due to data volume, large indexes etc?

Does the production environment have different settings to dev, i.e. session timeout, transaction time limits etc.

The best way to resolve this issue is to determine what the difference, then you will be able to identify more easily what needs to be tuned.
regards,
John.

Hi John,

esp. for external sources it is recommended to give a min pool size = 0 to avoid stale connections remaining in the pool.

When min pool size is 1 or greater there is the possibility that this number of stale connections can remain in the pool blocking all further requests which would use connections from this pool.

Regards,
Holger

Hi Koh

The error "(08003/0) This connection has been closed " means that the connection has been closed. This could be due to variety of external factors, but primarily due

1. Database server : Database server might have closed the connection handle, you can start investigation from the Database and figure it out. There can be many reasons here as well, one such could be max client connections has reached.
2. Firewall : Sometimes firewall as well closes the outbound connections, which are idle, depending on the configuration.

Other reasons could be Network, Analyzing tcp dump might help here. But i would recommend , right place to start the investigation is from Database server side.

Hope it helps.

Thanks
Nagasrikrishna

All right thanks for the input, we will start investigating on the Database Side first and try to compare the settings between DEV and Production, thanks Nagasrikrishna and John

Hi Vincent, And yes most likely such kind of these issues could be happening due to stale connections (min=1/max)/DB layer performance hiccups (unplanned)…

HTH,
RMG

Hi rmg, do you know how to check whether it has a stale connection between the Integration Server and the database? what can i do to define the rootcause

Isn’t the heartbeat interval supposed to resolve stale connections hanging around ?
However, I do agree in reflection. The only time where I have seen minConnections being 0 as an issue, was with a project where they had unrealistic latency requirements for an end to end integration that had to complete in less than 1.5 seconds or else it was considered a failure. In addition it was an asynchronous integration

thanks,
John.

Hello sorry for the late updates, so we tried to compare the settings/parameters between DEV and Production Database, we assume that production have alot of traffic so we increased the max connection to 2x, so far there is no connection closed issue but there is another issue that looked like this.

[ADA.1.204] Cannot connect to the database with DataSource class "org.postgresql.ds.PGSimpleDataSource".
The connection attempt failed.,exParmcnt=1,exParm=mandiri.sams.connections:SAMSNoTrx,date=Fri Oct 08 15:15:41 WIB 2021,sessionId=vamsCore,sessionName=10.0.96.46,sessionId=3e26bd7604314739a450bf2b3804c183,serviceCallStack=[Ljava.lang.String;@236506c7,reason=[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource mandiri.sams.connections:SAMSNoTrx.
[ADA.1.204] Cannot connect to the database with DataSource class "org.postgresql.ds.PGSimpleDataSource".
The connection attempt failed.,exceptionClass=com.wm.pkg.art.error.DetailedSystemException,stack=com.wm.adk.error.AdapterException: [ADA.1.204] Cannot connect to the database with DataSource class "org.postgresql.ds.PGSimpleDataSource".
The connection attempt failed.
at com.wm.adk.WmAdapter.createAdapterException(WmAdapter.java:186)
at com.wm.adapter.wmjdbc.connection.ConnectionInfo.handleSQLException(ConnectionInfo.java:756)
at com.wm.adapter.wmjdbc.connection.ConnectionInfo.handleSQLException(ConnectionInfo.java:764)
at com.wm.adapter.wmjdbc.connection.JDBCConnection.initializeConnection(JDBCConnection.java:388)
at com.wm.adk.connection.WmManagedConnectionFactory.createManagedConnection(WmManagedConnectionFactory.java:210)
at com.wm.app.b2b.server.jca.WmConnectionPool.createConnection(WmConnectionPool.java:1163)
at com.wm.app.b2b.server.jca.WmConnectionPool.getAvailConnection(WmConnectionPool.java:1094)
at com.wm.app.b2b.server.jca.WmConnectionPool.getConnection(WmConnectionPool.java:541)
at com.wm.app.b2b.server.jca.WmConnectionManager.allocateManagedConnection(W...<truncated>       

Any idea about this?

Hi Vincent,

might be that you are now exceeding the maximum number of allowed sessions on the database.
You might need to increase this value on the database side.

Regards,
Holger

Dear Holger,
We tried to increase the max connection for the postgres Database, isn’t max connection is the same with max session?

Hi Vincent,

no, these are different values.
One is the max munber which the database is willing to accept, the other one is the value how many connections the pool can persist, before issuing an out-of-connections, when max+1 connections are requested in parallel.

When hosting several schemas on the database there can be several pools connecting to the same database but different users/schemas whose sum of max connections should not exceed the total number of sessions the database can accept.

As I am never have worked with PostgeSQL, I am not sure if it is possible to change the max number of sessions.
For Oracle this is possible with an ALTER-Statement, which needs to be executed as SYSDBA.

Regards,
Holger

We did try to increase the max session and connection on the postgresDB but still getting the same error message but no more closed connection

[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.204] Cannot connect to the database with DataSource class "org.postgresql.ds.PGSimpleDataSource".
The connection attempt failed.

[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource mandiri.sams.connections:SAMSBulkNoTrx.
[ADA.1.204] Cannot connect to the database with DataSource class "org.postgresql.ds.PGSimpleDataSource".
The connection attempt failed.

also i found this on the server.log what does this mean? is this making an exception?

2021-10-27 10:33:30 WIB [ISS.0015.0005D] Invoking service mandiri.sams.adapter.log:insertBulkFileLog.
2021-10-27 10:33:31 WIB [ISS.0096.0023D] Connection pool size is 5, Available pool size is 4 for pool ISInternal:Embedded Database Pool. The Maximum connection is 50
2021-10-27 10:33:31 WIB [ISS.0096.0023D] Connection pool size is 5, Available pool size is 3 for pool ISInternal:Embedded Database Pool. The Maximum connection is 50
2021-10-27 10:33:31 WIB [ISS.0096.0030D] Released the connection to pool ISInternal:Embedded Database Pool. Connection pool size is 5. Available pool size is 4.
2021-10-27 10:33:31 WIB [ISS.0096.0030D] Released the connection to pool ISInternal:Embedded Database Pool. Connection pool size is 5. Available pool size is 5.
2021-10-27 10:33:31 WIB [ISS.0096.0023D] Connection pool size is 5, Available pool size is 4 for pool ISInternal:Embedded Database Pool. The Maximum connection is 50
2021-10-27 10:33:31 WIB [ISS.0142.0001D] Registered Lifecycle Listener com.wm.app.b2b.server.scheduler.ScheduleDB$ScheduledService@282d304f

Need idea for this, have been occurring quite a time, thanks