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

Referenced by
...and 4 more

JSPWiki v2.2.33


Hide Menu


This is version 4. It is not the current version, and thus it cannot be edited.
[Back to current version]   [Restore this version]

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 admin/admin.

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

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 properties.xml and change the database.jar to be db2.jar:

<property name="database.jar" location="${db2.jar}"/>

Edit database.properties, where you'll change a number of settings:

Uncomment the db2 settings and comment out the mysql settings
## DB2
hibernate.dialect net.sf.hibernate.dialect.DB2Dialect
hibernate.connection.driver_class = COM.ibm.db2.jdbc.app.DB2Driver
hibernate.connection.url = jdbc:db2:apptrack
hibernate.connection.username = admin
hibernate.connection.password = admin
hibernate.connection.pool_size = 20
hibernate.statement_cache.size = 6
hibernate.show_sql = false

I haven't yet figured out how to create the appfuse database using straight SQL, but I did find it to be fairly easy using DB2's "Command Line Processor". Just open the application and type "create db appfuse" - and wait for about 2 minutes.

At this point, running "ant setup-db" will fail because we have no db2-create.sql in metadata/sql. Since the database is already created, I just used "ant db-init". Now onto the errors and ways I fixed them.

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
So 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".

     * 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".

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()).

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]

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 -> admin (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.persistence.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.

    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. I suppose if I had a database creation script, this would not be a problem because the Buffer Pool and Table Space sizes would be set properly.

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.
I'm sure if I had a proper database creation script, with proper table spaces sizes (page size = 8), this would be much easier.

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

Go to top   More info...   Attach file...
This particular version was published on 06-Nov-2006 13:52:23 MST by MattRaible.