DS v9 -- DB2 Connection Closed error
Posted: Tue Oct 28, 2014 7:21 am
I'm fairly convinced this isn't a DataStage issue, but ... the job that runs fine in v8 is having issues in v9.
There's a job with a fairly complex and heavy query that runs well in v8, but is encountering a "DB2 connection closed" error in v9. The text of the error is: CLI0106E Connection is closed. SQLSTATE=08003 SQL30081N A communication error has been detected. Communication function detecting the error: "recv". Protocol specific error codes: "78", "*", "*"
Doing some research -- the suggestions with a "78" are to look at modifying any of the following: DB2TCP_CLIENT_CONTIMEOUT, QueryTimeoutInterval in db2cli.ini, or the network may be slow and we need to adjust tcp_keepinit.
I verified that all of these settings are the same on our v8 & v9 servers, so we haven't missed a setup step on v9 that we did previously on v8. I have suggested to the developer that the job needs to be modified to use v9 DB2 stages, to see if that improves the situation. I suppose another answer might be to split the job up if possible, so that the SQL query isn't nearly as complex.
The communication taking place has also been shown to have network issues from our DataStage server in Rochester NY to Boulder CO. I actually noticed that pings from the DS server to the DB2 DB will spike sporadically, sometimes taking as much as 12k ms for 64 bytes, then dropping back down to 60 ms. So, the references I'm finding that seem to point to network issues appear to align.
http://knowledgebase.progress.com/artic ... icle/20017
http://www-01.ibm.com/support/docview.w ... wg21164785
As I said, I'm not sure this can be solved here -- just curious if anyone else has noticed this behavior when migrating jobs from v8 to v9, and if you found a solution / workaround?
There's a job with a fairly complex and heavy query that runs well in v8, but is encountering a "DB2 connection closed" error in v9. The text of the error is: CLI0106E Connection is closed. SQLSTATE=08003 SQL30081N A communication error has been detected. Communication function detecting the error: "recv". Protocol specific error codes: "78", "*", "*"
Doing some research -- the suggestions with a "78" are to look at modifying any of the following: DB2TCP_CLIENT_CONTIMEOUT, QueryTimeoutInterval in db2cli.ini, or the network may be slow and we need to adjust tcp_keepinit.
I verified that all of these settings are the same on our v8 & v9 servers, so we haven't missed a setup step on v9 that we did previously on v8. I have suggested to the developer that the job needs to be modified to use v9 DB2 stages, to see if that improves the situation. I suppose another answer might be to split the job up if possible, so that the SQL query isn't nearly as complex.
The communication taking place has also been shown to have network issues from our DataStage server in Rochester NY to Boulder CO. I actually noticed that pings from the DS server to the DB2 DB will spike sporadically, sometimes taking as much as 12k ms for 64 bytes, then dropping back down to 60 ms. So, the references I'm finding that seem to point to network issues appear to align.
http://knowledgebase.progress.com/artic ... icle/20017
http://www-01.ibm.com/support/docview.w ... wg21164785
As I said, I'm not sure this can be solved here -- just curious if anyone else has noticed this behavior when migrating jobs from v8 to v9, and if you found a solution / workaround?