Installing LR with DB2

Ganesh Ram
Published Date14 Years Ago - 48865 Views

Recently, I had to configure LR to IBM's DB-2 Database.
I tried this on my windows machine. If you are installing on any other OS, you could use these steps as a guideline, the steps would be pretty similar though.


Here are the steps.

Step 1:

I downloaded DB-2 9.5 Express from here.

This is a trial version that is valid for 90 days.

Step 2:

Install DB2. It is pretty straight forward.

Step 3:

Open the DB2 Control Center.

Clik on "All Databases".

Click on Create New Database.

Enter the Database Name as lportal, and click on Finish.

Step 4:

Click on lportal in the "All Databases".

Click on User and Group Objects -> DB Users -> Add New User.

AFAIK, DB2 maps the OS users to the DB users. Not sure how to create new users in DB2.

So I created a OS user named lportal.

In the "Add New User" dialog, enter the user Name as lportal, and allocate all permissions.

Click on Apply and Ok.

 

To update the user password, in control center -> Tools -> Configuration Assistant -> Right Click on lportal -> Change Password -> Enter the userId, old and new password and click on "OK".


Step 5:

The next step is to place the DB2 driver classes in the app server path.

Copy the db2jcc.jar, db2jcc_license_cu.jar from <db2-install>IBM\SQLLIB\java and put them in <Glassfish>\domains\<ur domain>\lib directory if you are using Glassfish as the app server.

If you are using tomcat, then copy these 2 jars into <tomcat>\lib\ext directory.


Step 6:

For LR-5.2 and above, In WEB-INF\classes\portal-ext.properties, over ride the jdbc-default* properties, and give the appropriate values for DB2.

I gave the following properties

jdbc.default.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.default.url=jdbc:db2:lportal
jdbc.default.username=lportal
jdbc.default.password=lportal

Enter the values as per your set up.

If you are using any version that is before 5.2, then you need to update the DB config in Root.xml (Tomcat) and domain.xml (for Glassfish).

Step 7:

In case of Glassfish, you have this additional step.

Access the admin console (localhost:4848 by default).

Click Configurations -> your_config -> JVM settings -> Path settings -> Native Library Path Suffix.

I added the following:

F:/PROGRA~1/IBM/SQLLIB/BIN;F:/PROGRA~1/IBM/SQLLIB/FUNCTION;F:/PROGRA~1/IBM/SQLLIB/SAMPLES/REPL

Add the values according to the path in your DB2 install.

Save this setting. GF saves this in your domain.xml.

This step is NOT eeded in case of Tomcat. When DB2 installs, it sets the above parameters as env variables.

Tomcat reads these env varialbes, but GF dosent.

If you dont do this in case of GF, you would java.lang.UnSatisfiedLink Error because GF cant find the dll files.

Thanks Sandeep, for your guidance on this step . I was struggling on this step, when he came over to my desk and discovered what the problem was, and suggested this solution.

Step 8:

Restart app server,

You would see that when the app server starts, the tables are created.

Goto DB2 control center, and verify that the tables are created.

Thats it !!

Pls leave ur comments on this page.












 

3

More Blog Entries

thumbnail

Loving the new machine...

thumbnail
Ray Augé
01 May
5
thumbnail

Podcast interview by Kito Mann of JSF Central regarding Liferay and ICEfaces

thumbnail
Neil Griffin
06 May
0
Blogs
Joel Gilchrist
12 Years Ago
Hi I have also just installed LR 6.0.6 with DB2 Version 9 (not express) on Tomcat on Windows. On first inspection the basic things seems to be working but when I check the tomcat log it shows a few errors during what I think is the create table process when first starting LR. Did you see these? Do you think there are any underlying issues that I should look at?

Here are the errors:

