CentOS ConfigEngine Database Installation Portal Transfer WebSphere

Foolproof way of transferring WebSphere Portal From Derby to DB2

By on Friday, October 4, 2013
Ok. I have seen several pieces of documentation on this. Lots of tutorials out there. However all of the sites and tutorials do not have all the steps mentioned clearly. I am trying to make this as comprehensive as possible. Try this on a Trial or test Portal Server, preferably a VM with a Snapshot taken. This is to ensure that in case something goes pear shaped, you can always go back to the working snapshot.
For this exercise, I have two VM’s.
portale.svil.com contains the WebSphere Portal 8.0.0.7 Server
connections.svil.com contains the DB2 10.1 Database server
I am not covering the basic installation of DB2 or of Portal itself here. I will try and cross link these articles later. For the moment, I am assuming that you already have Portal and DB2 installed, especially that DB2 is installed on a Remote DB2 server instead of on the same server as Portal Server.
1. Copy the DB2 JDBC driver jar files from the DB2 server to the Portal server.
cd /opt/IBM/WebSphere/wp_profile/PortalServer
mkdir dbdrivers
cd dbdrivers
scp db2inst1@connections.svil.com:/opt/IBM/db2/V10.1/java/* .
Please Note: DB2 usually installed under the lowercase ibm folder like /opt/ibm. I have changed this to the Uppercase IBM like /opt/IBM for consistency. The above commands are run within the Portal server.
2. Take a backup copy of the properties files.
cd /opt/IBM/WebSphere/wp_profile/ConfigEngine/properties
cp wkplc_dbdomain.properties wkplc_dbdomain.properties.bak
cp wkplc_dbtype.properties wkplc_dbtype.properties.bak
cp wkplc.properties wkplc.properties.bak
3. On the DB2 Server issue the following commands under the instance owner id
db2 create database wpsdb alias wpsdb PAGESIZE 32 K
db2 create role WP_JCR_CONFIG_USERS
db2 create role WP_PZN_CONFIG_USERS
db2 create role WP_BASE_CONFIG_USERS
db2 GRANT DBADM ON DATABASE TO ROLE WP_JCR_CONFIG_USERSdb2 GRANT DBADM ON DATABASE TO ROLE WP_PZN_CONFIG_USERSdb2 GRANT DBADM ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
db2 GRANT ACCESSCTRL ON DATABASE TO ROLE WP_JCR_CONFIG_USERSdb2 GRANT ACCESSCTRL ON DATABASE TO ROLE WP_PZN_CONFIG_USERSdb2 GRANT ACCESSCTRL ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
db2 GRANT DATAACCESS ON DATABASE TO ROLE WP_JCR_CONFIG_USERS db2 GRANT DATAACCESS ON DATABASE TO ROLE WP_PZN_CONFIG_USERSdb2 GRANT DATAACCESS ON DATABASE TO ROLE WP_BASE_CONFIG_USERS
db2 GRANT SECADM ON DATABASE TO ROLE WP_JCR_CONFIG_USERSdb2 GRANT SECADM ON DATABASE TO ROLE WP_PZN_CONFIG_USERSdb2 GRANT SECADM ON DATABASE TO ROLE WP_BASE_CONFIG_USERS

db2 GRANT ROLE WP_JCR_CONFIG_USERS TO GROUP DB2IADM1db2 GRANT ROLE WP_PZN_CONFIG_USERS TO GROUP DB2IADM1db2 GRANT ROLE WP_BASE_CONFIG_USERS TO GROUP DB2IADM1
4. Edit the wkplc_dbdomain.properties file
  • Change all .DbType= to .DbType=db2
  • Change all .DbURL= to .DbUrl=jdbc:db2://connections.svil.com:50000/wpsdb:returnAlias=0;
  • Change all .DbUser= to .DbUser=db2inst1
  • Change all .DbPassword=ReplaceWithYourDbAdminPwd to .DbPassword=
  • Change all .DBA.DbUser= to .DBA.DbUser=db2inst1
  • Change all .DBA.DbPassword=ReplaceWithYourDBAPwd to .DBA.DbPassword=
5. Save and close the file
6. Edit the wkplc_dbtype.properties file
  • Change db2.DbLibrary= to db2.DbLibrary=/opt/IBM/WebSphere/wp_profile/PortalServer/dbdrivers/db2jcc4.jar;/opt/IBM/WebSphere/wp_profile/PortalServer/dbdrivers/db2jcc_license_cu.jar
7. Save and close the file
8. Edit wkplc.properties
Change WasPassword=ReplaceWithYourPassword to WasPassword=
Change LTPAPassword=ReplaceWithYourPassword to LTPAPassword=
Change PortalAdminPwd=ReplaceWithYourPwd to PortalAdminPwd=
9. Save and close the file
10. On the Database server , run the following commands
db2set DB2COMM=TCPIP
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_INLIST_TO_NLJN=YES
db2 “UPDATE DBM CFG USING query_heap_sz 32768”
db2 “UPDATE DBM CFG USING sheapthres 0”
db2 “CREATE DB wpsdb alias wpsdb using codeset UTF-8 territory us PAGESIZE 32 K”
db2 “UPDATE DB CFG FOR wpsdb USING applheapsz 4096”
db2 “UPDATE DB CFG FOR wpsdb USING app_ctl_heap_sz 1024”
db2 “UPDATE DB CFG FOR wpsdb USING stmtheap 32768”
db2 “UPDATE DB CFG FOR wpsdb USING dbheap 2400”
db2 “UPDATE DB CFG FOR wpsdb USING locklist 1000”
db2 “UPDATE DB CFG FOR wpsdb USING logfilsiz 4000”
db2 “UPDATE DB CFG FOR wpsdb USING logprimary 12”
db2 “UPDATE DB CFG FOR wpsdb USING logsecond 20”
db2 “UPDATE DB CFG FOR wpsdb USING logbufsz 32”
db2 “UPDATE DB CFG FOR wpsdb USING avg_appls 5”
db2 “UPDATE DB CFG FOR wpsdb USING locktimeout 30”
db2 “UPDATE DB CFG FOR wpsdb using AUTO_MAINT off”


db2 “CONNECT TO wpsdb USER db2inst1 USING “

db2 “CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K” 
db2 “CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K”
db2 “CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 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 “CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING (‘icmlsusrtspace4’) BUFFERPOOL ICMLSVOLATILEBP4”
db2 “UPDATE DB CFG FOR wpsdb USING DFT_QUERYOPT 2”
db2 “UPDATE DB CFG FOR wpsdb USING PCKCACHESZ 16384”
db2 “DISCONNECT wpsdb”
db2 “TERMINATE”
11. On the portale.svil.com server switch to the ConfigEngine directory and run the ConfigEngine.sh task
cd /opt/IBM/WebSphere/wp_profile/ConfigEngine
./ConfigEngine.sh validate-database-driver
./ConfigEngine.sh validate-database-connection

Only if the above 2 commands succeed, proceed to the next step

./ConfigEngine.sh setup-database
12. From the connections.svil.com server issue the following commands
cd ~
scp wasadmin@portale.svil.com:/opt/IBM/WebSphere/PortalServer/jcr/wp.content.repository.install/lib/wp.content.repository.install.jar .
scp wasadmin@portale.svil.com:/opt/IBM/WebSphere/wp_profile/PortalServer/jcr/config/registerCollationUDFTemplate.sql .
cd ~/sqllib/function
/opt/IBM/db2/V10.1/java/jdk64/bin/jar -xvf ~/wp.content.repository.install.jar icm/CollationUDF.class
13. Edit the ~/registerCollationUDFTemplate.sql and change all occurrences of to jcr.
14. Issue the following commands
db2 connect to wpsdb user db2inst1 using
db2 -tvf ~/registerCollationUDFTemplate.sql
db2 terminate
db2stop
db2start
db2 connect to wpsdb user db2inst1 using
values schema.sortkeyj('abc','en')
db2 terminate  
15. You should receive a single record for the above values schema.sortkeyj statement.
16. On the portale.svil.com server issue the following command
cd /opt/IBM/WebSphere/wp_profile/PortalServer/jcr/lib/com/ibm/icm
vi icm.properties
17. Edit the icm.properties file and add the following section to the end of the file
# Enable/Disable collation support for all DB2 platforms
# Disabled by default
jcr.query.collation.db2.enabled = false
 
# Database specific collation mappings
# These mappings apply map a Java locale name into a collation name
# supported by the underlying database.
# Example mappings for DB2 platform
 
# English
jcr.query.collation.en = en
 
# Swedish
jcr.query.collation.sv = sv
 
jcr.query.collation.zh = zh
jcr.query.collation.de = de
jcr.query.collation.da = da
jcr.query.collation.hu = hu
jcr.query.collation.jp = jp

18. Please NOTE:  I have purposefully disabled collation support. Because I want to go through the steps but not really enable collation support at this point in time.

19. Take a snapshot at this point in time for both the VM’s. If this go pear shaped we can restore back to this state and continue.

20. Start the VM’s again and ensure that the DB2 instances are started

21. Login to the portale.svil.com server and issue the following commands. Ensure Portal server is stopped.

cd /opt/IBM/WebSphere/wp_profile/ConfigEngine
./ConfigEngine.sh validate-database -DWasPassword=
cp ../PortalServer/jcr/lib/com/ibm/icm/icm.properties ~
./ConfigEngine.sh database-transfer

22. Verify that everything went well by editing the ConfigTrace.log file like

vi ./log/ConfigTrace.log

23.Connect to the connections.svil.com database server and perform a reorg check to improve performance. Issue the following commands

db2 connect to wpsdb user db2inst1 using 
db2 reorgchk update statistics on table all > xyz.out 
 
24. Edit the file and for each file that is marked with an asterix (*)
in the REORG Column run the following command:

db2 reorg table tablename
 
25. After you have run the reorg command for each tablename, 
run the following commands: 
 

db2 terminate
db2rbind database_name -l db2rbind.out -u db2_admin -p password

 
26. !!!!! BEFORE YOU START THE SERVER PERFORM THE FOLLOWING !!!!

cp /opt/IBM/WebSphere/wp_profile/PortalServer/dbdrivers/*.jar /opt/IBM/WebSphere/AppServer/lib


0
Related Posts