Table of Contents

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:

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.

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

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:

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

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:

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.

"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:

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:

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:

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