Accessing Database using Eclipse/ Rational Application Developer

If your someone like me who is using Eclipse/ RAD for most of the development work and want to connect to database then you might want to consider using Database development perspective in Eclipe/RAD. The database perspective allows you to work with any database using JDBC. It is almost same in both Eclipse and RAD. I took these screen shots in Eclipse Galileo but the basics remain same in RAD.

In my case i am using Apache Derby database and i wanted to work with it. Before i do that i will have to configure the Derby driver. These are the steps that you can follow to configure and use your database

1. In your IDE click on Windows -< Preferences and it will open preferences window. In the preferences window click on Data -< Connectivity -< Driver definitions.
   
   
   


2. Click on Add and it will open New Driver Definition dialog box like this. Since i want to connect to Network version of Apache Derby 10.2, i will select it like this
   
   


3. As you can see when we select the Derby Client JDBC Driver, it is giving an error message "Unable to locate JAR/zip in file system as specified by the driver definition: derbyclient.jar.", that is because Eclipse is not able to find the .jar file containing the database driver for Apache Derby.
   
4. Switch to the Jar list tab like this, on this tab you will notice that derbyclient.jar is already added but it is not pointing to actual file on your machine so remove empty derbyclient.jar and add the derbyclient.jar on your machine like this
   
   


5. Save your changes and it will take you back to the Driver definitions list, Since i configured only Apache Derby so far, it is listed here. If you want to connect to additional databases configure them over here.
   
   

Now once the Database driver is configured, next step is to configure Database connection follow these steps for that.

1. Switch to Database development perspective and in the Datasource Explorer view click on New Connection Profile to get Connection Profile dialog like this
   
   


2. On the next page configure your database connection properties such as DB Name, user id password, same properties as you will use to connect to that database using JDBC.
   
   


3. After setting properties, click on Test Connection button to verify that your actually able to connect to database. If everything works fine click on Finish


4. Once the connection is open you should be will get a view like this, you can use the SQL scrapbook to write one or more query and execute them against the database or you can use database explorer view to explore the database structure.
   
   
   

Error due to missing table space in JCR database

When i was executing ConfigEngine.sh database-transfer to transfer data from derby to DB2 data based it was failing with this error


action-switch-database:
Sat May 16 20:17:54 EDT 2009

action-create-jcrls-tables:
Sat May 16 20:17:54 EDT 2009
[echo] Calling cmcfgdbu to create LS tables
[echo] In case of error, refer to /software/WebSphere/wp_profile/ConfigEngine/log/JCRDB61.log
[java] com.ibm.cm.dbutil.ICMDBUtilsException: Fail to create database
[java] 2009-05-16 20:18:01 cmcfgdbu: ERROR ->Fail to create database
[java] at com.ibm.cm.dbutil.ICMConfigureLSDB.createDB(ICMConfigureLSDB.java:337)
[java] at com.ibm.cm.dbutil.ICMConfigureLSDB.configureLSDB(ICMConfigureLSDB.java:1193)
[java] at com.ibm.cm.dbutil.ICMConfigureLSDB.start(ICMConfigureLSDB.java:1860)
[java] at com.ibm.cm.dbutil.cmcfgdbu.startSilentMode(cmcfgdbu.java:162)
[java] at com.ibm.cm.dbutil.cmcfgdbu.startConfig(cmcfgdbu.java:275)
[java] at com.ibm.cm.dbutil.cmcfgdbu.main(cmcfgdbu.java:291)

BUILD FAILED
/software/WebSphere/wp_profile/ConfigEngine/base_dynamic.xml:821: The following error occurred while executing this line:
/software/WebSphere/wp_profile/ConfigEngine/config/actions/db_cfg_mgr.xml:290: The following error occurred while executing this line:
/software/WebSphere/PortalServer/jcr/prereq.jcr/config/includes/jcr_cfg.xml:117: The following error occurred while executing this line:
/software/WebSphere/PortalServer/jcr/prereq.jcr/config/includes/jcr_cfg.xml:1005: The following error occurred while executing this line:
/software/WebSphere/PortalServer/jcr/prereq.jcr/config/includes/jcr_cfg.xml:1069: The following error occurred while executing this line:
/software/WebSphere/PortalServer/jcr/prereq.jcr/config/includes/jcr_cfg.xml:3924: Java returned: 202
Total time: 6 minutes 53 seconds
isIseries currently set to: null
uploading registry
CELL: spinfotech
Websphere:_Websphere_Config_Data_Type=Registry,_Websphere_Config_Data_Id=cells/spinfotech|registry.xml#Registry_1242441339497,_WEBSPHERE_CONFIG_SESSION=anonymous1242519482288


This is what the stack trace looked like like in the JCRDB61.log file.


