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:

ENCODED_ARTIFACT CLOB (1024 M )

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 hibernate.properties 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() {
        super();
    }

    /**
     * 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 = 1024;
        }

        // 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
            buffer.append(result.substring(0, result.indexOf(")")));

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

                    break;
                case 1:
                    buffer.append(" K ");

                    break;
                case 2:
                    buffer.append(" M ");

                    break;
                case 3:
                    buffer.append(" G ");

                    break;
                default:
                    buffer.append(" M ");

                    break;
            }

            buffer.append(")");

            result = buffer.toString();
        }

        return result;
    }
}