Database Scripts for creating Portal database on DB2

0 comments



For migrating WebSphere Portal database from Derby (default portal database) to other database like DB2 you need to first create the databases in DB2. Portal uses following 6 databases to store different kind of
information so the first step is to create the following databases in DB2.
1. Release
2. Community
3. Customization
4. JCR
5. Feedback
6. Likeminds


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 as a single script.If everything goes well, you will saw a successful message at the end.

Release database

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

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

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

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

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

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"

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

No comments:

Post a Comment

Recent Posts

Popular Posts

© 2011-2019 Web Portal Club