2009-05-16 20:18:00 cmcfgls: TRACE -> - getDBOperations()
2009-05-16 20:18:00 cmcfgls: Creating Server database
2009-05-16 20:18:00 cmcfgls: TRACE -> + createDB()
2009-05-16 20:18:00 cmcfgls: Starting database creation
2009-05-16 20:18:00 cmcfgls: TRACE -> + connectToDB()
2009-05-16 20:18:00 cmcfgls: TRACE -> dbJDBCDriverType 4
2009-05-16 20:18:00 cmcfgls: TRACE -> conString jdbc:db2://dbserver.wpcertification.com:50000/JCRDB61
2009-05-16 20:18:00 cmcfgls: TRACE -> loading jdbc driver : com.ibm.db2.jcc.DB2Driver
2009-05-16 20:18:00 cmcfgls: TRACE -> Connecting jdbc:db2://dbserver.wpcertification.com:50000/JCRDB61
2009-05-16 20:18:01 cmcfgls: TRACE -> Connected jdbc:db2://dbserver.wpcertification.com:50000/JCRDB61
2009-05-16 20:18:01 cmcfgls: TRACE -> - connectToDB()
2009-05-16 20:18:01 cmcfgls: Creating DB2 Content Manager tables
2009-05-16 20:18:01 cmcfgls: TRACE -> + executeSQL()
2009-05-16 20:18:01 cmcfgls: TRACE -> source --> /software/WebSphere/wp_profile/PortalServer/jcr/config/icmplsct.ddl
2009-05-16 20:18:01 cmcfgls: DEBUG -> Executing:
CREATE TABLE jcr.ICMSTADMINDOMAINS ( DOMAINID INTEGER NOT NULL, PRIMARY KEY (DOMAINID) ) IN ICMSFQ04

2009-05-16 20:18:01 cmcfgls: ERROR ->Fail to execute:
CREATE TABLE jcr.ICMSTADMINDOMAINS ( DOMAINID INTEGER NOT NULL, PRIMARY KEY (DOMAINID) ) IN ICMSFQ04

com.ibm.cm.dbutil.ICMDBUtilsException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: ICMSFQ04
at com.ibm.cm.dbutil.ICMDBUtilsCommon.executeSQL(ICMDBUtilsCommon.java:875)
at com.ibm.cm.dbutil.ICMDBUtilsCommon.executeSQL(ICMDBUtilsCommon.java:768)
at com.ibm.cm.dbutil.ICMConfigureLSDB.createDB(ICMConfigureLSDB.java:321)
at com.ibm.cm.dbutil.ICMConfigureLSDB.configureLSDB(ICMConfigureLSDB.java:1193)
at com.ibm.cm.dbutil.ICMConfigureLSDB.start(ICMConfigureLSDB.java:1860)
at com.ibm.cm.dbutil.cmcfgdbu.startSilentMode(cmcfgdbu.java:162)
at com.ibm.cm.dbutil.cmcfgdbu.startConfig(cmcfgdbu.java:275)
at com.ibm.cm.dbutil.cmcfgdbu.main(cmcfgdbu.java:291)
com.ibm.cm.dbutil.ICMDBUtilsException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: ICMSFQ04
at com.ibm.cm.dbutil.ICMDBUtilsCommon.executeSQL(ICMDBUtilsCommon.java:903)
at com.ibm.cm.dbutil.ICMDBUtilsCommon.executeSQL(ICMDBUtilsCommon.java:768)
at com.ibm.cm.dbutil.ICMConfigureLSDB.createDB(ICMConfigureLSDB.java:321)
at com.ibm.cm.dbutil.ICMConfigureLSDB.configureLSDB(ICMConfigureLSDB.java:1193)
at com.ibm.cm.dbutil.ICMConfigureLSDB.start(ICMConfigureLSDB.java:1860)
at com.ibm.cm.dbutil.cmcfgdbu.startSilentMode(cmcfgdbu.java:162)
at com.ibm.cm.dbutil.cmcfgdbu.startConfig(cmcfgdbu.java:275)
at com.ibm.cm.dbutil.cmcfgdbu.main(cmcfgdbu.java:291)
com.ibm.cm.dbutil.ICMDBUtilsException: Fail to create database
at com.ibm.cm.dbutil.ICMConfigureLSDB.createDB(ICMConfigureLSDB.java:337)
at com.ibm.cm.dbutil.ICMConfigureLSDB.configureLSDB(ICMConfigureLSDB.java:1193)
at com.ibm.cm.dbutil.ICMConfigureLSDB.start(ICMConfigureLSDB.java:1860)
at com.ibm.cm.dbutil.cmcfgdbu.startSilentMode(cmcfgdbu.java:162)
at com.ibm.cm.dbutil.cmcfgdbu.startConfig(cmcfgdbu.java:275)
at com.ibm.cm.dbutil.cmcfgdbu.main(cmcfgdbu.java:291)


