AppFuseOnDB2

This is version 9. 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 db2admin/db2admin.

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.

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]

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

database.jar=${db2.jar}
database.type=db2
#database.name=appfuse
database.host=localhost
#database URL for creating other databases (not used with db2)
database.admin.url=jdbc:${database.type}://${database.host}/db2
database.admin.username=db2admin
database.admin.password=db2admin

hibernate.dialect=net.sf.hibernate.dialect.DB2Dialect
database.driver_class=COM.ibm.db2.jdbc.net.DB2Driver
database.url=jdbc:${database.type}://${database.host}/${database.name}
database.username=db2admin
database.password=db2admin

PostgreSQL:_

database.jar=${postgresql.jar}
database.type=postgresql
database.name=appfuse
database.host=localhost
#database URL for creating other databases
database.admin.url=jdbc:${database.type}://${database.host}/template1
database.admin.username=postgres
database.admin.password=postgres

hibernate.dialect=net.sf.hibernate.dialect.PostgreSQLDialect
database.driver_class=org.postgresql.Driver
database.url=jdbc:${database.type}://${database.host}/${database.name}
database.username=postgres
database.password=postgres

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]

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

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

I'm sure if I had a proper database creation script, with proper table spaces sizes (page size = 8), this would be much easier.

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:

        <parameter>
            <name>defaultAutoCommit</name>
            <value>true</value>
        </parameter>

Sample CREATE DATABASE script [#6]

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