Transferring Portal Databases from default Derby Database to DB2 Databases

Portal is installed by default with Derby database to store portal information. Portal uses following 6 databases to store different kind of information:

1. Release

2. Community

3. Customization

4. JCR

5. Feedback

6. Likeminds

Portal provides following 3 options while creating and transferring the databases:

1. Create one database and transfer all the databases to this single database

2. Create separate databases on the same server and transfer the portal data to them

3. Create separate databases on different server (distributed environment) and transfer the portal data to them.

This post will demonstrate how to transfer the portal databases to separate databases on the same server, but you can use the same steps to transfer the database to one database or databases created on separate servers.

After configuring your database server(s) by installing DB2 (or Oracle/MS SQL Server) and creating databases using the DB2 creation script you need to run the following to transfer the databases:

<wp_profile_root>\PortalServer\wizard\configwizard.bat

Running the above batch command will open the following screen:

Click on “Next” to go to next screen.

Select “Transfer data to other databases” and click next to go to next step.

Provide WebSphere Application Server username and password to start WebSphere_Portal server. Click “Next” to go to next screen.

Wizard will check if server is started or not. If server is not started then it will start the server before moving to next screen.

Select “IBM Derby” from the options to select the source database. Click “Next” to go to next screen.

Select “IBM DB2 Universal Database” to select the target database. Click “Next” to go to next screen

Specify database server name or provide IP address in “DbHostName” and “DbPort”. Also specify complete path for “DbLibrary” files. You need to specify the location for the jar files “db2jcc.jar” and “db2jcc_license_cu.jar”. Click “Next” to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “community” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Wizard will validate the information provided by you before moving to next screen. You just have to wait for it to finish verification to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “customization” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “FEEDBACK” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “jcr” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “likeminds” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Specify “DbName”, “DbUser” (having admin access) and “DbPassword” for “release” database. You can specify other custom information as well according to your need. Click “Next” to go to next screen.

Check all the information in that you specified in this screen. If everything is correct then click “Next” to start the transfer of the databases. You will get successful message if everything goes well without any error.

You can now start and stop the servers to propagate the changes. If servers start without any errors and you are able to login successfully using GUI then you have successfully transferred the databases.

DB2 Database Creation Script for Portal Database Migration

For migrating WebSphere Portal databases from Derby to DB2 you need to first create the databases in DB2. Following scripts create all the databases required by the Portal on DB2. For executing these scripts open DB2 Command Window and copy and paste each of the commands mentioned in the script one by one or you can also copy and paste them in a bunch. If you receive a success message after every command of the scripts then you have completed the database creation properly.

---

Release database creation script

---

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

---

Community database creation script

---

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

---

Customization database creation script

---

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

---

JCR database creation script

---

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

---

Feedback database creation script

---

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

---

Likeminds database creation script

---

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

---

JCR database update script

---

You need to perform following additional steps on the JCR DB to match it with the Portal's requirements. replace & with the one you used to create the database.

db2 "CONNECT TO jcrdb USER userid USING pasword"
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"

---

Depending on the environment you are setting up you may need to tweak the scripts a little like increasing the pagesize, etc. to achieve the desired level of performance. You can migrate all the Portal DBs into one DB as well but its recommended to have each of them separately for better performance.

---

Custom Portlet Service

A WebSphere Portlet service is used to provide common functionality/services to portlets. WebSphere Portal provides many out of the box portlet services for different functionality like PUMA, authentication, etc.

JSR 168 portlets use JNDI lookup to obtain an instance of the required portlet service. A portlet service can only be invoked from inside a portlet.

Following are the steps to create a custom Portlet Service:
1. Create an interface which extends com.ibm.portal.portlet.service.PortletService interface and defines public methods which you want to expose through the service.
2. Implement the above interface along with com.ibm.portal.portlet.service.spi.PortletServiceProvider interface in your custom class.
3. Register the portlet service with Portal.

CODE for the above is as follows:

package com.pankaj.portlet.service;

import java.util.Map;

import com.ibm.portal.portlet.service.PortletService;

/**

* @author Pankaj Gupta

*

*/

public interface PortalUserCreationService extends PortletService {

public static final String JNDI_NAME = "portletservice/com.pankaj.portlet.service.PortalUserCreationService";

public static final Class serviceClass = com.pankaj.portlet.service.PortalUserCreationService.class;

public boolean createUser(String shortName, String parentDN, Map map);

}

Code Listing 1: Interface PortalUserCreationService

package com.pankaj.portlet.service.impl;

import java.util.Map;

import java.util.prefs.Preferences;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import com.ibm.portal.portlet.service.PortletServiceHome;

import com.ibm.portal.portlet.service.PortletServiceUnavailableException;

import com.ibm.portal.portlet.service.spi.PortletServiceProvider;

import com.ibm.portal.um.PumaController;

import com.ibm.portal.um.PumaHome;

import com.ibm.portal.um.User;

import com.ibm.portal.um.exceptions.PumaAttributeException;

import com.ibm.portal.um.exceptions.PumaMissingAccessRightsException;

import com.ibm.portal.um.exceptions.PumaModelException;

import com.ibm.portal.um.exceptions.PumaSystemException;

import com.ibm.websphere.cache.DistributedMap;

/**

* @author Pankaj Gupta

*

*/

public class PortalUserCreationServiceImpl implements PortalUserCreationService, PortletServiceProvider {

private Context ctx = null;

private PumaController pumaController = null;

private PumaHome pumaHome = null;

public boolean createUser(String shortName, String parentDN, Map map) {

boolean flag = false;

try {

User user = pumaController.createUser(shortName, parentDN, map);

flag = true;

}

catch (PumaAttributeException e) {

e.printStackTrace();

}

catch (PumaSystemException e) {

e.printStackTrace();

}

catch (PumaModelException e) {

e.printStackTrace();

}

catch (PumaMissingAccessRightsException e) {

e.printStackTrace();

}

return flag;

}

public void init(Preferences arg0) throws PortletServiceUnavailableException {

try {

ctx = new InitialContext();

pumaHome = (PumaHome)ctx.lookup(PumaHome.JNDI_NAME);

pumaController = pumaHome.getController();

}

catch (NamingException e) {

e.printStackTrace();

}

}

}

Code Listing 2: Implementation of the Listing 1 Interface

package com.pankaj.portlet.service.common;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import com.ibm.portal.portlet.service.PortletService;

import com.ibm.portal.portlet.service.PortletServiceHome;

import com.ibm.portal.portlet.service.PortletServiceUnavailableException;

/**

* @author Pankaj Gupta

*

*/

public class PortletServiceLocator {

//

public static PortletService getPortletService(String jndiName, Class serviceClass){

PortletServiceHome portletServiceHome = null;

PortletService portletService = null;

try {

Context ctx = new InitialContext();

portletServiceHome = (PortletServiceHome)ctx.lookup(jndiName);

portletService = (PortletService) portletServiceHome.getPortletService(serviceClass);

}

catch (NamingException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

catch (PortletServiceUnavailableException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return portletService;

}

}

Code Listing 3: Service Locator class which abstracts the service lookup.

DB2 DB creation script to create user repository DB for Portal

The following script creates a DB2 DB for storing user credentials. This DB will be used to create a custom user repository in DB2 and use the same to authenticate the portal users.

---

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

---

Replace the 'D:\', 'D:\IBM\wimDb' DBPATH ON 'D:\' with your own settings. D:\IBM\wimDb is the directory where DB data files will be stored. D:\ is the path under which DB2 directory will be created which will store DB configuration files.

---