I did some googling and it seems that this problem is because i did not execute these configuration commands on my JCR database

db2 "CONNECT TO JCRDB61 USER jcr"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 8000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 8000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"

db2 "DISCONNECT jcrdb"
db2 "TERMINATE"


These commands are explained in more details on Creating Remote database page.

After executing these set of commands i re-executed the database-transfer configuration command and that problem seems to be solved now. The DB transfer is still going on but i passed the step where it failed last time

Error due to incorrect character encoding of database during database transfer

I was getting the following error when executing ./ConfigEngine database-transfer configuration command to transfer database from derby to DB2. I was facing this error because my RELDB61 was not created with UTF-8 as character encoding. So when portal tried creating a table with var char it failed

In my case i did create the RELDB61 database using the DB2 Control center by right clicking on the Database -> Create database.

[sqlproc] CREATE TABLE release.WSRP_PROD_LOD (
[sqlproc] WSRP_PROD_OID CHAR(18) FOR BIT DATA NOT NULL,
[sqlproc] LOCALE VARCHAR(64) NOT NULL,
[sqlproc] TITLE VARGRAPHIC(255) NOT NULL,
[sqlproc] DESCRIPTION VARGRAPHIC(1024),
[sqlproc] CONSTRAINT PK560 PRIMARY KEY (WSRP_PROD_OID, LOCALE),
[sqlproc] CONSTRAINT FK560 FOREIGN KEY (WSRP_PROD_OID) REFERENCES release.WSRP_PROD (OID) ON DELETE CASCADE
[sqlproc] )

[sqlproc] Error during statement execution!
[sqlproc] com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -1216, SQLSTATE: 56031, SQLERRMC: null
[sqlproc] at com.ibm.db2.jcc.b.sf.e(sf.java:1680)
[sqlproc] at com.ibm.db2.jcc.b.sf.b(sf.java:1207)
[sqlproc] at com.ibm.db2.jcc.c.jb.i(jb.java:224)
[sqlproc] at com.ibm.db2.jcc.c.jb.b(jb.java:50)
[sqlproc] at com.ibm.db2.jcc.c.w.b(w.java:36)
[sqlproc] at com.ibm.db2.jcc.c.cc.f(cc.java:150)
[sqlproc] at com.ibm.db2.jcc.b.sf.m(sf.java:1204)
[sqlproc] at com.ibm.db2.jcc.b.sf.a(sf.java:1987)
[sqlproc] at com.ibm.db2.jcc.b.sf.e(sf.java:809)
[sqlproc] at com.ibm.db2.jcc.b.sf.execute(sf.java:793)
[sqlproc] at com.ibm.wps.config.SqlProcessor.process(SqlProcessor.java:609)
[sqlproc] at com.ibm.wps.config.SqlProcessor.process(SqlProcessor.java:148)
[sqlproc] at com.ibm.wps.config.SqlProcessorTask.executeSqlScript(SqlProcessorTask.java:774)
[sqlproc] at com.ibm.wps.config.SqlProcessorTask.execute(SqlProcessorTask.java:327)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:64)
[sqlproc] at com.ibm.wps.config.If.execute(If.java:75)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:64)
[sqlproc] at com.ibm.wps.config.If.execute(If.java:75)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at com.ibm.wps.config.ForTask.execute(ForTask.java:56)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at com.ibm.wps.config.ForTask.execute(ForTask.java:56)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.helper.SingleCheckExecutor.executeTargets(SingleCheckExecutor.java:37)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:382)
[sqlproc] at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:107)
[sqlproc] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[sqlproc] at org.apache.tools.ant.Task.perform(Task.java:364)
[sqlproc] at org.apache.tools.ant.Target.execute(Target.java:341)
[sqlproc] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[sqlproc] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[sqlproc] at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
[sqlproc] at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
[sqlproc] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[sqlproc] at org.apache.tools.ant.Main.runBuild(Main.java:668)
[sqlproc] at org.apache.tools.ant.Main.startAnt(Main.java:187)
[sqlproc] at org.apache.tools.ant.Main.start(Main.java:150)
[sqlproc] at com.ibm.wps.config.ConfigEngine.process(ConfigEngine.java:861)
[sqlproc] at com.ibm.wps.config.ConfigEngine.main(ConfigEngine.java:236)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
[sqlproc] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[sqlproc] at java.lang.reflect.Method.invoke(Method.java:618)
[sqlproc] at com.ibm.ws.bootstrap.WSLauncher.main(WSLauncher.java:263)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[sqlproc] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
[sqlproc] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[sqlproc] at java.lang.reflect.Method.invoke(Method.java:618)
[sqlproc] at com.ibm.wps.config.launch.WpsConfigLauncher.process(WpsConfigLauncher.java:243)
[sqlproc] at com.ibm.wps.config.launch.WpsConfigLauncher.main(WpsConfigLauncher.java:459)
[sqlproc] (ErrCode=-1216, SqlState=56031)
[sqlproc] _________________________________________________________
[sqlproc] Ant Database Properties:

