Raible's Wiki

Raible Designs
Wiki Home
Recent Changes


  - Korean
  - Chinese
  - Italian
  - Japanese

QuickStart Guide
  - Chinese
  - French
  - German
  - Italian
  - Korean
  - Portuguese
  - Spanish
  - Japanese

User Guide
  - Korean
  - Chinese

  - Chinese
  - German
  - Italian
  - Korean
  - Portuguese
  - Spanish

  - Korean

Latest Downloads

Other Applications

Struts Resume
Security Example
Struts Menu

Set your name in

Edit this page

Referenced by
...and 4 more

JSPWiki v2.2.33


Hide Menu


This page describes my attempt to get AppFuse working on IBM's DB2 8.1 (on Windows). Hopefully these instructions will help others trying to use DB2.

This howto assumed you've already installed DB2 and your DBA username/password combination is db2admin/db2admin.

Table of Contents

  • [1] Setup JDBC Driver
  • [2] Basic Configuration Tweaks
  • [3] Run JUnit Tests
  • [4] Configure Tomcat to talk to DB2
  • [5] Other issues
  • [6] Sample CREATE DATABASE script

Setup JDBC Driver [#1]

The first step is to add DB2's JDBC Driver to AppFuse. To do this, copy db2java.zip from DB2_INSTALL_DIR/java to appfuse/lib/db2-jdbc-8.1. The name of the directory is arbitrary, just make sure it matches up with your lib.properties file. After doing this, I renamed the jar to be db2java.jar because of issues I've seen with Oracle's classes12.zip.

Edit your lib.properties file to include the following information for the db2 driver:

# DB2 - http://www-306.ibm.com/software/data/db2/
db2.version                 = 8.1

Edit your build.properties file to reflect your new database of choice. MySQL settings are the defaults specified in properties.xml. Here is a sample for DB2:

#database URL for creating other databases (not used with db2)


You can use the database.properties.reference file for other database's settings.

I was unable to create databases on-the-fly (using JDBC) on PostgresSQL (transaction error) or on DB2. Therefore, I changed the "db-create" target to only attempt to do this for MySQL. If you're not using MySQL, you don't need to specify the database.admin.* properties since they won't be used. See the DB2 Sample Script[6] for creating a new database in the Command Line Processor.

Now let's looks at tweaks I had to make to AppFuse to make easy database switching a reality.

Code/Configuration Tweaks [#2]

The first error I got was on the app_user table.
[schemaexport] (hbm2ddl.SchemaExport                155 ) [IBM][CLI Driver][DB2/NT] SQL0542N  "EMAIL
" cannot be a column of a primary key or unique key because it can contain null values.  SQLSTATE=42
To fix this, I simply changed the e-mail field to be not unique in my XDoclet code. To make this modification, open src/ejb/.../User.java, search for "getEmail" and delete the unique="true" attribute. Of course, you could also make not-null="true". This change has been committed to CVS.

     * Returns the email.  This is an optional field for specifying a
     * different e-mail than the username.
     @return String
     * @struts.validator type="required"
     * @struts.validator type="email"
     * @hibernate.property column="email" not-null="false"
    public String getEmail() {
        return email;

After making this change and running "db-init" again, all tables should create successfully. Now it's time to load the database with our sample data. To do this, run "ant db-load". Wow, that worked for me without any issues - Cool! Now, let's try to run "ant test-ejb".

NOTE: After trying AppFuse on PostgreSQL, I discovered that using Hibernate's generator-class="native" for my id's failed. This is likely due to the fact that I'm inserting data (using DBUnit) and the sequences get out of wack. I changed all id's to "increment" (example below) and it fixed the problem. This worked on all the databases I tested (MySQL, PostgreSQL and DB2).

     * Returns the id.
     @return String
     * @hibernate.id column="id"
     *  generator-class="increment" unsaved-value="null"
    public Long getId() {
        return id;

Run JUnit Tests [#3]

When running "ant test-ejb", the UserDaoTest.testAddUserRole(), testAddUser() and testRemoveUser() all failed. The last test (removeUser) depends on addUser, so that makes sense why it failed. However, the first two don't make sense. The code for adding a new user role is fairly trivial:

    public void testAddUserRole() throws Exception {
        user = dao.getUser("tomcat");

        assertTrue(user.getRoles().size() == 1);

        UserRole role = new UserRole(Constants.ADMIN_ROLE);
        user = dao.saveUser(user);

        assertTrue(user.getRoles().size() == 2);

        user = dao.saveUser(user);

        assertTrue(user.getRoles().size() == 1);

The error I received from the testAddUserRole() method is as follows:

    [junit] Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0803N  One or mor
e values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE stateme
nt are not valid because the primary key, unique constraint or unique index identified by "1" constr
ains table "ADMIN.USER_ROLE" from having duplicate rows for those columns.  SQLSTATE=23505

After commenting out the code to remove the User's roles, all the tests run successfully. This must be because the role does not have a primary key. I fixed this by changing the return statement in UserDAOHibernate.saveUser() from return user to return getUser(user.getUsername()). This change has been committed to CVS.

After looking at the UserManagerImpl.java class, I noticed that I never use the returned value from dao.saveUser(), so it might be wiser to change the saveUser() method to be return type void and to call getUser() after saving, but I'll just leave it like it is for now.

After making this modification, "ant test-ejb" and "ant test-web -Dtestcase=Manager" runs just fine. Sweet! Now let's configure Tomcat to talk to the db2 database.

Configure Tomcat to talk to DB2 [#4]

Below is the old way to do it. In 1.3, I modified the tomcat-context.xml and hibernate.cfg.xml to get the database-specific attributes replaced at build-time. This eliminates the process below. I'll leave it in here in case you're on an older version of AppFuse.

The first step is to edit metadata/web/tomcat-context.xml. Search and replace the following:

com.mysql.jdbc.Driver -> COM.ibm.db2.jdbc.app.DB2Driver
jdbc:mysql://localhost:3306/appfuse?autoReconnect=true -> jdbc:db2:appfuse
test -> db2admin (usernames and passwords)
Run "ant setup-tomcat" to configure Tomcat with this new context and copy the jdbc driver to $CATALINA_HOME/common/lib.

Now we need to edit web/WEB-INF/classes/hibernate.cfg.xml so that Hibernate uses the DB2Dialect instead of the MySQLDialect. Open this file and change this value:

net.sf.hibernate.dialect.MySQLDialect -> net.sf.hibernate.dialect.DB2Dialect

Now running "ant test-all" (with Tomcat stopped) should result in BUILD SUCCESSFULL.

3 minutes later... Tests FAILED. It looks like DB2 doesn't like the SQL UPPER function:

     [java]     at org.appfuse.dao.UserDAOHibernate.getUsers(UserDAOHibernate.java:89)
     [java]     ... 65 more
     [java] Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0440N  No authori
zed routine named "UPPER" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884

After examining the code where upper occurs (getting a list of users), it appears I was doing an upper on the "id" column (BUG ALERT). I changed UserDAOHibernate.listUsersQuery to order by username, and it fixed this problem. This change has been committed to CVS.

    private static final String listUsersQuery =
        "from u in class " + User.class " order by upper(u.username)";

Now running "ant test-all" results in:

Total time: 2 minutes 29 seconds

Nice! yee haw

Other issues [#5]

"Text" field type in MySQL:
After getting AppFuse to work with DB2, I tried to migrate an AppFuse-based application to DB2, and discovered a few more issues. First of all, to mimic the "text" field type in MySQL, I used length="2147483647" as advised by the Hibernate documentation. DB2 doesn't seem to dig on that:
[schemaexport] (hbm2ddl.SchemaExport                155 ) [IBM][CLI Driver][DB2/NT] SQL0604N  The le
ngth, precision, or scale attribute for column, distinct type, structured type, attribute of structu
red type, function, or type mapping "VARCHAR(2147483647)" is not valid.  SQLSTATE=42611
According to this document, type="text" will give you the same thing, and that seems to work on DB2, so I changed all my length="..." files to be type="text". This results in CLOB(255) in DB2 - I hope that type holds large text fields.

Default table space not big enough:
The next error I received is a bit more cryptic:

[schemaexport] (hbm2ddl.SchemaExport                155 ) [IBM][CLI Driver][DB2/NT] SQL0286N  A defa
ult table space could not be found with a page size of at least "8192" that authorization ID "ADMIN"
 is authorized to use.  SQLSTATE=42727

After searching on Google it appears this is caused by some columns I have that are length="2000". I created a new Buffer Pool with Page Size = 8, and added a new Table Space that used this Buffer Pool. Running "ant db-init" worked after doing this.

Conflicts between inserted Ids and existing Ids:
I populate the database with sample data (using DBUnit) before running any of my tests. When adding new records, I kept getting errors about conflicting keys. Changing the generator-class from "native" to "increment" solved the problem. I don't know if this is the best solution, I'm just trying to get any solution working at this point.

Temporary Table Space not big enough:
One of my DAOs keeps failing with the following error:

    [junit] COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL1585N  A system temporary ta
ble space with sufficient page size does not exist.  SQLSTATE=54048
Creating a Temporary Table Space that uses the Buffer Pool with page size = 8 fixed this problem.

Change defaultAutoCommit:
In order to get my JSP tests to run, and successfully insert data, I had to change the context config file for Tomcat (myapplication.xml) so that the defaultAutoCommit value was true:

Now I have to try and figure out how to get AppFuse running on WebSphere 5.x. This should be fun. ;-)

Sample CREATE DATABASE script [#6]

After encountering the page size problems above, I spent some time creating a database creation script that sets suitable page sizes for table spaces. I also modified AppFuse so that the "defaultAutoCommit" is "true" out of the box. I tested this on Windows and nothing was case sensitive (all databases and tablespaces names got created in uppercase).
-- Create the APPFUSE database
create database appfuse 

-- Connect to database
connect to appfuse user db2admin using 'db2admin'

-- Create the APPFUSE 8K Buffer Pool
-- The IBMDEFAULTBP buffer pool cannot be dropped (SQLSTATE 42832)
-- https://aurora.vcu.edu/db2help/db2s0/sqls0629.htm#HDRDROPP 
create bufferpool APPFUSE_BP size 250 pagesize 8 k

-- Create the APPFUSE 8K User Tablespace
drop tablespace userspace1
create tablespace userspace1 pagesize 8 k managed by system using 
    ('C:\DB2\NODE0000\SQL00001\SQLT0002.0') bufferpool APPFUSE_BP

-- Create the APPFUSE 8K Temp Tablespace
create temporary tablespace APPFUSE_TEMP pagesize 8 k 
    managed by system 
    using ('C:\DB2\NODE0000\SQL00001\SQLT0003.0') 
    bufferpool APPFUSE_BP
-- DB2 Users are mapped to OS Users, so we can't create them in SQL
-- Do this mapping (or New User Creation) manually using the Control Center

Go to top   Edit this page   More info...   Attach file...
This page last changed on 06-Nov-2006 13:52:58 MST by MattRaible.