Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
As anyone looked at the diaglogs for the database while this error was occuring? Usually if there is an error, that is one of the first places I look, in case there is something going on the database. The database can be "healthy" and still have some issues under the surface that need looking into. Also, what version of DB2 (and what OS) are you running. And what version of DB2 drivers are you running? –  Chris Aldrich Aug 7 '13 at 19:22
    
Note: I found a similar issue on SO that happens to have an explaination and an accepted answer: stackoverflow.com/questions/13976063/… –  Chris Aldrich Aug 7 '13 at 19:24
1  
@Chris FYI we typically don't migrate things that old (mostly because of the rep that gets lost in the transfer), but between sites it wouldn't be a terrible thing to copy some of the answer over here. I'd start by asking the author to come over here and answer here as well. –  Aaron Bertrand Aug 8 '13 at 0:46

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.