[sqlproc] DbDomain specific settings (release):
[sqlproc] release.DbType = db2
[sqlproc] release.DbUrl = jdbc:db2://dbserver.wpcertification.com:50000/RELDB61
[sqlproc] release.DbName = RELDB61
[sqlproc] release.DbSchema = release
[sqlproc] release.DbUser = db2admin
[sqlproc] release.DbPassword = PASSWORD_REMOVED

[sqlproc] DbType specific settings (db2):
[sqlproc] db2.DbDriver = com.ibm.db2.jcc.DB2Driver
[sqlproc] db2.DbLibrary = /software/IBM/db2jcc.jar | /software/IBM/db2jcc_license_cu.jar
[sqlproc] _________________________________________________________
[sqlproc] _________________________________________________________

BUILD FAILED
/software/WebSphere/wp_profile/ConfigEngine/base_dynamic.xml:821: The following error occurred while executing this line:
/software/WebSphere/wp_profile/ConfigEngine/config/actions/db_cfg_mgr.xml:290: The following error occurred while executing this line:
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:95: The following error occurred while executing this line:
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:337: ERROR: Error during the execution of the sql files.

Total time: 3 minutes 1 second
isIseries currently set to: null
uploading registry
CELL: spinfotech
Websphere:_Websphere_Config_Data_Type=Registry,_Websphere_Config_Data_Id=cells/spinfotech|registry.xml#Registry_1242441339497,_WEBSPHERE_CONFIG_SESSION=anonymous1242503359324


In order to solve this problem, i had to drop all the databases that i created using DB2 Control center and re-create them using the following DB2 script

db2 "CREATE DB RELDB61 using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR RELDB61 USING applheapsz 4096"
db2 "UPDATE DB CFG FOR RELDB61 USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR RELDB61 USING stmtheap 32768"
db2 "UPDATE DB CFG FOR RELDB61 USING dbheap 2400"
db2 "UPDATE DB CFG FOR RELDB61 USING locklist 1000"
db2 "UPDATE DB CFG FOR RELDB61 USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR RELDB61 USING logprimary 12"
db2 "UPDATE DB CFG FOR RELDB61 USING logsecond 20"
db2 "UPDATE DB CFG FOR RELDB61 USING logbufsz 32"
db2 "UPDATE DB CFG FOR RELDB61 USING avg_appls 5"
db2 "UPDATE DB CFG FOR RELDB61 USING locktimeout 30"
db2 "UPDATE DB CFG FOR RELDB61 using AUTO_MAINT off"


This is only for Release database so i had to recreate COMDB61, CUSDB61, JCRDB61, FDBKDB61, LMDB61 database again using the similar code.

After executing the code i retried the database-transfer configuration task and it seems to be working now

Invalid path separator specified in db2.DbLibrary

I was getting this error when i was trying to validate the database driver information that i entered in wkplc_dbtype.properties file by executing ConfigEngine.bat validate-database-driver configuration command.

In my case i am trying to connect to DB2 using the Type 4 driver. In order to do that i did set db2.DBLibrary property like this in wkplc_dbtype.properties file

db2.DbLibrary=/software/IBM/db2jcc.jar;/software/IBM/db2jcc_license_cu.jar




action-validate-database-driver-dbdomain:
Sat May 16 15:07:36 EDT 2009


[sqlproc] action: validate-db-driver
[sqlproc] _________________________________________________________
[sqlproc] DbDomain: release
[sqlproc] Invalid path separator specified in db2.DbLibrary

BUILD FAILED
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:659: The following error occurred while executing this line:
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:665: Invalid path separator specified in db2.DbLibrary

Total time: 55 seconds
isIseries currently set to: null
uploading registry
Created admin client: com.ibm.ws.management.AdminClientImpl@6e406e4
Created config Service Proxy: com.ibm.websphere.management.configservice.ConfigServiceProxy@24322432
CELL: spinfotech
Websphere:_Websphere_Config_Data_Type=Registry,_Websphere_Config_Data_Id=cells/spinfotech|registry.xml#Registry_1242441339497,_WEBSPHERE_CONFIG_SESSION=anonymous1242500859524


