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.

Advertisements