Geek! SQL or Java?

Typical hard core (and perhaps inexperience) OO developer will despise others that put business logics in somewhere not their sphere of influences, well, let say in database server in the form of SQL stored procedures or UDF. To a certain extent, me as an overlord in OO realm (:p), agreed on this obstinated design decision, but sometime over engineering an application design will bring only undesirable consequences.

Design is all about trade offs, trade offs and trade offs. There is time when you should capitalize on platform specific features to make your life simpler and meeting the supposed functionalities. Remember the MERGE statement in my previous blog, LOL

Putting logics in database layer have advantages and disadvantages. Especially in this age of messy system integrations and consolidations, more than one application tends to access the same relational data sources.

You should notice by now around you, if the corporate IT environment involves lots of enterprise applications, that exact same logics were replicated across horizontally aligned systems. Changes are foreseen to be challenging.

The good news is you can use your existing knowledge and put it upfront in IBM DB2 environment. Support for Java Stored Procedures and UDFs is there for quite sometime, but their popularity still not exceptionally high, maybe due to the fact that data layer programmers' preference on SQL as prime language.

Basically what you need to do are (For Windows):

1. Put your logic in a Java class' static methods
2. Copy the compiled bytecodes in the specified DB2 directory, depending on whether it is FENCED or UNFENCED (for UDFs). or call a system SP to install the packaged JAR that contains the bytecodes.

3. Issue CREATE FUNCTION/PROCEDURE with proper options to the external Java methods.

That's all. Simple right? Of course NO, there are other critical considerations when using Java SP/UDFs.

Check out the REDBOOK here

Good luck, Folks.