Creating a DB2 database

You can create a DB2 database and all of its associated table containers manually.

Procedure

Create a database.
```
db2 "CREATE DATABASE database USING CODESET UTF-8 TERRITORY US" 
db2 "ALTER BUFFERPOOL IBMDEFAULTBP DEFERRED SIZE 10000 AUTOMATIC"
```
Note: If you use an existing database, ensure that the database was created using codeset UTF-8 and territory US, and that the IBMDEFAULTBP buffer pool has been altered as indicated.
Create buffer pools and table spaces.
These values are to be used by the WebSphere Commerce schema objects to optimize performance. You can use the recommendations below, or customize the values.
```
db2 "CREATE BUFFERPOOL BUFF8K DEFERRED SIZE 5000 AUTOMATIC PAGESIZE 8 K"
db2 "CREATE BUFFERPOOL BUFF16K DEFERRED SIZE 5000 AUTOMATIC PAGESIZE 16 K"
db2 "CREATE BUFFERPOOL BUFF32K DEFERRED SIZE 2500 AUTOMATIC PAGESIZE 32 K"

db2 "CREATE REGULAR TABLESPACE TAB8K PAGESIZE 8 K BUFFERPOOL BUFF8K"
db2 "CREATE REGULAR TABLESPACE TAB16K PAGESIZE 16 K BUFFERPOOL BUFF16K"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS8K PAGESIZE 8 K BUFFERPOOL BUFF8K"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS16K PAGESIZE 16 K BUFFERPOOL BUFF16K"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS32K PAGESIZE 32 K BUFFERPOOL BUFF32K" 
```
If you prefer to use your own buffer pool and table space customizations, you need to ensure:
- You have three buffer pools with page sizes of 8K, 16K and 32K respectively.
- You have two regular table spaces of 8K and 16K, respectively.
- Three system temporary table spaces of 8K, 16K and 32K, respectively.
You need to ensure that the WebSphere Commerce instance schema object creation files (SQL files) that rely on the table spaces have been modified to reference the names of your custom table spaces. Files that may be modified for this purpose include:
- WC_installdir/schema/db2/contract.sql
- WC_installdir/schema/db2/wcs.emailactivity.sql
- WC_installdir/schema/db2/wcs.schema.sql
- WC_installdir/schema/db2/wcs.summary.richAttribute.create.sql
- WC_installdir/schema/db2/wcs.summary.richAttributeCatgroup.create.sql
- WC_installdir/schema/db2/wcs.summary.standardPrice.create.sql
Depending on the feature pack level you have installed, modify one or more of the following files:
- WC_installdir/components/foundation/schema/fep1/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep2/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep3/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep4/db2/wcs.schema.foundation.sql
- WC_installdir/components/location-services/schema/fep4/db2/wcs.schema.location-services.sql
- WC_installdir/components/foundation/schema/fep5/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep6/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep7/db2/wcs.schema.foundation.sql
- WC_installdir/components/foundation/schema/fep8/db2/wcs.schema.foundation.sql
In each applicable SQL file, you must change any table space references so that they point to your custom table spaces. Feature packs are cumulative, so if you are on a later feature pack, files for lower-level feature packs will already be present on your system and will need to be modified.

Tune the database configuration and database manager configuration.

Use the settings in the following example as minimum guidelines:


db2 "UPDATE DATABASE CONFIGURATION FOR database USING LOCKLIST 2400 AUTOMATIC"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING INDEXREC RESTART"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING LOGFILSIZ 1000"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING LOGPRIMARY 12"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING LOGSECOND 10"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING PCKCACHESZ 4096 AUTOMATIC"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING CATALOGCACHE_SZ 4096"
db2 "UPDATE DATABASE CONFIGURATION FOR database USING LOCKTIMEOUT 45"

db2set DB2_WORKLOAD=WC
db2set DB2BIDI=yes
db2 "UPDATE DBM CFG USING CPUSPEED -1"

where database is the database name.

Note:

The LOGFILSIZ and LOGPRIMARY values might need to be higher in heavy-transactional environments. For example, in an authoring environment with active workspaces, these values might need to be higher to ensure that you do not encounter the error SQL0964C.
It is recommended to set the LOGFILSIZ to at least 2048.