Alfresco - DB2: Details of Issues encountered

[jc]

Could the Alfresco team (or anyone else who has looked into this) please share the specifics of what issues were encountered when configuring Alfresco to use DB2. I learned that one problem was with respect to user locks, but was looking for an exhaustive list (if there is one).

Reason is we are embracing Alfresco on WebSphere and DB2 and wanted to prepare for the sort of issues we can expect to run into going forward.

Thanks!

[derek]

During high concurrent load, look for lock escalation.  What version of Alfresco are we dealing with here?

[jc]

Ok, we will keep an eye on that as we move on with this. We are planning to use ver 2.1 (community).
And please let me know what else if anything.

Thanks!

[fiki]

I have successfully configured Alfresco 2.1 community edition to run with DB2.

Here is my little how-to.

First of all I had to configure the database connection (url, username/password, drivers,…) in the custom-repository.properties file, and set the correct hibernate dialect in custom-hibernate-dialect.properties.

Then I put the needed db2 jars (db2jcc.jar, db2jcc_javax.jar,db2jcc_license_cu.jar) into the lib folder of the server instance (for example D:/JBOSS/server/alfresco/lib/).

I tried to run jboss, in order to get the SQL script needed for creating all the tables. Hibernate automatically generates the script and puts it in the C:\Documents and Settings\user_name\Local Settings\Temp\Alfresco\ on windows (/tmp/Alfresco/ on linux).

Pay attention: there is a problem in the generated script. An index name is too long - fk_attributes_n_acl (DB2 accepts up to 18 characters); I removed the last charater, so it became fk_attributes_n_ac - up to now still everything's fine

I tried to run the updated script in dbvisualizer, and came across another problem. DB2 complained that there were problems with table spaces. So I created a regular and a system temporary table space with 32K of page size. In a serious environment, you should ask your DBA to sreate it for you

Now the SQL script went through without problems.

Once the tables are created, you should change custom-repository.properties file, by adding this line: db.schema.update=false

This prevents hibernate to check and try to update the database everytime jboss is restarted (I think this is only needed when upgrading alfresco - correct me if I'm wrong).

That's it.

[jc]

Thanks for replying, but I have gotten past that point myself. I have Alfresco on WAS running against DB2. This thread was more to check to see what sort of issues people are having while using Alfresco against DB2. Derek reposnded with one such issue. I'm looking to get as many gotchas as I can together so we know what to look out for as we move ahead with using it.

But thanks for your list of tasks, it does tally well with the steps I took.

Regards,
Javeed

[beb4ch]

Just wanted to add that the DB2 9.5 and Alfresco 2.1 work great. There is no problem with the index name length so you can allow Hibernate to create the schema for you. The only thing that needs to be done is to create the database with the 32K pagesize.