Java Database Exception Handling

One of the presentations that I did at the IDUG Berlin conference was “Java DB2 Developer Performance Best Practices.”  This speech covers all the interesting Java application designs, coding consideration and database related best practices I have found over the years doing application performance tuning for a wide range of industries.  Interestingly the main questions about Java database exception handling discussed below have arisen several times.

First, how should we handle database exceptions within Java processes?  Java applications need to handle database exceptions just like all the other application programming languages–right after the SQL is executed.  Executing SQL and then testing a SQLCODE for a Java database exception should be done inside the same Java class method.  Too often I find that the Java database exception SQLCODE checking is done outside the method or ignored entirely.  This is really bad for immediately reporting application errors, handling the database exception proactively, and resolving the overall unit-of-work.

Next, what information should be communicated when a Java database exception occurs?  DB2 produces a lot of information when a database exception occurs so the Java application SQLCODE error routine just needs to report it.  The various vendors supply many SQLCODE SQLException routines, the Oracle-Java one is here, the IBM/DB2 one is here and I have created derivative Java database exception reporting routines (shown below) that provide the information necessary for debugging your application.

catch (SQLException sqle) {
// ==
// == DDDD  BBBB    2222                  
// == D  D  B  B   2  22   ERROR Routine  
// == D  D  BBBB     22    ERROR Routine  
// == D  D  B   B   22                    
// == DDDD  BBBBb  222222
//
// == Valid for DB2 Version 9, 10 for z/OS
// ==           DB2 Version 9.7, 10 for LUW
//
//  By Dave Beulke – Dave Beulke and Associates
//
//  Questions? Comments! Improvements!  
//  Please contact moc.eklueBevaDnull@evaD                   
//
System.out.println(“-Error-moduleName.java: SQL Error: ” + sqle);
while (sqle != null) { // More SQLExceptions to process
        // =====> Optional IBM Data Server Driver for JDBC
        // error processing
        if (sqle instanceof DB2Diagnosable) {
          // Check if IBM Data Server Driver for JDBC
          // information exists
          com.ibm.db2.jcc.DB2Diagnosable diagnosable = (com.ibm.db2.jcc.DB2Diagnosable) sqle;
          //diagnosable.printTrace(printWriter, “”);
          java.lang.Throwable throwable = diagnosable.getThrowable();
          if (throwable != null) {
            // Extract java.lang.Throwable information much as message or stack trace.
          }
          DB2Sqlca sqlca = diagnosable.getSqlca();
          // Get DB2Sqlca object
          if (sqlca != null) { // Check that DB2Sqlca is not null
            int sqlCode        = sqlca.getSqlCode(); // Get the SQL error code
            String sqlErrmc    = sqlca.getSqlErrmc();     // Get the entire SQLERRMC
            String[] sqlErrmcTokens = sqlca.getSqlErrmcTokens();
  // You can also retrieve the individual SQLERRMC tokens
            String sqlErrp    = sqlca.getSqlErrp();  // Get the SQLERRP
            int[] sqlErrd     = sqlca.getSqlErrd();  // Get SQLERRD fields
            char[] sqlWarn    = sqlca.getSqlWarn();  // Get SQLWARN fields
            String sqlState   = sqlca.getSqlState(); // Get SQLSTATE
            String errMessage = sqlca.getMessage();     // Get error message
            System.out.println(“-Error-moduleName.java Error: ”    + errMessage);
            System.out.println(“————— SQLCA —————“);
            System.out.println(“Error code: ” + sqlCode);
            System.out.println(“SQLERRMC: ” + sqlErrmc);
            if (sqlErrmcTokens != null) {
              for (int i = 0; i < sqlErrmcTokens.length; i++) {
                System.out.println(”  token ” + i + “: “+ sqlErrmcTokens[i]);
                }
            }
            System.out.println(“SQLERRP: ” + sqlErrp);
              System.out.println(“SQLERRD(1): ” + sqlErrd[0] + “\n”
                   + “SQLERRD(2): ” + sqlErrd[1] + “\n”
                   + “SQLERRD(3): ” + sqlErrd[2] + “\n”
                   + “SQLERRD(4): ” + sqlErrd[3] + “\n”
                   + “SQLERRD(5): ” + sqlErrd[4] + “\n”
                   + “SQLERRD(6): ” + sqlErrd[5]);
              System.out.println(“SQLWARN1: ” + sqlWarn[0] + “\n”
                  + “SQLWARN2: ” + sqlWarn[1] + “\n”
                  + “SQLWARN3: ” + sqlWarn[2] + “\n”
                  + “SQLWARN4: ” + sqlWarn[3] + “\n”
                  + “SQLWARN5: ” + sqlWarn[4] + “\n”
                  + “SQLWARN6: ” + sqlWarn[5] + “\n”
                  + “SQLWARN7: ” + sqlWarn[6] + “\n”
                  + “SQLWARN8: ” + sqlWarn[7] + “\n”
                  + “SQLWARN9: ” + sqlWarn[8] + “\n”
                  + “SQLWARNA: ” + sqlWarn[9]);
              System.out.println(“SQLSTATE: ” + sqlState); //part of SQLException
            }
            sqle = sqle.getNextException(); // Retrieve next SQLException
          }
        }
  }

If that Java database exception routine is too extensive for your application, the routine below is also good for displaying the Java database exception debugging information.

catch (SQLException ex) {
// == DDDD  BBBB    2222                  
// == D  D  B  B   2  22   ERROR Routine                  
// == D  D  BBBB     22    ERROR Routine                  
// == D  D  B   B   22                              
// == DDDD  BBBBb  222222
//
// == Valid for DB2 Version 9, 10 for z/OS
// ==           DB2 Version 9.7, 10 for LUW
//
//  By Dave Beulke – Dave Beulke Associates
//
//  Questions? Comments! Improvements!  
//  Please contact moc.eklueBevaDnull@evaD  
//
      System.out.println(“\n— SQLException caught —\n”);
      while (ex != null) {
        System.out.println(“Message:   ” + ex.getMessage());
        System.out.println(“SQLState:  ” + ex.getSQLState());
        System.out.println(“ErrorCode: ” + ex.getErrorCode());
        ex = ex.getNextException();
        System.out.println(“”);
      }
        }

Questions also always come up about getting the Java database exception information and these two routines supply the debugging information needed to quickly understand the cause of the issues. Also note that these routines use the “System.out.println” that is extremely expensive and should be replaced with your corporate standard Java Logger infrastructure.

Include these routines inside the Java class method, test, and improve them to make sure your application provides the Java database exception information needed for your testing and production environments.  Please pass along any improvements as life and application coding always keeps moving ahead.

_____________________________________________________
On December 4th 5th and 6th I will be presenting at the Minneapolis, Milwaukee and Chicago DB2 User groups.

Please come by any of these presentations and say, “Hi.”
_____________________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.