Transferring a Portal Database to DB2

Portal installs out of the box using Cloudscape (a.k.a. Apache Derby). Needles to say, this is not a scalable database, but is appropriate for demos. A RDBMS is very CPU and disk intensive and often memory intensive as well. Separating the db from the Portal is the first step in scaling a Portal environment up.

WebSphere Portal 6 comes with a limited use license of DB2 Enterprise Edition. I won't go into all the specifics, but will mention a few lessons learned. First, read the InfoCenter's instructions and follow them very carefully, then come back here for my addendums.

Some things that I have found doing a windows db2 transfer that differs from the InfoCenter:
1) DB2 does not like database names to be greater than 8 characters, thus the 'customization' and 'community' database names need to be shortened. You do not have to use the default names as listed in the examples! The InfoCenter fails to mention this 8 character limitation.


2)The version of DB2 that comes with Portal is 8.1 fixpack 12. There is a blurb in the documentation about fixpack 11. This is from the InfoCenter:

Note: If you are using IBM DB2 Universal Database™ Enterprise Server Edition Fix Pack 11 or 12, you must complete the following steps prior to database transfer. Failure to follow these steps will cause the database transfer to hang at the task action-process-constraints. If you are using DB2 client to connect to a remote DB2 server, make the following changes on the DB2 client.
  1. Locate the following file:
    • UNIX: /home/db2inst1/sqllib/cfg/db2cli.ini
    • Windows: db2home/sqllib/db2cli.ini
  2. Edit the file by adding the following to the end of the file:
    • For Fix Pack 11:
      [COMMON]
      DYNAMIC=1
      Note: An empty line is required after the dynamic=1 at the end of the file.
    • For Fix Pack 12:
      [COMMON]
      ReturnAliases=0
      Note: An empty line is required after the ReturnAliases=0 at the end of the file.
Now, a word of warning. You need to add BOTH of these to BOTH the client and server db2cli.ini files. The Portal manual database transfer WILL hang if you do not. I learned this the hard way.

3) If you are using Portal 6 for the iSeries, you can ONLY use DB2400 on the iSeries. This means you can't distribute the workload to another machine or LPAR. If you are using Portal on any other platform, you cannot use DB2400 on the iSeries. I have posted some other comments about Portal on the iSeries, so if you are an iSeries shop, these should be racking up in your head.

4) Most of the instructions can be summarized and put into batch files, which I found most helpful. I will post my batch files below so you can use them on your own installation. These are for remote transfer situations where the database server is on another physical machine than the portal (a great way to distribute the portal workload). Copy the contents into your own batch file in notepad. Then you run them from a DB2 Command window (run db2cmd from a command prompt, then call the batch file from the new window).

Change the names of the databases according to your tastes.


rem ------------------------------------------------------------------------------------
REM Change 'portal6' to the actual hostname of the Portal server (8 char preferred).
rem ------------------------------------------------------------------------------------
db2 update dbm cfg using tp_mon_name WAS
db2 update dbm cfg using spm_name "portal6"

db2set DB2_RR_TO_RS=yes
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_INLIST_TO_NLJN=YES
db2 "UPDATE DBM CFG USING query_heap_sz 32768"
db2 "UPDATE DBM CFG USING maxagents 500"
db2 "UPDATE DBM CFG USING sheapthres 50000"

rem ----------------Release------------------
db2 "CREATE DB release using codeset UTF-8 territory us COLLATE USING UCA400_NO 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 8192"
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 1000"
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"

rem ---------------Community---------------------------
db2 "CREATE DB commity using codeset UTF-8 territory us COLLATE USING UCA400_NO PAGESIZE 8192"
db2 "UPDATE DB CFG FOR commity USING applheapsz 4096"
db2 "UPDATE DB CFG FOR commity USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR commity USING stmtheap 8192"
db2 "UPDATE DB CFG FOR commity USING dbheap 2400"
db2 "UPDATE DB CFG FOR commity USING locklist 1000"
db2 "UPDATE DB CFG FOR commity USING logfilsiz 1000"
db2 "UPDATE DB CFG FOR commity USING logprimary 12"
db2 "UPDATE DB CFG FOR commity USING logsecond 20"
db2 "UPDATE DB CFG FOR commity USING logbufsz 32"
db2 "UPDATE DB CFG FOR commity USING avg_appls 5"
db2 "UPDATE DB CFG FOR commity USING locktimeout 30"

