Amicon Tech Guest

Fix for DB2 Compatibility with Opentaps

Author - Amit Shinde

Amicon has implemented a stable permanent fix for DB2 compatibility with Opentaps. The main problem with DB2 was that we simply could not run select queries on entities that contain CLOB/BLOB type fields. LOBs don’t support scrollable cursors on DB2 and all the screens of Opentaps that did select queries on tables with LOBs were broken.

Final Solution

We had a previous solution for working around LOBs which required us to fix each area of code/screen that was broken by excluding LOB fields from the select queries. This was not the best solution and would have required a lot of code changes depending on what screens we wanted to work.

We have implemented a much better and contained solution using CachedRowSet. The implementation of the fix is described below -

When a select query is run for entities having LOBs and the ResultSetType is TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE, an exception is thrown by DB2 - com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -270, SQLSTATE: 42997, SQLERRMC: 63. This has been fixed with the help of CachedRowSet. Whenever we catch this exception, we execute the same query with ResultSetType as TYPE_FORWARD_ONLY and then return the ResultSet as CachedRowSet as follows –

ResultSet _rs = myPreparedStatement.executeQuery();

CachedRowSetImpl crs = new CachedRowSetImpl();

crs.populate(_rs);

_rs = (ResultSet)crs;

CachedRowSet Limitations:

A CachedRowSet object does have a limitation. It is limited in size by the amount of data it can store in memory at one time. The amount of data that it can contain at any one time is determined by the amount of memory available. To get around this limitation, a CachedRowSet object can retrieve data from a ResultSet object in chunks of data, called pages. To take advantage of this mechanism, an application sets the number of rows to be included in a page using the method setPageSize. In other words, if the page size is set to five, a chunk of five rows of data will be fetched from the data source at one time. An application can also optionally set the maximum number of rows that may be fetched at one time. If the maximum number of rows is set to zero, or no maximum number of rows is set, there is no limit to the number of rows that may be fetched at a time.

Please refer here for more details - http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

Conclusion:

CachedRowSet fixes all the problems related to LOBs and all Opentaps screens were functional and working after a comprehensive testing. We can do performance tuning moving forward as we start seeing performance hits and memory issues but this is more unlikely to happen as LOBs are used infrequently throughout the application.

The files that were modified are –

GenericDAO.java

SQLProcessor.java

GenericDataSourceException.java

fieldtypedb2.xml

entityengine.xml