Hello,
This information is regarding configuration of Knowledge Exchange using Tomcat as the application server when connecting to SQL databases. To avoid Database connection loss after a long run of the Tomcat service you can make a couple of changes to your server configuration. One way to address this is to add parameters to the KEServer.xml file (as shown in example 1 below) and the other way is to set weekly Tomcat service restarts.
1) So, you should add:
testOnborrow="true" testWhileIdle="true" testOnReturn="true"
See below KEServer.xml as an example:
<?xml version="1.0" encoding="UTF-8"?>
<Context debug="9" docBase="KEServer" path="/KEServer" reloadable="true">
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.apache.commons.dbcp.BasicDataSourceFactory" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/admindb" password="KEserver60" poolPreparedStatements="true" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:sqlserver://servername:1433;databasename=pvj61admin;instance_name=sqlinstance;SelectMethod=cursor" username="pro61user" validationQuery="select 1" testOnborrow="true" testWhileIdle="true" testOnReturn="true"/>
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.apache.commons.dbcp.BasicDataSourceFactory" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/serverdb" password="KEserver60" poolPreparedStatements="true" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:sqlserver://servername:1433;databasename=pvj61user01;instance_name=sqlinstance;SelectMethod=cursor" username="pro61user" validationQuery="select 1" testOnborrow="true" testWhileIdle="true" testOnReturn="true"/>
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.apache.commons.dbcp.BasicDataSourceFactory" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/server1db" password="KEserver60" poolPreparedStatements="true" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:sqlserver://servername:1433;databasename=pvj61user02;instance_name=sqlinstance;SelectMethod=cursor" username="pro61user" validationQuery="select 1" testOnborrow="true" testWhileIdle="true" testOnReturn="true"/>
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.apache.commons.dbcp.BasicDataSourceFactory" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/ReportDb01" password="KEserver60" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:sqlserver://servername:1433;databasename=pvj61Reporting01;instance_name=sqlinstance;SelectMethod=cursor" username="pro61user" validationQuery="select 1" testOnborrow="true" testWhileIdle="true" testOnReturn="true"/>
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.apache.commons.dbcp.BasicDataSourceFactory" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/ReportDb02" password="KEserver60" removeAbandoned="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" url="jdbc:sqlserver://servername:1433;databasename=pvj61Reporting02;instance_name=sqlinstance;SelectMethod=cursor" username="pro61user" validationQuery="select 1" testOnborrow="true" testWhileIdle="true" testOnReturn="true"/>
<Resource auth="Container" mail.from="myAdmin@myCompany.net" mail.pop3.host="pop3.host.com" mail.pop3.socketFactory.class="javax.net.ssl.SSLSocketFactory" mail.smtp.auth="false" mail.smtp.host="smtp.host.com" mail.smtp.socketFactory.class="javax.net.ssl.SSLSocketFactory" mail.smtp.starttls.enable="true" mail.store.protocol="pop3" mail.transport.protocol="smtp" mail.user="user@metastorm.com" name="mail/MailSession" type="javax.mail.Session"/>
</Context>
I have attached a KEServer.xml file that you can keep as a hardcopy example.
Thanks,
Matt Michael
