DB2 Communication link failure issue I would like to get your valuable suggestions for the below problem.

My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data for a while and after sometime I start seeing the below Error 1.

Error 1:

[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003


Error 2:

[IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003 


Error 1 occurs only once, after that Error 2 occurs repeatedly till I restart the application and establish new connection to the database. It's been confirmed that the database is running healthy and able to accept connections and even the network connections are healthy.

Background on Error 1:

Error "CLI0108E" Suggests that "The connection between the driver and the data source failed during execution of this function."

And "SQLSTATE=40003" suggests that "The statement completion is unknown."

Is it possible that something going wrong in an insert statement actually closes the connection? Or leaves the connection stateless?

This actually breaks my Perl application. What are the best options to handle this situation?
Copied from a StackOverflow answer of mine  This answer assumed C# code, but the error message is directly applicable here as well.

CLI0108E simply means the connection was broken.  From the official IBM documentation of this error code:


  CLI0108E Communication link failure.
  
  Explanation The connection between the driver and the data source failed during execution of this function.
  
  User response Establish a new connection.




The SQLState (40003) indicates that the DB server can't tell whether or not the statement completed successfully.  (Documentation of SQLState messages and their meaning can be found here: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.msg.doc%2Fdoc%2Fr0sttmsg.htm

values in this range:


  
  40001 Deadlock or timeout with automatic rollback occurred. 
  40003 The statement completion is unknown. 
  40504 A system error has caused the unit of work to be rolled back. 
  40506 The current transaction was rolled back because of an SQL error. 
  40507 The current transaction was rolled back as a result of a failure creating an index.
  




CLI0108E could have any number of causes:  Issues in the network, the client or server closing the connection due to a timeout, someone kicking a cable in the computer room, you name it.  I've even seen is in our environment where a virtual server running on a particular VM host got this error because there was a problem with the virtual NIC card, and it was fixed by reinstalling the driver.  The list of possible causes is surprisingly large, but a good Network Admin should be able to help.  (Monitoring packets with a sniffer is a good way to track down the source when all else fails.)

If  you can, it might not hurt to refactor the code to upload smaller sets.  Say you have a 10,000 records, try uploading a thousand at once, ten times to see if that helps.

Another approach I used (once) in a situation where the server was completely unreliable was to just try inserting one record at a time.

pseudo-code: (Assuming a connection object named "connection" and a command object named "cmd" that uses that connection...)

connection.Open()

for each record
   try
      cmd.CommandText  ="whatever changes need to be made"
      cmd.Execute()
   catch(Exception ex)
      if( the exception is a communication link failure)
         connection.Open()
         cmd.Execute()
       else
          handle different errors accordingly
       end if
  end try

next


But that approach has performance issues, is messy, and still could fail of the attempt to reopen the connection fails.  Even though I've used it in a pinch, I don't recommend it long-term.  (I got rid of that approach once the original issue was fixed.)  You're far better off trying to track down the root cause of the broken connections.

In your case, it is possible that the underlying cause may just be that you're trying to upload too much data at once. I doubt it, but it's possible.  Refactoring the code to send the data in smaller chunks would definitely by my first choice were I in your shoes.