EII and Stored Procedures

When IBM announced that the next rev of DB2 would support Oracle PL/SQL stored procedures, native, it seemed like a good deal. As I understand it, suppose I want to move an Oracle database function onto DB2. Before, I would have had to rewrite the PL/SQL stored procedure for the business rule in DB2’s language; now, I just recompile the SP I had on the Oracle machine. Then I can redirect any apps that use the Oracle SP to DB2, which is less of a modification than also changing the SP call in the app. It’s native, so there’s little degradation in performance. Neat, yes?

Well, yes, except that (a) in many cases, I’ve just duplicated much of the functionality of a similar existing DB2 SP, and (b) I don’t care how native it is, it doesn’t run as well as the DB2-language SP – after all, that language was designed for DB2 25 years ago. If we do this a lot, we create a situation in which we have SP A on Oracle, plus SP A and A’ (the DB2-language one) on DB2, all of which have to be upgraded together, for lots and lots of As. We’ve already got SP proliferation, with no records of which apps are calling the SP and hence a difficult app modification when we change the SP; now you want to make it worse?

This kind of thinking is outdated. It assumes the old 1990s environments of one app per database and one database vendor per enterprise. Those days are gone; now, in most situations, you have multiple databases from multiple vendors, each servicing more than one app (and composite app!).

So what’s the answer? Well, in cases like this, I tend to support the idea of “one interface to choke”. The idea is that there should be one standard SP language, with adapters to convert to multiple database SP source and compiled code – a “many to one to many” model. I send a PL/SQL SP to the converter, it puts it in standard language, then converts it to all “target” languages. It then sends it down to all target databases, where each compiles it and has it available.

Now suppose I invoke an SP originally written in PL/SQL, but it is to be applied to a DB2 database. The converter intercepts the PL/SQL-type invocation and converts it to a DB2-type format, and then (because I have told it to) redirects the invocation to the DB2 database. The database runs the DB2-type SP version, and returns the result to the converter, which performs a reverse conversion to deliver the result back to the invoking app.

The cool thing about this is that when you migrate or copy an SP, no app needs to be changed – remember, you simply tell the converter to redirect the invocation. The usual objection is, yes, but the converted SP doesn’t run as well as one written for the target database. True (actually, in the real world sometimes not true!), but the difference is marginal, and, in case you hadn’t noticed, there’s less pressure these days to get the absolute maximum out of a SP, with so many other performance knobs to twiddle.

Where’s the best place to set up such a converter? Why, an EII (Enterprise Information Integration) tool! It has the architecture – it takes SQL/XQL and translates/adapts for various databases, then reverses the translation for the results. Yes, we have to add stored procedure conversion to its tasks, but at least the architecture is in place, and we can integrate SP invocation with general SQL-handling.

And, by the way, we will have a metadata repository of stored procedures, all served up and ready for use in event-driven architectures.

What do you think?