17:18:54,968 INFO [PortalImpl:298] Portal lib directory /D:/liferaybundle-6.0.6
/liferay-portal-6.0.6/tomcat-6.0.29/webapps/ROOT/WEB-INF/lib/
Starting Liferay Portal Community Edition 6.0.6 CE (Bunyan / Build 6006 / Februa
ry 17, 2011)
17:19:06,625 ERROR [JDBCExceptionReporter:101] DB2 SQL Error: SQLCODE=-204, SQLS
TATE=42704, SQLERRMC=LIFERAY.LOCK_, DRIVER=4.9.78
17:19:06,625 ERROR [JDBCExceptionReporter:101] DB2 SQL Error: SQLCODE=-727, SQLS
TATE=56098, SQLERRMC=2;-204;42704;LIFERAY.LOCK_, DRIVER=4.9.78
17:19:06,625 ERROR [JDBCExceptionReporter:101] DB2 SQL Error: SQLCODE=-727, SQLS
TATE=56098, SQLERRMC=2;-204;42704;LIFERAY.LOCK_, DRIVER=4.9.78
17:19:07,421 WARN [ReleaseLocalServiceImpl:143] DB2 SQL Error: SQLCODE=-204, SQ
LSTATE=42704, SQLERRMC=LIFERAY.RELEASE_, DRIVER=4.9.78
17:19:07,421 INFO [ReleaseLocalServiceImpl:84] Create tables and populate with
default data
17:19:23,546 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008, SQL
ERRMC=IX_7020130F, DRIVER=4.9.78: create index IX_7020130F on SCProductVersion (
directDownloadURL);
17:19:26,031 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008, SQL
ERRMC=IX_89509087, DRIVER=4.9.78: create index IX_89509087 on User_ (companyId,
openId);
17:19:27,343 INFO [BaseDB:400] Database supports case sensitive queries

And later on during the loading of KALEO:

17:19:42,500 INFO [ServiceComponentLocalServiceImpl:187] Running Kaleo SQL scri
pts
17:19:42,765 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-286, SQLSTATE=42727, SQL
ERRMC=8192;LIFERAY, DRIVER=4.9.78: create table KaleoLog (kaleoLogId LONG not nu
ll primary key,groupId LONG,companyId LONG,userId LONG,userName VARCHAR(200) nul
l,createDate DATE null,modifiedDate DATE null,kaleoDefinitionId LONG,kaleoInstan
ceId LONG,kaleoInstanceTokenId LONG,kaleoTaskInstanceTokenId LONG,kaleoNodeId LO
NG,kaleoNodeName VARCHAR(200) null,terminalKaleoNode BOOLEAN,kaleoActionId LONG,
kaleoActionName VARCHAR(200) null,kaleoActionDescription VARCHAR(2000) null,prev
iousKaleoNodeId LONG,previousKaleoNodeName VARCHAR(200) null,previousAssigneeCla
ssName VARCHAR(200) null,previousAssigneeClassPK LONG,currentAssigneeClassName V
ARCHAR(200) null,currentAssigneeClassPK LONG,type_ VARCHAR(50) null,comment_ VAR
CHAR(2000) null,startDate DATE null,endDate DATE null,duration LONG,workflowCont
ext TEXT null);
17:19:43,578 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_73B5F4DE on KaleoLog (com
panyId);
17:19:43,578 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_6C64B7D4 on KaleoLog (kal
eoDefinitionId);
17:19:43,578 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_5BC6AB16 on KaleoLog (kal
eoInstanceId);
17:19:43,578 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_25157F25 on KaleoLog (kal
eoInstanceTokenId, kaleoNodeId, type_);
17:19:43,593 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_470B9FF8 on KaleoLog (kal
eoInstanceTokenId, type_);
17:19:43,593 WARN [BaseDB:376] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQL
ERRMC=LIFERAY.KALEOLOG, DRIVER=4.9.78: create index IX_B0CDCA38 on KaleoLog (kal
eoTaskInstanceTokenId);
17:19:44,343 INFO [HookHotDeployListener:394] Registering hook for kaleo-web

But as I said I am able to get to the portal and add users/content etc. So everything looks fine - I'm just worried that there is something wrong that will show itself later on.

Thanks,
Joel
Please sign in to reply.
Sheetal Sharma
12 Years Ago
These are steps I followed with liferay 6.0.5 CE and tried to migrate from HSQL to DB2.
1. On local machine, I added following jar files tomcat-6.0.26\lib\ext folder and restarted server.
a . db2java.zip
b. db2jcc.jar
c. db2jcc_license_cu.jar
d. db2jcc_license_cisuz.jar
e. db2jcc_javax.jar
f. db2policy.jar

