Saturday, 17 March 2012

Scripting the creation of the WebSphere Portal v7 databases

I'm following this Wiki article: -

Configure WebSphere Portal to use DB2

and these are the scripts that I'm using to create the databases via this command: -

$  db2 -tvf CreatePortalDB.sql

CreatePortalDB.sql

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


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

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


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


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


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


and then tune the JCR database as follows: -


$  db2 -tvf TuneJcrDB.sql

TuneJcrDB.sql



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


Note that the scripts are "purely" text files, created using vi, gedit, Notepad, TextEdit etc.

The important thing to note is that the Wiki article assumes that you're running the commands interactively, hence the prefix of: -

db2 "

As we're running this "in batch", there's no need to do that.

In addition, the suffix of: -

;

is the default line terminator for the DB2 command-line interface (CLI). Other terminators are available ......

For more information about the terminator and the db2 -tvf command, please see this post. 



I'll be back ....... :-)

2 comments:

xnomeansno said...

Hi David,
as referred in the comments here:
http://www-10.lotus.com/ldd/portalwiki.nsf/dx/Step-by-Step_Cluster_Guide_for_IBM_WebSphere_Portal_v7.0.0

In the JCR tuning sequence it's missed:
CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlsusrtspace4') BUFFERPOOL ICMLSVOLATILEBP4
and both bufferpools are suggested to be of twice the size in the official doc:
CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K

CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K

cheers
Giannandrea

Dave Hay said...

@Giannandrea - thanks for this, much obliged, will update my own internal docs.