Raible's Wiki: AppFuseOnDB2

At line 3 changed 1 line.

;:''This howto assumed you've already installed DB2 and your DBA username/password combination is admin/admin.''

;:''This howto assumed you've already installed DB2 and your DBA username/password combination is db2admin/db2admin.''

At line 11 added 1 line.

* [6] Sample CREATE DATABASE script

At line 26 changed 4 lines.

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

At line 31 removed 1 line.

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

At line 30 added 1 line.

database.jar=${db2.jar}

At line 34 changed 1 line.

database.name=jdbc:db2:appfuse

#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=org.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

At line 36 removed 12 lines.

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

}}}

At line 49 changed 1 line.

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.

You can use the database.properties.reference file for other database's settings.

At line 51 changed 1 line.

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.

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

At line 50 added 2 lines.

Now let's looks at tweaks I had to make to AppFuse to make ''easy database switching'' a reality.

At line 60 changed 1 line.

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

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

At line 71 changed 2 lines.

* @hibernate.property

* column="email" not-null="false"

* @hibernate.property column="email" not-null="false"

At line 79 added 16 lines.

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

[{Java2HtmlPlugin

/**

* Returns the id.

* @return String

*

* @hibernate.id column="id"

* generator-class="increment" unsaved-value="null"

*/

public Long getId() {

return id;

}

}]

At line 112 changed 1 line.

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

At line 133 added 4 lines.

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

At line 123 changed 1 line.

test -> admin (usernames and passwords)

test -> db2admin (usernames and passwords)

At line 135 changed 1 line.

{{{ [java] at org.appfuse.persistence.UserDAOHibernate.getUsers(UserDAOHibernate.java:89)

{{{ [java] at org.appfuse.dao.UserDAOHibernate.getUsers(UserDAOHibernate.java:89)

At line 141 changed 1 line.

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.

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

At line 166 added 1 line.

</div>

At line 177 added 1 line.

__"Text" field type in MySQL:__\\

At line 186 added 1 line.

__Default table space not big enough:__\\

At line 173 changed 1 line.

After [searching on Google|http://tinyurl.com/ys3zm] 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.

After [searching on Google|http://tinyurl.com/ys3zm] 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:

{{{

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

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

{{{

<name>defaultAutoCommit</name>

</parameter>

}}}

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

!!Sample CREATE DATABASE script [#6]

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

{{{

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

}}}

Raible's Wiki

AppFuse

Other Applications

AppFuseOnDB2