I’m using JDBC Adapter 6.0.2 to connect to a DB/2 database on an AS/400 with these settings:
Transaction Type: NO TRANSACTION
Datasource Class: com.ibm.as400.access.AS400JDBCDataSource
databaseName: blank
portNumber: blank
networkProtocol: blank
Other Properties: libraries=<library>
Services I create using this connection work fine. When I try to use anything the next day, however, I get this error:
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service EKANBAN.send:lookupOrder.
[ADA.1.316] Cannot execute the SQL statement “<sql>”. "
(HY000/-99999) Internal driver error.(Connection reset by peer: socket closed)"
Internal driver error.(Connection reset by peer: socket closed)
If I disable and re-enable the connection in wM Administrator, it works for the rest of the day.
I suspect something is happening to the connection during the AS/400’s overnight processing. Is there any way I can programmatically disable and re-enable the connection, so I don’t have to do it manually?
We too are facing similar problem on the JDBC Adapter,the difference being we have Oracle 8i data source.We have temporarily solved the problem by removing the connection pooling on the adapter.The latest proposal from tech support is to apply SP2 on the Integration server and use the following settings:
watt.server.db.blocktimeout=20000 (fix28 in SP2)
watt.server.db.maintainminimum=true (fix28 in SP2)
watt.server.keepAliveTimeout=30000.
We have not yet moved to SP2 and are still testing SP1.
Are we sure that the database is not timing out the connection ? This is what would normally happen if we leave the services unused for an extended period of time. The trick is then to keep sending keep to alive signals at regular intervals.
Are we sure that the database is not timing out the connection ? This is what would normally happen if we leave the services unused for an extended period of time. The trick is then to keep sending keep-alive signals at regular intervals.
Yes the database connection might have timed out or the firewall might have dropped the DB Connection.We would have expected the Adapter which implements the connection pooling to handle these scenarios.In the absence I would agree with Ashuthosh that we need to send keep-alive signals at regular intervals,perhaps a generic flow service that executes a simple SQL such as “select 1 from dual” . The behaviour we have noticed with JDBC Adapter is that once you receive a Connection reset by peer: socket closed, merely re-excecuting the service again is enough for the JDBC Adapter to restablish the connection and execute the DB call.So even if the “ping” routine fails the next call to DB should take care of the problem.
I haven’t had DB connection timeouts impact our webMethods servers (yet), but they did impact another application server we run. That problem was solved by using this Oracle setting:
(ENABLE=BROKEN)
This enables keepalive on TCP transports which support
this. Keepalive allows the caller to detect a dead remote
server, although typically it will take 2 hours or more to
notice - O/S TCP configurables (which vary by platform)
define the actual keepalive timing details.
This line has to be inserted in the DESCRIPTION clause.
Eg: xxx = (DESCRIPTION=(ENABLE=BROKEN)(ADDRESS= ....)... )
With this in place KeepAlive packets will be sent on an
idle connection after the OS configured interval.
==================================================
Please correct me if I am wrong but I believe that the setting ENABLE=BROKEN did not fix your problem and instead the time out scenario simply didn’t resurface! Lets take the case when OS of the remote node has not sent back a keep-alive signal to the caller program (saying that the DB server is unavailable) and meanwhile the caller sends in a request for another transaction. The caller would simply keep waiting for the request to time out as it does not have any means to know that the DB server is unavailable.
I also found some data on the net validating this theory -
8.1.2.1 Database Machine Crash
The machine hosting a database instance was powered off to simulate this behavior.
Oracle OCI Driver
Non-XA - JDBC client hung for 10 minutes at the TCP/IP level, after which a connection was established to the second node in the Oracle RAC.
XA - JDBC client hung for 10 minutes at the TCP/IP level, after which no connection was established to the second Oracle RAC node. When “enable=broken” was specified in the tnsnames.ora entry, the client only hung for 3 minutes, but was still not able to connect to the second node.
This leads me to conclude that the only other alternative left with us to save the JDBC connections from a timeout is to keep sending a keep-alive signal from the caller program.
> I believe that the setting ENABLE=BROKEN did not
> fix your problem and instead the time out
> scenario simply didn’t resurface!
I don’t have firsthand experience with this setting - it was implemented by another team. However the people who used this setting are quite categoric - their servers beset with broken DB connections - this problem went away the moment they applied this setting. I had previously discussed this problem (it occured for many weeks) at length with them and was quite interested when this solution (proposed by our DBAs) fixed it.
Note, this is a client-side setting for the Oracle OCI driver used by a different (non-Java) application - it may not apply in this case. However, the basic architecture seems similar - an application opens multiple connections to the database, which are rudely closed by the firewall after a period of inactivity is detected.
The BEA website you pointed to talks about downtime recovery for Oracle cluster - I’m not sure how it disproves the use of this setting to prevent termination of DB connections.
> This leads me to conclude that the only other alternative
> left with us to save the JDBC connections from a timeout
> is to keep sending a keep-alive signal from the caller program.
One problem with this alternative is when multiple database connections are used (not just one connection), how can the caller program know which DB connection it’s “keep-alive” SQL queries (eg: “select 1 from dual”) are going over? Instead, the ‘enable=broken’ setting apparently does what it does at a lower level - it enables TCP/IP level keep-alives (a socket setting for the TCP/IP connection). The TCP/IP keep-alives stops the firewall killing off the connection.
Anyway, this is free (and very much secondhand) advice. I just hope it helps someone.
I am not really sure about the enable=broken part as I’ve never implemented that . However, here is how we can address the other issue -
>>One problem with this alternative is when multiple database >>connections are used (not just one connection), how can the caller >>program know which DB connection it’s “keep-alive” SQL queries >>(eg: “select 1 from dual”) are going over?
We would need to write a multithreaded program and in the run method (am a java guy) keep a check on the number of connection objects (from the connection pool) that has not been used for a period of time. Keep sending the keep-alive signals for these connection objects and things should be fine. This is a very elementary way of solving this problem and we can enhance it as per our needs.
Eric and others, we have the same problem about losing our adapter connections. We have a JDBC (6.0.2) connection to DB2 on the AS/400. Is there any way to automate the adapter re-connection ?
We are able to use webMethods admin (web) console to re-enable the connection but we would rather have this occur auto-magically.
I created a automatic scheduled service to turn on/off the connection
during night once or earlier monring. Because the AS400 driver will stale if the DB2 is down for some reason.
Here is the service I used, you need to create one for stop, one of start.
I am facing a similar issue, everyday morning the Oracle database is bounced for taking a backup and the jdbc adapter cannot reconnect to the database once it is up. I’ve to run an adapter service/ reload the adapter package to restore the connection.
Shouldn’t the JDBC adapter conncetion automatically reconnect once the database is up ?
Hi Mike/Samrat/Thota/Wayne;
I know this is quite late, but looking at so many people asking for some service to reset JDBC Adapter Connections once DB is down; I have uploaded this service in a package which resets JDBC Adapter Connections. Simply run this service or put it on scheduler as per your needs.
If we set Minimum Pool Size=0 in the JDBC Adatper connection settings amd Adapter connnections will remain enabled.Once the DB is up the transactions will be processed normally.
This is what we have done in all the Adapter connections and we dont have any errors.
Anyways the Saurabhs Package is very useful who ever facing the problems.
Hi RMG,
This is cool/easy solution. I will try to put Minimum Pool Size = 0, and see if the error occurs.
The only reason I didnt have this setting set to 0 was due to (perceived) overhead of creating a new connection. But having this setting as zero is much better solution than scheduling the svc, at least in our env.
hi guyz
i m a newbie to webMethods and i tried to have a adapter connection i succeeded in that but unintentionally the package was deleted.when i recoverd the packege i no more have the connction i have looked in the discussion forums for help but i could not get.my latest error message is
Error encountered
[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource MYEXAMPLES:bhushan.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
Io exception: The Network Adapter could not establish the connection
can any body plz help me out to fix this it will be a gr8 help
Did you check whether the Adapter connection is Enabled or not.
Check in the JDBC Adapter in Adminisitrator to see whether it’s enabled or not.
If not Enable and try again.
Hi
Sorry didn’t checkout the question completely.
Verify that you have correctly entered all the parameters for a JDBC Connection and also verify whether Classes12.zip is placed in the Is/bin/Jars .
I just hope it helps someone.
. However, here is how we can address the other issue -