Raible's Wiki: AppFuseOnDB2

January 16, 2004 UPDATE: I greatly simplified this process in AppFuse and altered this HowTo accordingly. This reflects the latest code in CVS and updates that will be in 1.3.

Setup JDBC Driver [#1]

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

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, followed by one for PostgreSQL:

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]

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

Run JUnit Tests [#3]




    public void testAddUserRole() throws Exception {

        user = dao.getUser("tomcat");



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



        UserRole role = new UserRole(Constants.ADMIN_ROLE);

        user.addRole(role);

        user = dao.saveUser(user);



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



        user.getRoles().remove(role);

        user = dao.saveUser(user);



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

    }

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 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 -> 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. This change has been committed to CVS.




    private static final String listUsersQuery =

        "from u in class " + User.class + " order by upper(u.username)";

Other issues [#5]

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

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:

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:

Raible's Wiki

AppFuse

Other Applications

AppFuseOnDB2

Table of Contents

Setup JDBC Driver [#1]

Code/Configuration Tweaks [#2]

Run JUnit Tests [#3]

Configure Tomcat to talk to DB2 [#4]

Other issues [#5]

Sample CREATE DATABASE script [#6]