rem -------------------Customization------------------------
db2 "CREATE DB customiz using codeset UTF-8 territory us COLLATE USING UCA400_NO PAGESIZE 8192"
db2 "UPDATE DB CFG FOR customiz USING applheapsz 4096"
db2 "UPDATE DB CFG FOR customiz USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR customiz USING stmtheap 8192"
db2 "UPDATE DB CFG FOR customiz USING dbheap 2400"
db2 "UPDATE DB CFG FOR customiz USING locklist 1000"
db2 "UPDATE DB CFG FOR customiz USING logfilsiz 1000"
db2 "UPDATE DB CFG FOR customiz USING logprimary 12"
db2 "UPDATE DB CFG FOR customiz USING logsecond 20"
db2 "UPDATE DB CFG FOR customiz USING logbufsz 32"
db2 "UPDATE DB CFG FOR customiz USING avg_appls 5"
db2 "UPDATE DB CFG FOR customiz USING locktimeout 30"

rem ----------------JCR-------------------------------
db2 "CREATE DB jcrdb using codeset UTF-8 territory us COLLATE USING UCA400_NO 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 8192"
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 1000"
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"

rem ----------------WMM---------------------------
db2 "CREATE DB wmm using codeset UTF-8 territory us COLLATE USING UCA400_NO PAGESIZE 8192"
db2 "UPDATE DB CFG FOR wmm USING applheapsz 4096"
db2 "UPDATE DB CFG FOR wmm USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR wmm USING stmtheap 8192"
db2 "UPDATE DB CFG FOR wmm USING dbheap 2400"
db2 "UPDATE DB CFG FOR wmm USING locklist 1000"
db2 "UPDATE DB CFG FOR wmm USING logfilsiz 1000"
db2 "UPDATE DB CFG FOR wmm USING logprimary 12"
db2 "UPDATE DB CFG FOR wmm USING logsecond 20"
db2 "UPDATE DB CFG FOR wmm USING logbufsz 32"
db2 "UPDATE DB CFG FOR wmm USING avg_appls 5"
db2 "UPDATE DB CFG FOR wmm USING locktimeout 30"

rem --------------------Likeminds-------------------------
db2 "CREATE DB lmdb using codeset UTF-8 territory us COLLATE USING UCA400_NO 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 8192"
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 1000"
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"

rem ------------------Feedback----------------------
db2 "CREATE DB fdbkdb using codeset UTF-8 territory us COLLATE USING UCA400_NO 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 8192"
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 1000"
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"

rem ----On the DB2 server machine, set DB2COMM to TCP/IP by using the db2set command, as follows:-----
db2set DB2COMM=TCPIP

db2 "UPDATE DBM CFG USING svcename DB2"


Next, update the JCR database with the following script

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



Then you update the service files with the correct ports on both the client and server.
Finally, catalog the databases on the Portal server's db2client (if you changed the database names, then update them below as well):

db2set DB2COMM=tcpip
db2 "catalog tcpip node DB2INST remote db2.yourcompany.com server 50000"

db2 "catalog db release as reldba at node DB2INST"
db2 "catalog db commity as commdba at node DB2INST"
db2 "catalog db customiz as customdb at node DB2INST"
db2 "catalog db fdbkdb as fdbkdba at node DB2INST"
db2 "catalog db lmdb as lmdba at node DB2INST"
db2 "catalog db jcrdb as jcrdba at node DB2INST"
db2 "catalog db wmm as wmmdba at node DB2INST"
db2 "quit"
After this you should be able to connect to the DB2 database from portal. If you have trouble try telnetting to the db2 port (db2 connect to wmm user db2admin using db2admin). If you cant' connect, go back and check your db2 installation. Reinstall and redo the databases if you must.

Labels: , , , , ,