db2.DbLibrary=/software/IBM/db2jcc.jar;/software/IBM/db2jcc_license_cu.jar


action-validate-database:
Sat May 16 15:17:32 EDT 2009
[echo] domain 'release'
[echo] DbtDbDriver 'com.ibm.db2.jcc.DB2Driver'
[echo] DbtDbLibrary '/software/IBM/db2jcc.jar'
[echo] DbtDbUser 'db2admin'
[echo] DbtDbUrl 'jdbc:db2://dbserver.wpcertification.com:50000/reldb61:returnAlias=0;'
[echo] DbtDbName 'reldb61'
[java] [05/16/09 15:17:35.954 EDT] Attempting to make connection using: jdbc:db2://dbserver.wpcertification.com:50000/reldb61:returnAlias=0; :: db2admin :: PASSWORD_REMOVED
[java] [05/16/09 15:17:36.473 EDT] ERROR: Error obtaining connecting for jdbc:db2://dbserver.wpcertification.com:50000/reldb61:returnAlias=0;
[java] com.ibm.db2.jcc.b.SqlException: No license is present.
[java] at com.ibm.db2.jcc.b.o.d(o.java:563)
[java] at com.ibm.db2.jcc.b.p.(p.java:859)
[java] at com.ibm.db2.jcc.c.b.(b.java:324)
[java] at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:165)
[java] at java.sql.DriverManager.getConnection(DriverManager.java:572)
[java] at java.sql.DriverManager.getConnection(DriverManager.java:165)
[java] at com.ibm.wps.config.db.Database.init(Database.java:139)
[java] at com.ibm.wps.config.db.validation.ValidationDriver.main(ValidationDriver.java:209)
[java] Exception in thread "main" com.ibm.db2.jcc.b.SqlException: No license is present.
[java] at com.ibm.db2.jcc.b.o.d(o.java:563)
[java] at com.ibm.db2.jcc.b.p.(p.java:859)
[java] at com.ibm.db2.jcc.c.b.(b.java:324)
[java] at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:165)
[java] at java.sql.DriverManager.getConnection(DriverManager.java:572)
[java] at java.sql.DriverManager.getConnection(DriverManager.java:165)
[java] at com.ibm.wps.config.db.Database.init(Database.java:139)
[java] at com.ibm.wps.config.db.validation.ValidationDriver.main(ValidationDriver.java:209)

BUILD FAILED
/software/WebSphere/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:719: The following error occurred while executing this line:
/software/WebSphere/wp_profile/ConfigEngine/config/actions/dbt_cfg.xml:205: Java returned: 1

Total time: 1 minute 33 seconds
isIseries currently set to: null
uploading registry
Created admin client: com.ibm.ws.management.AdminClientImpl@3f923f92
Created config Service Proxy: com.ibm.websphere.management.configservice.ConfigServiceProxy@49664966
CELL: spinfotech
Websphere:_Websphere_Config_Data_Type=Registry,_Websphere_Config_Data_Id=cells/spinfotech|registry.xml#Registry_1242441339497,_WEBSPHERE_CONFIG_SESSION=anonymous1242501459200


So i had to change the wkplc_dbtype.properties file to take out the ; and the db2jcc_license_cu.jar.

db2.DbLibrary=/software/IBM/db2jcc.jar

With this change i could successfully execute ConfigEngine.bat validate-database-driver command but the ConfigEngine.bat validate-database-connection command started failing. So after executing the validate-database-driver we have to revert back to the original version of wkplc_dbtype.properties

Configuring WebSphere Portal to use multiple databases

Before few days i tried database transfer task to transfer data from default Apache Derby database to DB2 database. At that time i did create a single database on the DB2 WPSDB and data for all 6 different database domains was transferred to WPSDB with different schema names. I did document it under Database transfer manually.

This time i wanted to use following 6 different databases for each of the database domains.

1. RELDB61: Release domain


2. COMDB61: Community domain


3. CUSDB61:Customization domain


4. JCRDB61: JCR database.


5. FDBKDB61: Feed back domain


6. LMDB61: Learning management

I followed these steps to do the database transfer

• Crate 6 databases on your target server by using the following script
  
  
db2 "CREATE DB RELDB61 using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR RELDB61 USING applheapsz 4096"
db2 "UPDATE DB CFG FOR RELDB61 USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR RELDB61 USING stmtheap 32768"
db2 "UPDATE DB CFG FOR RELDB61 USING dbheap 2400"
db2 "UPDATE DB CFG FOR RELDB61 USING locklist 1000"
db2 "UPDATE DB CFG FOR RELDB61 USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR RELDB61 USING logprimary 12"
db2 "UPDATE DB CFG FOR RELDB61 USING logsecond 20"
db2 "UPDATE DB CFG FOR RELDB61 USING logbufsz 32"
db2 "UPDATE DB CFG FOR RELDB61 USING avg_appls 5"
db2 "UPDATE DB CFG FOR RELDB61 USING locktimeout 30"
db2 "UPDATE DB CFG FOR RELDB61 using AUTO_MAINT off"

