The following error showed up in our logs for our Electronic Approval System. Whenever this error hits we get a deadlock on our table (pbba.bbab10t) that causes users to be unable to approve documents. This is a very frustrating error. It has been very tough to track.

The error is as follows:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL30020N Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements: Reason Code "1232". SQLSTATE=58009

1232
The command could not be completed because of a permanent error. In most cases, the server will be in the process of an abend.

SQL30020N
Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements: Reason Code "".
Explanation: A system error occurred that prevented successful execution of the current environment command or SQL statement, as well as any subsequent commands or SQL statements.
The command or statement cannot be processed. The current transaction is rolled back and the application is disconnected from the remote database.
User Response: Record the message number (SQLCODE) and the reason code. Record all error information from the SQLCA, if possible. Attempt to connect the remote database and rerun the application.
If sufficient memory resources exist and the problem continues, invoke the Independent Trace Facility at the operating system command prompt. Refer to the Independent Trace Facility in the Troubleshooting Guide for information on how to use this facility.

The Line of code that was executed before this Exception was thrown is the following:
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.db.DBApprovalController.getCriteriaOrder(DBApprovalController.java(Compiled Code))
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.ApprovalController.isCriterionMatchForSigner(ApprovalController.java(Compiled Code))
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.ApprovalController.createUnitWaitingItems(ApprovalController.java(Compiled Code))
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.ApprovalController.updateWaitingItems(ApprovalController.java(Compiled Code))
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.docvalidation.ValidationPageBean.checkUpdateWaitingItems(ValidationPageBean.java:351)
[3/22/05 14:56:52:391 CST] 9c75565 SystemErr R at webapr.docvalidation.ValidationPageBean.processRequest(ValidationPageBean.java:200)

Later I got the following error log:
[3/22/05 15:23:57:234 CST] 815d565 SystemErr R COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C9008E". SQLSTATE=57033
[3/22/05 15:23:57:234 CST] 815d565 SystemErr R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:261)[3/22/05 15:23:57:234 CST] 815d565 SystemErr R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java(Inlined Compiled Code))[3/22/05 15:23:57:234 CST] 815d565 SystemErr R at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java(Compiled Code))[3/22/05 15:23:57:234 CST] 815d565 SystemErr R at COM.ibm.db2.jdbc.app.DB2Statement.execute2(DB2Statement.java(Compiled Code))[3/22/05 15:23:57:234 CST] 815d565 SystemErr R at COM.ibm.db2.jdbc.app.DB2Statement.executeQuery(DB2Statement.java(Compiled Code))[3/22/05 15:23:57:250 CST] 815d565 SystemErr R at edu.uga.busfin.dfs.db.DBAccess.query(DBAccess.java(Compiled Code))[3/22/05 15:23:57:250 CST] 815d565 SystemErr R at webapr.db.DBApprovalController.getCurrentApprovalGroupLevel(DBApprovalController.java:46)[3/22/05 15:23:57:250 CST] 815d565 SystemErr R at webapr.ApprovalController.updateWaitingItems(ApprovalController.java(Compiled Code))[3/22/05 15:23:57:250 CST] 815d565 SystemErr R at webapr.docvalidation.ValidationPageBean.checkUpdateWaitingItems(ValidationPageBean.java:351)[3/22/05 15:23:57:250 CST] 815d565 SystemErr R at webapr.docvalidation.ValidationPageBean.processRequest(ValidationPageBean.java:200)

...
I feel like the error must be the fact that the following sequence of events happens in this old code...

Snippet from ValidationPageBean:
public void processRequest(Object objReqRes){
Object [] objArr = (Object[])objReqRes;
HttpServletRequest request = (HttpServletRequest)objArr[0];
HttpServletResponse response = (HttpServletResponse)objArr[1];
this.request = request;
context = request.getSession().getServletContext();
con = DBUtil.getSysConnection(context); //ESTABLISHES A CONNECTION
...
// Place all of the logic within a try{} so that we may rollback
// the entire transaction if we get an error in our messages object.
try{ (191)
con.setAutoCommit(false); // sets the auto commit to false

Snippet from ApprovalController:
public boolean updateWaitingItems(Document document) {
this.conn = DBUtil.getSysConnection(context); // ESTABLISHES ITS OWN CONNECTION!!!
this.db = new DBApprovalController(this);
setDebug();
this.document = document;
this.groupLevel = db.getCurrentApprovalGroupLevel(conn, document);

My best guess on this error is that the connection that is set to autoCommit(false) in the ValidationPageBean should be used in the ApprovalController object that the ValidationPageBean creates in its checkUpdateWaitingItems method:

private void checkUpdateWaitingItems(ServletContext context, Document document, String validationCode){
if(document.getDocHead().getHeaderElecApprCode().equals("P")){
if(wasDocumentChanged(document, validationCode)){
this.appCon = new ApprovalController(context, this.user);
this.appCon.setConn(con);
if(!this.appCon.updateWaitingItems(document)){
this.appCon.appendMessages(this.messages);
}
}
}
}

Well, after much investigation I found out what the error was. Seems some legacy code was creating a nice little infinite loop when it was comparing units on a document versus units in our tracking table. So, I completely scrapped the old updateWaitingItems logic in the ApprovalController and replaced it with some testable code that works much faster, is more intuitive, and much easier to maintain.
This issue has been resolved and has been moved into our production environment before another user steps into an infinite loop by making changes to a document and submitting it to the approval system.

- posted by ThinkEddy @ 4:16 PM