Working together with DB2 Express-C 9.5, Tomcat 6.0.x, Liferay 5.1.x on CentOS 5.2

IBM DB2 Express-C and Liferay are enterprise class applications which are free to develop, deploy and distribute without limitation.

This guide explains how to set up IBM DB2 Express-C and Liferay running on a text-mode shell CentOS linux server.

DB2 #

Prerequisites #

Install the necessary C++ compatibility libraries

	yum install compat-libstdc++-33
	yum install libaio

Install DB2 #

Download DB2 Express-C 9.5 from http://www-306.ibm.com/software/data/db2/express/download.html

Login as root, unzip and install

	tar zxvf db2exc_950_LNX_x86.tar.gz
	cd exp
	./db2_install

Post installation tasks #

Create groups

	groupadd db2grp1
	groupadd db2fgrp1
	groupadd dasadm1

Create users

	useradd -g db2grp1 -m -d /opt/ibm/db2/V9.5/db2inst1 db2inst1 
	useradd -g db2fgrp1 -m -d /opt/ibm/db2/V9.5/db2fenc1 db2fenc1 
	useradd -g dasadm1 -m -d /opt/ibm/db2/V9.5/dasusr1 dasusr1 

Assign password for each users created

	passwd db2inst1
	passwd db2fenc1
	passwd dasusr1

Create an instance

	cd /opt/ibm/db2/V9.5/instance
	./db2icrt -p 50000 -u db2fenc1 db2inst1

Create DB2 Administration Server

	cd /opt/ibm/db2/V9.5/instance
	./dascrt -u dasadm1

Add the following line into /etc/services

	DB2_TMINST	50000/tcp

Login as db2inst1 and set communication protocol

	db2 update dbm cfg using svcename 50000
	db2set DB2COMM=tcpip

Start DB2 database

	db2start

JDK #

Installation #

Download jdk-6u7-linux-i586.bin from http://java.sun.com/javase/downloads/index.jsp

Login as root and install as below steps

	mv jdk-6u7-linux-i586.bin /opt/
	cd /opt/
	chmod +x jdk-6u7-linux-i586.bin
	./jdk-6u7-linux-i586.bin

Environment #

Create a text file /etc/profile.d/java.sh and add the following lines

	export JAVA_HOME=/opt/jdk1.6.0_07
	export PATH=$JAVA_HOME/bin:$PATH

Set up Java environment

	source /etc/profile.d/java.sh
	alternatives --install /usr/bin/java java /opt/jdk1.6.0_07/bin/java 2
	alternatives --config java

Liferay #

Install Liferay #

Download and unzip the below two files from http://www.liferay.com/web/guest/downloads/portal

• liferay-portal-sql-5.1.1.zip
• liferay-portal-tomcat-6.0-5.1.1.zip

Sample data #

Load sql script to create a sample database 'lportal'

	db2 -td\; -vf create-db2.sql

JDBC driver #

Install jdbc driver files db2jcc.jar and db2jcc_license_cu.jar into directory $TOMCAT_HOME/lib/ext

Database connection #

Modify database connection of $TOMCAT_HOME/conf/Catalina/localhost/ROOT.xml

	<Resource
		name="jdbc/LiferayPool"
        	auth="Container"
        	type="javax.sql.DataSource"
        	driverClassName="com.ibm.db2.jcc.DB2Driver"
        	url="jdbc:db2://your_ip_address:50000/lportal:fullyMaterializeLobData=true;
			fullyMaterializeInputStreams=true;
			progressiveStreaming=2;
			progresssiveLocators=2;"
        	username="db2inst1"
        	password="your_password"
        	maxActive="10"
	/>

The additional connection parameters of jdbc url are fixing issues of using blob type of DB2 in hibernate: fullyMaterializeLobData=true;fullyMaterializeInputStreams=true;progressiveStreaming=2;progresssiveLocators=2

References: http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14053553

http://www.hibernate.org/120.935.html

Add the following statements into $TOMCAT_HOME/lib/portal-ext.properties

	hibernate.dialect=com.liferay.portal.dao.orm.hibernate.DB2Dialect
	custom.sql.function.isnull=CAST(? AS VARCHAR(32672)) IS NULL
	custom.sql.function.isnotnull=CAST(? AS VARCHAR(32672)) IS NOT NULL

Appendix #

Bugs fix for DB2 8.2 #

For DB2 version 8.2.x or before, property value of "deferPrepares" should be false like this:

File: $TOMCAT_HOME/conf/Catalina/localhost/ROOT.xml

	<Resource
		name="jdbc/LiferayPool"
        	auth="Container"
        	type="javax.sql.DataSource"
        	driverClassName="com.ibm.db2.jcc.DB2Driver"
        	url="jdbc:db2://your_ip_address:50000/lportal:fullyMaterializeLobData=true;
			fullyMaterializeInputStreams=true;
			progressiveStreaming=2;
			progresssiveLocators=2;
			deferPrepares=false;"
        	username="db2inst1"
        	password="your_password"
        	maxActive="10"
	/>

Reference: http://www.hibernate.org/120.826.html

Hibernate cannot detect DB2 8.x. Their workaround is setting hibernate dialect explicitly. Hence please make use the setting of hibernate.dialect portal-ext.properties as below:

File: $TOMCAT_HOME/lib/portal-ext.properties

	hibernate.dialect=com.liferay.portal.dao.orm.hibernate.DB2Dialect

Reference: https://jira.jboss.org/jira/browse/HIBERNATE-64

Backup & restore data #

Assume that you login as db2inst1 and backup data into directory "/home/db2inst1/backup":

db2 force applications all
db2 backup db lportal to /home/db2inst1/backup

Restore data from directory "/home/db2inst1/backup":

db2 force applications all
db2 restore db lportal from /home/db2inst1/backup replace existing

Reference: http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/index.jsp?topic=/com.ibm.itame3.doc_5.1/am51_perftune78.htm