Half SQL: semi-opaque tables reduce developer effort

Your relational data design could steal a trick from NoSQL: database representation of your application objects doesn’t need to be broken down to primitive values. Some opacity of relational data is beneficial.

Relational databases impose rigid structure on the data: data is organized in tables, which are made up of columns. Traditionally the columns could only be of primitive types (numeric or alpha), although opaque types (BLOB, CLOB) and XML are now universally available. The rigid structure makes it easy to reason about data, to guarantee data quality in the database layer and to build tooling on top of the database. Querying based on values of individual columns is well defined and highly efficient. Downstream usage (reporting, analysis) becomes easier. Relational structure works well when you need referential integrity across entities that are not updated together.

But relational databases have downsides. One of the downsides is the notorious difficulty of changing the database structure. Martin Fowler wrote: “A lot of effort in application development is tied up in working with relational databases. Although Object/ Relational Mapping frameworks have eased the load, the database is still a significant source of developer hours”. Guy Harrison blogging for Tech Republic: “Change management is a big headache for large production RDBMS. Even minor changes to the data model of an RDBMS have to be carefully managed and may necessitate downtime or reduced service levels“. There is “impedance mismatch”  between RDBMS and application layer: “Application developers have been frustrated with the impedance mismatch between the relational data structures and the in-memory data structures of the application”. Even a change confined to a single table (e.g. adding a column) requires significant effort and synchronizing rollout of database and application layers.

The frustration with the amount of developers’ effort that the relational databases required was one of the drivers behind the rise of NoSQL starting about a decade ago. NoSQL databases differ from the relational in many other ways and switching to NoSQL is not an easy step. Fortunately, you can solve some of your problems by using just one item from the NoSQL bag of tricks. You can greatly reduce the impact of single-table changes (such as adding a new column, the most frequent type of change) by making your table definition semi-opaque. Don’t throw away your old and trusty RDBMS. Turn it into a Half SQL data store: in each table, select a small number of key columns that may be used in indexes and keep them. Hide all other fields from RDBMS by placing them into an opaque container field of a BLOB type. As a simplified example, Orders table may look like this:

Your application will be responsible for serializing and deserializing those blobs. Adding a new object field will be invisible to the RDBMS. When you need to add a new field, you will only need to change the code in the serializer/deserializer. And if you use a good serialization library (if your application is written in Java, please, don’t use built in serialization; there are many libraries that are faster and more flexible), even those changes in most cases will be NOOP because your library will take care of those automatically. No data migration will be needed. You will be able to write test to verify that your logic works before and after the change. And you retain the RDBMS goodness of referential integrity and super-fast queries over the indexed columns.

Stashing all “other” object fields into a BLOB column could save you quite a bit of effort.

Database creation script for Alphablox install

Suppose you install Alphablox, say as part of WebSphere Business Monitor product. Alphablox has been and still remians (as of version 6.2) an important integral part of WBM, providing dimensional analysis of monitored data and KPIs. Alphablox requires a number of database objects to function. By default it will attempt to create database tables it needs upon first run. But what if you are using DB2 for z/OS or another restrictive database environment, where programs are not allowed to run DDL. DBAs hold the keys to database structure and you are required to submit any DDL to a DBA for review and execution. Not unreasonable for a structured corporate IT where I’d expect to find WebSphere Business Monitor.
So you want to get your hands on DDL Alphablox would attempt to execute and hand it your DBA. It is possible, even though scripts are not on the surface as they are for the rest of WBM (or othe products in Business Process Management stack).

While scripts do not exist in a form of standalone DDL files, DDL statements are available. Just inspect properties file named $ABX_ROOT/repository/servers/$dbtype.dmlsql, where $ABX_ROOT is the root of Alphablox installation directory and $dbtype is your database type, e.g. db2_zos.dmlsql
Look for properties named DDL.CREATE1-DDL.CREATE6 and DDL.INDEX1-DDL.INDEX10

You will find entries like this:

DDL.CREATE1            = CREATE TABLE ABX_VERSION (DESCRIPTION VARCHAR(32) NOT NULL, VALUE VARCHAR(64) NOT NULL)

This is not a complete script, of course. You will have to add things like tablespaces and permission grants, but all it is exactly what program needs.

DROP statements and a number of updates/inserts round out this file.

UPDATE 12/5/2009. In response to Mike Killelea’s comment below. I worked with Mike on the DB2-z/OS based Alphablox installation. Mike correctly mentions that ABX install over DB2/z not straightforward. Installation procedure is nothing like regular ABX install and you have to jump through some hoops. Yet in the end it is possible to host Alphablox repository on DB2-z/OS. In my post, I wanted to give a pointer that would be useful in doing so. It is certainly not all you will need to do. I’m not in a position to disclose details of this procedure – please contact IBM support. I just wanted to correct the record in that the task is doable.