AppFuseOnDB2

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

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]

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
db2.dir=${lib.dir}/db2-jdbc-${db2.version}
db2.jar=${db2.dir}/db2java.jar

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:

database.type=db2
database.name=jdbc:db2:appfuse

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]

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

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]

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]

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)

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.

Now running "ant test-all" results in:

Nice!

Other issues [#5]

[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

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 one 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