JDBC Adapter loses connection overnight

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