This sample is for creating RELDB61 database. Similarly create 5 other databases.



• 
  In case of JCR database execute these additional script to tune the database as well as create table spaces that are required for JCR database.
  

db2 "CONNECT TO JCRDB61 USER jcr"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 8000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 8000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"

db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

  Make sure that each of the SQL statement is executed successfully
  


• 
  Next create a JDBC provider for connecting to DB2 database using the type 4 driver named wpdbJDBC_db2
  


• Next create one following 6 data sources using the wpdbJDBC_db2 JDBC provider. Each of the data sources should connect to each of the 6 databases.
  
  
  
  • wpdbDS_reldb: for connecting to RELDB61 database
  
  
  • wpdbDS_cusdb: for connecting to CUSDB61 database
  
  
  • wpdbDS_comdb: for connecting to COMDB61 database
  
  
  • wpdbDS_jcrdb: for connecting to JCRDB61 database
  
  
  • wpdbDS_fdbk: for connecting to FDBKDB61 database
  
  
  • wpdbDS_lmdb: for connecting to LMDB61 database
  
  
  
  While creating a datasource in WAS if you created a new security alias then you will have to restart the server
  


• Next set your database preferences in wkplc_dbtype.properties file like this. The wkplc_dbtype.properties file is used to configure your database driver related information such as what is the DBDriver class that you want to use, the .jar files that have the DBDriver related code and name of the JDBC Provider that you created in the WAS
  
# DbDriver: The name of class SqlProcessor will use to import SQL files
# For DB2 Type 2 driver use COM.ibm.db2.jdbc.app.DB2Driver
# For DB2 Type 4 driver use com.ibm.db2.jcc.DB2Driver
db2.DbDriver=com.ibm.db2.jcc.DB2Driver

# DbLibrary: The directory and name of the zip/jar file containing JDBC driver class
# For DB2 Type 2 driver use /java/db2java.zip
# For DB2 Type 4 driver use /java/db2jcc.jar;/java/db2jcc_license_cu.jar
# Please use the system specific file separator names, e.g. for windows semicolon and for unix colon.
db2.DbLibrary=/software/IBM/db2jcc.jar;/software/IBM/db2jcc_license_cu.jar

# JdbcProviderName: The name of jdbc provider to be used
db2.JdbcProviderName=wpdbJDBC_db2

  


• The wkplc_comp.properties is the main file for database configuration. It defines what datasource and database name should be used for each of the portal database domains. These are the values that i set for RELEASE domain. Similarly change values for other 6 domains
  

  


• Once your done setting the wkplc_dbtype.properties you should validate your configuration by executing these two configuration commands
  
ConfigEngine.bat validate-database-driver
ConfigEngine.bat validate-database-connection

  


• If the validation is successful then first stop the WebSphere Portal server and then execute ./ConfigEngine.sh database-transfer command to transfer data. In my case it took close to 40 minutes to finish the database transfer


• Once the database transfer is completed successfully start the WebSphere Portal server and try login in into to see if it works

Database Transfer Manually

If you dont want to use Configuration Wizard or cant use Configuration wizard for some reason then you can use Manual process for database transfer. The manual process can be divided into 3 basic sections

Creating necessary JDBC Provider and Data Source

You will have to

• First create a wpdbJDBC_db2 JDBC Provider for connecting to DB2


• Configure a wpdbDS as datasource, use same value for name and JNDI name


• Restart the server and make sure that Test Connection is successful for this Data source

Important Note: When you create a new DataSource, you might create a new J2C security alias with it. In order for security alias to get effective, the server must be restarted.

Modifying the .properties file

The Database configuration is spread into three different configuration files so you will have to update all those configuration files.

wkplc_dbtype.properties

This configuration file defines the database properties such as Driver class name, JDBC Class path, etc. Configure it like this.

###############################################################################
# DB2 Properties
###############################################################################

# DbDriver: The name of class SqlProcessor will use to import SQL files
# For DB2 Type 2 driver use COM.ibm.db2.jdbc.app.DB2Driver
# For DB2 Type 4 driver use com.ibm.db2.jcc.DB2Driver
db2.DbDriver=com.ibm.db2.jcc.DB2Driver