2. Logged in as admin and added following details for migration and clicked on execute button.

jdbc.default.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.default.url=jdbc:db2://Server_Name:port/DB_Name
jdbc.default.username=lportal
jdbc.default.password=Schema_Password

3. It ran successful for some tables but it always throws exception, here is the exception stack-trace.
Hi Sandeep,
Thanks for your inputs - these are steps I followed.
1. On local machine, I added following jar files tomcat-6.0.26\lib\ext folder and restarted server.
a. db2java.zip
b. db2jcc.jar
c. db2jcc_license_cu.jar



2. Logged in as admin and added following details for migration and clicked on execute button.

jdbc.default.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.default.url=jdbc:db2://Server_Name:port/DB_Name
jdbc.default.username=lportal
jdbc.default.password=Schema_Password

3. It ran successful for some tables but it always throws exception in Kaleolog migration. Here is the exception stack-trace.

12:10:31,754 INFO [Table:407] Finished backup of KaleoDefinition to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoDefinition-1325679031754 in 0 ms
12:10:32,848 INFO [Table:371] Starting backup of KaleoInstance to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoInstance-1325679032848
12:10:32,848 INFO [Table:407] Finished backup of KaleoInstance to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoInstance-1325679032848 in 0 ms
12:10:33,301 INFO [Table:371] Starting backup of KaleoInstanceToken to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoInstanceToken-1325679033301
12:10:33,301 INFO [Table:407] Finished backup of KaleoInstanceToken to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoInstanceToken-1325679033301 in 0 ms
12:10:33,660 INFO [Table:371] Starting backup of KaleoLog to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoLog-1325679033660
12:10:33,660 INFO [Table:407] Finished backup of KaleoLog to C:\Liferay\Softwares\Server\liferay-portal-6.0.5\tomcat-6.0.26\temp/temp-db-KaleoLog-1325679033660 in 0 ms
12:10:35,129 WARN [MaintenanceUtil:78] Session 9300F69285EE550A70627E4B59DA19DD is null
12:10:35,129 FATAL [ConvertProcessMessageListener:35] Unable to process message {destinationName=liferay/convert_process, responseDestinationName=null, responseId=null, payload=com.liferay.portal.convert.ConvertDatabase, values=null}
com.liferay.portal.convert.ConvertException: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -286, SQLSTATE: 42727, SQLERRMC: 8192;LPORTAL
at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:53)
at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:47)
at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:32)
at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:63)
at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:56)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -286, SQLSTATE: 42727, SQLERRMC: 8192;LPORTAL
at com.ibm.db2.jcc.c.tf.e(tf.java:1680)
at com.ibm.db2.jcc.c.tf.b(tf.java:1207)
at com.ibm.db2.jcc.b.jb.i(jb.java:225)
at com.ibm.db2.jcc.b.jb.b(jb.java:50)
at com.ibm.db2.jcc.b.w.b(w.java:36)
at com.ibm.db2.jcc.b.cc.e(cc.java:150)
at com.ibm.db2.jcc.c.tf.m(tf.java:1204)
at com.ibm.db2.jcc.c.tf.a(tf.java:1993)
at com.ibm.db2.jcc.c.tf.c(tf.java:566)
at com.ibm.db2.jcc.c.tf.executeUpdate(tf.java:551)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:227)
at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:187)
at com.liferay.portal.convert.ConvertDatabase.migrateTable(ConvertDatabase.java:251)
at com.liferay.portal.convert.ConvertDatabase.doConvert(ConvertDatabase.java:154)
at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:44)
... 7 more
Please sign in to reply.
David Welch
8 Years Ago
Deployed 6.2 GA4 on Tomcat 7 (bundled), install fine, but no portlets can install.

After going to Marketplace, then Control Panel | Apps | Purchased, try to click Install on any app, just spins and spins. Cannot install any apps using DB2. Have tried this multiple times, same result. DB2 error is relating to cursor, posted this in forums, no solution provided. Any ideas?
Please sign in to reply.