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?

Thanks!

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.

My $0.02 worth.

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.

My $0.02 worth.

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.

Hello:

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:

==================================================
TNSNAMES.ORA:

(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 -

[url=“http://dev2dev.bea.com/products/wlserver81/whitepapers/wls_bea_hp.jsp”]http://dev2dev.bea.com/products/wlserver81/whitepapers/wls_bea_hp.jsp[/url]

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.

My thoughts.

Hey Ashutosh:

> 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.

Sonam,

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.

Hope that helps.

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.

Any thoughts ?

Regards,

Wayne

Hi Wayne,

webMethods havn’t given any re-enabling feature for the adapter connections,even we are facing the same problems with the Adapter.

Cheers,
Thota

All,

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.

wm.art.admin.connection:setResourceState

good luck.

Guys,

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 ?

thanks

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.

HTH,

• Saurabh.

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.

Regards,

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.

Will keep you posted.

Thanks

• Saurabh.

Saurabh,

Thanks for the response,let us know the positive/negative feedback after testing this change.

Regards,

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

thanx in advance
bhushan

Hi Bhushan,

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.

Thanks,
Somu

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 .

hi mr reddy thanx for the response

the adapter connection is not getting enabled i have tried for several times and the above error message in my earlier post is what i get when i tried to enable the connection in admin.these are my settings

MYEXAMPLES:bhushan Details
Connection Type JDBC Adapter Connection
Package Name MYEXAMPLES
Connection Properties
Transaction Type LOACL_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName 192.168.1.69
user scott
password ******
databaseName bhushan
portNumber 1521
networkProtocol <blank>
Other Properties driverType=thin
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 1
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10

mr reddy

yes i do have the classes12.zip in the path specified i have been trying it regularly by turning down the server and restarting.but this is the error i keep on getting

thanx

Hi
Looking at the entries you have sent, the database name is “bhushan”.
Have you customized your database . Normally it’s “ORCL” if you are connecting to Oracle database.

mr reddy
yes i tried using the orcl and infact i later customised my database .

Hi Bhushan,

Tried to create a new connection at my workplace .I too had the same errors surfacing initially but then i deleted the conection and then created a new one reentering the parameters.
Try once afresh to create a new connection.

hi reddy

i too tried it but the same result but failed network adapter

thanx for the response

I’m looking to fill a position for a Lead Integration Architect, ideally someone who has knowledge of webMethods and/or other EAI tools. The position is permanent and can either be based in Dublin, Ireland or N Carolina. If you are interested or know someone who may be, please get in touch. rlevene@glueltd.com

I am looking for a webMethods administrator for a big multinational in the fast moving consumer goods branch. The position is based in The Netherlands. The organisation is in the middle of restructuring their IT environment and use webMethods to integrate the systems. It regards a permanent position. If you are interested or you know someone who could be, please contact me. bas.van.der.muren@eclectic.nl

Dear All,

We are facing a severe problem with database connection. Please see below the errors (server log message)

<<
[SCC.0121.0035E] resource delisted with TMFAIL flag, transaction marked for rollback. xid = <server_name>/1119537951421 rxid = [FormatId=45744, GlobalId=<server_name>/1119537951421, BranchQual=1]

[ART.0116.3001E] Adapter Runtime (Notification): Notification error during execution of PU006_ERPM_RCM.resourceOrg.jdbc.core:notifyResourceOrgDetails. Error: [ADA.1.316] Cannot execute the SQL statement “SELECT t1.WM_ROWID,t1.OPERATION,t1.ORG_HISTORY_ID,t1.ORG_UNIT_ID,t1.ORG_UNIT_NAME,t1.PARENT_ORG_UNIT_ID,t1.CREATE_DATE,t1.WM_SENDTIME FROM WEBMETHODS_RCM.WMB_ORG_HISTORY t1”. "
[12266]Closed Connection
12265 Closed Connection"
>>

-Version of IS >>6.1

• Version of JDBC Adapter >> 6.0.3

• Version of Database >> Oracle-8.1.7 & JDBC-Driver >> Oracle JDBC Thin Driver

I know to disable and re-enable the connection, solve this issue but again after some time it shows same error (“Closed Connection”). I would like to solve this permanently and also like to know the root cause of this error.

I will be grateful if anybody can help in this regards.Pls let me if you require any info.

Thanx in advance,

Joydeep

Joydeep,

In your JDBC connection are you using connection pool ? If yes then change the minimum connection pool size to 0. Let me know if it solves your problem or not

Hi Here,

I am also encountering connection closed problem. However, I would like to know how would the problem be resolved by setting minimum connection pool size to 0??

Setting the minimum connection to 0 avoids “stale” connections. That is, connections in the IS JDBC connection pool that think they are still connected to the DB but are not. The symptoms of this vary depending upon timeout settings, the nature of the DB operation that uses the stale connection, etc. Sometimes you’ll see quick errors, other times the thread/server will appear to hang.

There is overhead associated with establishing a connection, but connections are tossed out of the pool only after they expire, as controlled by the “Idle timeout” setting. If your IS is consistently busy, the connections remain. Typically, stale connections are more of a performance/uptime issue than is the time required to establish those connections.

In virtually every situation I’ve encountered where dropped DB connections have been an issue, setting the min connections to 0 resolves them. And performance has not been an issue. IMO, there really is no reason at all to have min connections set to anything other than 0.

Doesn’t the problem with stale connections go away when you use the Oracle ojdbc14.jar file from June 2005? Do you have to set the connections to zero if you have the latest jdbc driver? I think not.
I also think the new Microsoft JDBC driver also solves this problem.

Our testing shows that the problem of stale connections goes away with the new Oracle driver. Has anybody else found this problem to continue with the updated driver ?

Wayne, just set you min value in connection pooling to 0. That way the pool manager for adapter will shutdown all connections after a while when there is no traffic or when they expire. By doing this when yr DB comes up again., it should reconnect automatically.

We have uses this setting on almost all our JDBC adapters and we do not need to stop and start adapter connections every night.
Hope this helps.

ER

Guys,

I have not personally implemented this solution, but I have recently heard that setting the minimum number of connections to 0 (zero) may help with this problem. Theoretically, it will force the pool to be refreshed.

Again, I have not personally verified this solution, but I figured it’s something you could try.

• Percio

yes setting minimum number of connections to 0,will resolve the problem when ever DB goes down for maintainence etc…

Guys,

I apologize for the redundant post.

When I clicked on the link to review this thread today, I didn’t realize it was 4 pages long. I only saw the first page and I didn’t see that the whole “set the minimum # of connections to zero” conversation had already taken place.

• Percio

An updated driver may indeed hide the stale connections. IMO, one still shouldn’t set the minimum connections to anything other than 0. While one might think its useful to keep one or more connections established at all times, it begs the question of what is the benefit of doing so? The best that could be gained is shaving a few seconds from the initial transaction while the connection is established, which seems to be of limited value IMO.

You could use following extended setting which will refresh the database connection in your case.

watt.adapter.JDBC.database.fatalErrors=+17002,08S01,17410,28,17416,1012,40003,1033,-601,1089,-30081,1092,-79716,3114,-99999,12571,HY000,17001,JZ0C0