Mid Server Connection Caching - Can it be turned off?

[bbarnard]

Mid Server JDBC Connection Caching - Can it be turned off?

Is it possible to turn off Mid Server Connection Caching? I have a situation where I run a scheduled data import daily via JDBC and in my networking environment if traffic is not seen on the firewall for more than 1 hour the firewall will block any attempts to reuse an existing connection and force a new connection be made. When my scheduled data import runs it seems to first attempt to reuse the existing/cached connection. In most environments this seems like it would be a good choice and more efficient but in my situation I would like to turn this off.

Has anyone run into a similar issue or knows if connection caching can be turned off? I see that there is a connection timeout setting on JDBC Data Source records in ServiceNow I have not been able to confirm that setting this to a very low value (ie 1) would effectively have this effect.

Anyone have any experience with this or solved this problem?

Thanks!

[bbarnard]

Through some additional testing I've been able to confirm that using the Connection Timeout setting will force the connection that is opened by the mid server to be closed after the specified time has elapsed. The default value for Connection Timeout is 0, which is "No Timeout." To see this behavior setup a JDBC Data Source and execute it in your ServiceNow Instance. On the MidServer watch the agent log and you will see the debug messages indicating the job has been picked up by the Mid and is being executed. In another terminal run the netstat command and grep for the destination port you specified in your Data Source. In my case this was a MySQL data source so I was able to run "netstat | grep MySql" I could have also used the port number, and if your data source is MSSQL or Oracle you would run this same command but replace MySql with the port number for those databases. Run this command repeatedly and you will see that unless the MidServer is stopped the connection will be left open, and if you execute the data source again later it will attempt to reuse this same connection. Next update your connection timeout to a low value (ie 10) and execute the data source. Running the netstat command again and grepping for the destination port you will see that the connection is opened, now after the data source query has completed run the netstat command again and note the status of the connection. You should see the status updating from a state of Established, to FIN_WAIT, and eventually no longer being listed. Note, to see this you will have to be running netstat repeatedly. In my case I ran it every 10 seconds logging the output to a text file on my server and then reviewed the log to confirm this behavior. I tested this with the latest version of the MidServer software associated with the Berlin release of ServiceNow. (I'll update this post with the build number later).

Once I confirmed this behavior I updated all of our JDBC Data Sources to use a default connection timeout value of 10, and added a default value to the connection timeout dictionary record as I would like our connections to close after the query is done executing by default and only want the MidServer to attempt to reuse connections if I explicitly specify such.

-Bryan

[Ashu7]

Hi,

I am loading data in SN using JDBC data source and scheduled import.

While data load, getting error as "Error during data load, skipping application module creation"

Any idea why this error has been occurred?