Sunday, March 30, 2008

Hibernate Dialect for DB2

I have been working for about a week on making our Dodeca product work on DB2. I finally got it done today. First let me explain a little about how Dodeca works to set the context for my post.

Dodeca runs on metadata that describes every part of the application. All report definitions, Essbase connections, point of view selectors, toolbars and even custom code are stored in a relational database and are retrieved to the client via web services on 'as-needed' basis. The system is so completely run by metadata that an application can literally be exported to a single zip file. We often create prototypes for customers, ship them an exported zip of the metadata and, within minutes, they have their prototype up and running on their system.

On the middle tier, we use an open source Object-Relational Mapping ("ORM") technology called Hibernate. Hibernate takes care of persisting the Plain Old Java Object ("POJO") representations of the metadata in our server to a relational database. Hibernate uses a 'Dialect' to create the SQL syntax necessary for a particular database flavor. We have used Hibernate with a number of databases but most frequently with MySQL, SQL Server and Oracle. We have been able to get each of these databases running with a minimum of effort. My guess is that not as many people have used Hibernate with DB2 or that they haven't used it like we are using it.

In particular, we are storing the key metadata field in a Character Large OBject ("CLOB") field. Support for CLOB data among the relational database vendors is quite varied; DB2 does something quite different. After some research, I found that the CLOB fields produced by our Hibernate schema generation were very small. In fact, they were defaulting to a size of 255 bytes. That is pretty small for any piece of metadata, particularly an Excel file. I also found that DB2 CLOBs used a number and a scaling factor in their DDL language like this:


I looked at the Dialect base class in Hibernate and found an interesting method to override:

public String getTypeName(int code, int length, int precision, int scale) throws HibernateException

Based on my experience with Hibernate over the past couple of years, I guessed that the arguments passed to this function were obtained from the Hibernate mapping file. I tested that assumption with the following mapping:

<property name="EncodedArtifact" column="ENCODED_ARTIFACT" type="text" length="16" scale="2">

I was correct and decided to use the scale option to convert to int passed in to the method to a string in the format that DB2 could understand. In fact, I decided to let Hibernate create the initial string for a CLOB type and then I would modify it to fit my needs. I wrote the following class and used my new Dialect, com.appliedolap.dodeca.hibernate.dialect.DB2Dialect, in my file.

Here is the Java code for the dialect:

package com.appliedolap.dodeca.hibernate.dialect;

import org.hibernate.HibernateException;
import java.sql.Types;

* An SQL dialect for DB2 with fixes for Clob column creation for Dodeca.
@author Tim Tow

public class DB2Dialect extends org.hibernate.dialect.DB2Dialect {

public DB2Dialect() {

* Get the name of the database type associated with the given
* {
@link java.sql.Types} typecode with the given storage specification
* parameters. Note: this code calls the parent class getTypeName method
* unless the type is a CLOB. If the type is a CLOB, the length field from
* the Hibernate mapping is used as the length of the CLOB. Valid values are
* from 1 to 99999.
* The scale field is used to specify the scale of the CLOB. Valid values are 0 - bytes, 1 - kilobytes
* 2 - megabytes, and 3 - gigabytes. The default is megabytes.
@param code The {@link java.sql.Types} typecode
@param length The datatype length
@param precision The datatype precision
@param scale The datatype scale
@return the database type name
@throws org.hibernate.HibernateException If no mapping was specified for that type.

public String getTypeName(int code, int length, int precision, int scale) throws HibernateException {
// validate the length
if (length < 1 length > 99999) {
length =

// seed the typename
String result =
super.getTypeName( code, length, precision, scale );

// fix up the clob type for DB2 to create it properly
if (code == Types.CLOB) {
StringBuffer buffer =
new StringBuffer(16);

// add the first part of the string
0, result.indexOf(")")));

// add the scale for bytes/kbytes/mbytes/gbytes
switch (scale) {
case 0:
" ");

case 1:
" K ");

case 2:
" M ");

case 3:
" G ");

" M ");



result = buffer.toString();

return result;

Tuesday, March 25, 2008

Signup time for ODTUG

After a bunch of work, the ODTUG agenda is finally posted and if you want to keep up on the latest coming in the Essbase world and have access to the people who know, this is the place to be. Here is a link to the conference agenda:

My friend Edward Roske has already blogged about the trial and tribulations of putting together the track. His post, "Best Essbase Conference Ever?", is a good read. Here is the link:

Finally, there is a Hyperion-specific registration page that has just been published by the ODTUG team at:

Hopefully I will get some technical content posted here again in the next few weeks. I am off to Denver the week before Collaborate (again in Denver) for OBIEE training; I hope to post on some of what I learn there.

Until then, back to writing a custom Hibernate Dialect for DB2 (the as-shipped Hibernate DB2 Dialect doesn't create CLOB fields properly nor does it properly write a where clause if the column is an integer.. Arghhh!)

Monday, March 3, 2008

ODTUG Update

I have an update on the ODTUG Conference that I wanted to share with everyone. Edward Roske and I have been working hard on putting together a killer program that will attract people who are interested in learning, in-depth, what there is to know about Essbase. Edward has proposed a session schedule that just blows me away. It will be very unlike the Solutions Conferences over the past few years that really focused on customer success stories along with a moderated dose of 'what has recently shipped'. Rather, this conference will be all technical, period. We hope to get the sessions nailed down this week so we can make the agenda public.

Another thing I like about this conference is that, instead of the traditional golf outings that *used* to be a fixture at Solutions (years ago), this conference has a community service event on Saturday. Although the French Quarter and the Garden Districts of New Orleans have recovered relatively well, much of New Orleans is still reeling from the effects of Katrina. I haven't had the opportunity to get back to New Orleans since flying a couple of AngelFlight missions just after the storm and I look forward to helping again.

If you are an Essbase developer, mark your calendars for June 15-19 for New Orleans. Keep an eye on the website at for upcoming Hyperion announcements.