Testing and Deployment
Deploying your JSP is an easy task with most databases. Only compiled classes or JAR files can be loaded, but many databases can compile your code directly as you load it if you wish. Oracle provides a batch file called "loadjava" that makes it very easy to load source files, class files, or JAR files. (Keep in mind that any third-party APIs referenced by your classes must be inserted as well.) When everything is loaded, a stored procedure must be defined to wrap the Java methods. Below is an example of a PL/SQL FUNCTION for wrapping a JSP method:
CREATE OR REPLACE FUNCTION DB_OBJECT_JSP
(X IN BLOB) RETURN BLOB
AS
LANGUAGE JAVA
NAME
'com.package.class.DatabaseHelperJSP.databaseObjectJSP (oracle.sql.BLOB)
return oracle.sql.BLOB';
During testing, we quickly learned that debugging is one of the more frustrating aspects of using JSPs. There's no way to step through your JSP, nor is there generally an easy way to get output, so your standard last-ditch debugging technique of inserting a million System.outs to see where something is crashing won't fly here. So, odd and unexpected behavior can be difficult to track. But exceptions raised from JSPs do come back to the client wrapped in SQLExceptions, so it may be a good idea to write some debug text to a variable during the course of your method and then include that into the text of your error message, if possible.
When all was said and done and the application was using JSPs for all its databaseintensive tasks, application runs were again in the 10–15 minute range, with lengthy jobs taking about 30 minutes, regardless of location. Still not blisteringly quick by any means, but a great improvement over the 5+ hour times previously seen at remote sites. However, a small caveat: Monitoring the database box itself showed that CPU utilization was very high during these intensive and lengthy jobs, limiting the number of processes that we could run simultaneously. The high utilization is likely due to the code itself not being properly optimized to run on the database in batches of several-thousand queries or updates at a time, and will likely be addressed in future releases.
In this situation, moving to JSPs drastically improved performance at remote sites by eliminating the latency problem. And, with our applications already implemented in Java, using JSPs allowed us to realize this performance increase relatively quickly. However, the difficulty in debugging occasionally hurt us during testing, and high server loads during runs might call for further optimizations to our database code down the road.
NOTE
Special thanks to Roger Jestel, Matt Johnson, and Jason Randle for their help in designing, implementing, and testing the solutions discussed here.