Problem Reported: ODBC request is getting cancelled for a published view with below error in logs.

ERROR [DBChannel-359] 2017-06-21 14:32:04.372 +0000 ServerChannelConnection - java.io.IOException: Exceeded connect timeout of 30 seconds when waiting to write bytes to the socket.  Closing the connection.[dbchannel-1908511]

        at cs.jdbc.driver.protocol.WireEncoder.writeEverythingToSocket(WireEncoder.java:401)

        at cs.jdbc.driver.protocol.WireEncoder.sendEverything(WireEncoder.java:355)

        at cs.jdbc.driver.protocol.WireEncoder.writeResponse(WireEncoder.java:486)

        at com.compositesw.server.dbchannel.CsResultProcessor.send(CsResultProcessor.java:165)

        at com.compositesw.server.dbchannel.Request.fetch(Request.java:44)

        at com.compositesw.server.dbchannel.ServerCommand.getrows(ServerCommand.java:874)

        at com.compositesw.server.dbchannel.ServerCommand.dispatchCommand(ServerCommand.java:175)

        at com.compositesw.server.dbchannel.ServerCommand.run(ServerCommand.java:86)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 

 at java.lang.Thread.run(Thread.java:745)

ERROR [DBChannel-359] 2017-06-21 14:32:04.373 +0000 ServerChannelConnection - Failed to write result for "getrows"

java.io.IOException: Exceeded connect timeout of 30 seconds when waiting to write bytes to the socket.  Closing the connection.[dbchannel-1908511]

        at cs.jdbc.driver.protocol.WireEncoder.writeEverythingToSocket(WireEncoder.java:401)

        at cs.jdbc.driver.protocol.WireEncoder.sendEverything(WireEncoder.java:355)

        at cs.jdbc.driver.protocol.WireEncoder.writeResponse(WireEncoder.java:486)

        at com.compositesw.server.dbchannel.CsResultProcessor.send(CsResultProcessor.java:165)

        at com.compositesw.server.dbchannel.Request.fetch(Request.java:44)

        at com.compositesw.server.dbchannel.ServerCommand.getrows(ServerCommand.java:874)

        at com.compositesw.server.dbchannel.ServerCommand.dispatchCommand(ServerCommand.java:175)

        at com.compositesw.server.dbchannel.ServerCommand.run(ServerCommand.java:86)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

Troubleshooting:

During the troubleshooting we have identified that customer using below configuration items and we suspected, “Default Bytes to Fetch” configured value is  too large, that was causing the SEND to take too long, an exception was thrown, and the query would terminate.

ERROR [DBChannel-9] 2017-09-26 20:45:21.914 +0000 ServerChannelConnection - java.io.IOException: Exceeded connect timeout of 30 seconds when waiting to write bytes to the socket.  Closing the connection.[dbchannel-1908511]

“Default Bytes to Fetch” and “Default Rows to Fetch” adjust the size of the batch that is gathered before sending it to the client. Whichever value is hit first determines the batch size. That batch is sent to the client in chunks until it’s done, and then the next batch gathered and sent. If the batch size is too large, it takes too long for the batch to be sent, and the query will terminate – We saw this.

From the debug logs, we can see that most of the time is spent sending the data to the client.

Current Configuring Values Customer using :

•  DbChannel Queue Size = 10

•  Default Bytes to Fetch: 800000000

•  Default Rows to Fetch: 500

 

Fix/Solution: To fix the issue, we tried to adjust the “Default Bytes/Rows to Fetch” up or down to see if it speeds this query up. So we changed values  “Default Bytes to Fetch”: 800000000 (customer using now) to the product default value. 131072.

The connection is successfully and query passed and get the successful results.

We no more see the Connection reset errors.

Please note:  However remember that this is a global setting and will affect all queries. You can set them to what works best for you, but try the lower values to see if they work better.

Default Rows to Fetch: 4000

Default Bytes to Fetch: 8000000