# DbLibrary: The directory and name of the zip/jar file containing JDBC driver class
# For DB2 Type 2 driver use /java/db2java.zip
# For DB2 Type 4 driver use /java/db2jcc.jar;/java/db2jcc_license_cu.jar
# Please use the system specific file separator names, e.g. for windows semicolon and for unix colon.
db2.DbLibrary=/opt/ibm/tdsdb2V9.1/java/db2jcc.jar;/opt/ibm/tdsdb2V9.1/java/db2jcc_license_cu.jar

# JdbcProviderName: The name of jdbc provider to be used
db2.JdbcProviderName=wpdbJDBC_db2


wkplc_comp.properties

This file has detailed information for each of the database domains. You will have to enter following set of properties for each of the database domains. This is sample for release domain.

release.DbType=db2
release.DbName=WPSDB
release.DbSchema=release
release.DataSourceName= wpdbDS
release.DbUrl=jdbc:db2://localhost:50000/wpsdb
release.DbUser=db2admin
release.DbPassword=db2admin

Similarly enter values for other domains such as jcr,customization,community, likeminds,..

Executing Configuration task

Once the properties files are setup we will have to execute configuration tasks to first verify the data entered is correct or not and then to actually do the database transfer.

• First execute this command to verify the values that you entered in wkplc_dbtype.properties file
  
ConfigEngine.bat validate-database-driver -DTransferDomainList=release,customization,community,
jcr,feedback,likeminds

  Important Note: When i tried executing validate-database-driver configuration task with value of db2.DbLibrary equal to /opt/ibm/tdsdb2V9.1/java/db2jcc.jar;/opt/ibm/tdsdb2V9.1/java/db2jcc_license_cu.jar, i got invalid separate character. So i removed the ; and part after it and retried executing the validate-database-driver and it worked. After that i did revert back to the original or else the next validation fails
  


• Now verify the information that you entered in wkplc_comp.properties file by connecting to the data sources by executing this command
  
./ConfigEngine.sh validate-database-driver -DTransferDomainList=release,customization,community,jcr,feedback,likeminds

  


• Once both the verifications are completed stop both server1 and WebSphere_Portal server


• Now start the actual DB transfer configuration task by executing
  ConfigEngine.bat database-transfer -DTransferDomainList=release,customization,community,jcr,
feedback,likeminds
  

On my local machine with both Portal and DB2 on same machine this task took 30 minutes to complete.

Check the wp_profile\ConfigEngine\log\portal-database-transfer.log at the end to make sure that the database transfer was successful.

Database transfer - Configuration Wizard

The Portal Configuration wizard can be used to transfer data from the Apache Derby Database to the target database.

The Portal Installer allows to launch the Configuration wizard once portal install is complete, you can use that option or if you want you can go to wp_profile/PortalServer/wizard directory and launch wizard by executing configwizard.bat.

• Once the wizard is launched, you should get a screen like this, select Transfer data to other database and click next
  
  


• On the next screen, wizard will ask you to enter your administrative userid and password
  
  


• On the next screen it will ask you about which is your source directory, in my case i am shifting from default IBM Derby so it is IBM Derbry
  
  


• On the next screen it will ask you what is your target database type. I am moving data to my IBM DB2 on my local machine so it is IBM DB2
  
  


• On the next page it will ask you more information about the target database, such as where it is located, DB2 servers host name, port
  
  


• After that it will start asking you information specific to each of the portal domains, this is how my customization domain screen looks like
  
  


• On next few screen it will ask you same information for customization,jcr, likeminds and release database. At the end it will show you the summary screen like this
  
  

When you click Next on the last screen it will take the information that you entered on the wizard and generate parent.properties file in wp_profile/PortalServerwizard directory with that information. This is how my parent.properties file looks like

db2.DbDriver=com.ibm.db2.jcc.DB2Driver
db2.DbLibrary=C:/IBM/SQLLIB/java/db2jcc.jar;C:/IBM/SQLLIB/java/db2jcc_license_cu.jar
db2.JdbcProviderName=wpdbJDBC_db2
source.release.DbType=derby
release.DbType=db2
release.DbName=wpsdb
release.DbUser=db2admin
release.DbSchema=release
release.DataSourceName=wpdbDS_release
WasUserid=uid=wasadmin,o=defaultWIMFileBasedRealm
release.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
release.DbPassword=db2admin
WasPassword=wasadmin
source.likeminds.DbType=derby
likeminds.DbType=db2
likeminds.DbName=wpsdb
likeminds.DbUser=db2admin
likeminds.DbSchema=likeminds
likeminds.DataSourceName=wpdbDS_likeminds
likeminds.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
likeminds.DbPassword=db2admin
source.jcr.DbType=derby
jcr.DbType=db2
jcr.DbName=wpsdb
jcr.DbUser=db2admin
jcr.DbSchema=jcr
jcr.DataSourceName=wpdbDS_jcr
jcr.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
jcr.DbPassword=db2admin
source.feedback.DbType=derby
feedback.DbType=db2
feedback.DbName=wpsdb
feedback.DbUser=db2admin
feedback.DbSchema=FEEDBACK
feedback.DataSourceName=wpdbDS_feedback
feedback.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
feedback.DbPassword=db2admin
source.customization.DbType=derby
customization.DbType=db2
customization.DbName=wpsdb
customization.DbUser=db2admin
customization.DbSchema=customization
customization.DataSourceName=wpdbDS_customization
customization.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
customization.DbPassword=db2admin
source.community.DbType=derby
community.DbType=db2
community.DbName=wpsdb
community.DataSourceName=wpdbDS_community
community.DbUser=db2admin
community.DbSchema=community
community.DbUrl=jdbc:db2://localhost:50000/wpsdb:returnAlias=0;
community.DbPassword=db2admin


It will take this information and execute ConfigEngine.bat database-transfer -DparentProperties=wp_profiles/PortalServer/wizard/parent.properties command to start the configuration.

This process will take some time to complete

Portal data sharing

If your building an 24*7 Portal environment, or environment that can be brought down even for maintenance or upgrade then you will have to use the Golden Architecture defined by IBM.

IBM's Golden architecture defines that you should have two portal servers or two portal clusters, when you bring down one of the clusters for maintenance other takes care of serving user request.

This is how you can setup the database for Golden architecture.



As you can see each of the cluster would get its own release database but they will share customization and community databases with other clusters in the production.

If your clusters are in different location then you only have to replicate community and customization database between them.

How is portal data organized

Portal data is broken into four logical groups, each group has differnet users, charateristics and different rates of access and growht.

• Configuration data: This is data that defines the portal server setup such as database connections, object factories and deployment descriptor. This configuration data is usually constant over the uptime of a portal server node and is typically kept in property files on the portal servers hard disk and proptected by file system security. Most of this data is managed by the the WebSphere Application Server.



• Release data: This type of data defines all portal resource definitions, rules and rights. Ex. Portal pages, portlets, page hierarchy, access control rights related information. This type of data is typically not modified during production and need administrative rights to do so.Release data cannot be split or shared and administrator must make sure that the content of the release database is consistent across the different lines.



• Customization: This is typically only associated with a particular user but it is data that can be shared amongst portal server nodes. Example of customization data is private page or preferences of portlets. Since the data in the customization database applies to single user only, the ACL is greatly simplified.
  
  In an environment that consist of multiple lines of production, customization data is kept in a database that is shared across the lines of productions. Therefore the data is automatically in sync. across the lines of productions and no matter which line of production the user logs into, that customizations are still available to theme.



• Community: These are modified during production. This type of data includes items such as shared documents or application resources. Users and groups are allowed to modify or delete data. Community resources are protected by portal access control.
  
  Community data includes items such as Web Content Management (WCM), and the
  aforementioned Portal Document Manager (PDM). In other words, shared data that is not
  part of the release data.
  

What is portal database domains

When you install WebSphere Portal server by default it installs Apache Derby database on the target machine and all the portal related data such as Portlet Description, Page definitions, Personalization Rules, Documents are stored in this database.

Storing Portal data in Apache Derby database works if your using standalone development environment but if you want to use clustering or if you want to support more users you will have to migrate your data from the Apache Derby database to some other production quality database like IBM's DB2 or Oracle.

The WebSphere Portal allows you to split the database into different doamins. In this each domain contains data that is related to each other and has specific characteristics about growing. Following are the database domains

• Releaes


• Customization


• Community


• Releaes


• JCR


• Feedback


• Likeminds

Websphere provides you to very flexible options for managing these data. Either you can create one database and store all the data in it, in which case each of these domains would be stored in separate schemas or you can store say Release and Customization data in DB2 and rest of the data in Oracle.

On my local i am using one DB2 instance WPSDB and storing the different domains in there own schemas. Take a look at screen shot of DB2 Control Center

WebSphere Portal Version 6 introduced the notion of four distinct database domains called Release, Community, Customization, and JCR domain. Those database domains can be represented by individual database schema within the same database instance or by separate,distinct database instances. The four domains contain different kinds of data:

1. The Release database domain contains all portal configuration data that make up a portalinstallation in a specific version (or release) such as the portal page topology and the configuration data for individual portlets.


2. The Customization domain contains private user customization data such as implicitly derived pages or user specific portlet preferences.


3. The Community domain contains those pages and portlets that are used in the Composite Application Infrastructure (introduced with WebSphere Portal V6.0).


4. The JCR domain contains those portal resources that are stored within the Java Content Repository (JCR) such as documents, folders, policies, and templates, personalization rules