Database Administration Guide

Using a DB2 Database

This section describes the steps necessary to use a DB2 database with WebLogic Portal 8.1, and includes information on the following subjects:

Typically, the steps in this chapter should be performed by a database administrator.

Review this entire chapter and any release notes before proceeding.

Note: For additional database setup information, see "Managing WebLogic Platform Database Resources" at http://download.oracle.com/docs/cd/E13196_01/platform/docs81/db_mgmt/db_resource_mgmt.html.

Configuring a DB2 Database

Before proceeding, be sure that you have read Overview of Database Configuration for WebLogic Portal.

Install the DB2 client software and configure it to connect to the target DB2 database. See your DB2 documentation for more information.

Verify that you can connect to the target database through the Command Line Processor (CLP).

Prepare the DB2 database. The database creation scripts install domain-specific tables for each. It is recommended that you work with a database administrator to adjust the sample scripts, and to create the database objects (users, passwords, tablespaces, and so on) needed for your environment.

Notes: Multiple database schemas are required if you have multiple domains, or to run multiple environments using the same DB2 instance (for example, if you want to run development and system test from a single DB2 installation).

Be sure to back up your database before installing any new database objects. See your database documentation for details.

DB2 Configuration Parameter Minimum Settings

To ensure that the Portal application can successfully run on DB2, you must set some minimum configuration parameters. Without the minimum settings, heavy Portal activity might exceed database capacity.

Use the following minimum settings as a guideline as you configure your DB2 database:

Dynamic Sections: 20,000
applheapsz: 24,000
pckcachesz: 2,500

Review and modify the provided sample scripts to suit your environment. The scripts are located in WL_HOME/portal/db/db2/8/admin.

The following table lists the script names and the usage notes for each script.

Script Name

Description

create_user.sql

Grants createtab, bindadd and connect DB2 privileges to the WEBLOGIC schema owner user.

Usage Notes: Because IBM DB2 databases authenticate users using the operating system (OS), you need to create an OS user that owns database schema objects. Edit the script to change the schema owner user name.

The default schema owner user name and password are the following:

schema owner user: WEBLOGIC

schema owner user password: WEBLOGIC

create_bufferpool.sql

Creates an 8K bufferpool, if one does not already exist.

Usage Notes: You must stop and restart DB2 to utilize new bufferpools. Edit the script to change the 8K bufferpool name.

The default bufferpool is BP8K.

create_tablespaces.sql

Creates 4K and 8K regular tablespaces.

The default tablespace names are the following:

WEBLOGIC_DATA_4K: Tables for WebLogic Portal and/or WebLogic Platform with a rowsize smaller than 4K.

WEBLOGIC_DATA_8K: Tables for WebLogic Portal and/or WebLogic Platform with a rowsize larger than 4K and smaller than 8K.

Usage Notes: Edit the script to specify valid physical disk locations for your environment (d:\db2\data\data4k), for a database user other than WEBLOGIC and to change buffer pool names.

create_temp_tablespaces.sql

Creates an 8K temporary tablespace.

The default tablespace name is TEMPSPACE_8K.

Usage Notes: Edit the script to specify valid physical disk locations for your environment (d:\db2\data\data4k), for a database user other than WEBLOGIC and to change buffer pool names.

statistics_build.sql

Builds a file of runstats commands for each table that will compute database statistics needed for the database optimizer. Run runstats whenever any significant changes in database data occur. Your database administrator typically schedules runstats to run periodically in your environment.

install_report.sql

Builds an informational installation report about the database objects created in the WEBLOGIC schema.

bt_create_tablespace.sql

Creates the WEBLOGIC_EVENT_DATA tablespace.

Usage Notes: Edit the script to specify valid physical disk locations for your environment (event_container), and to use a buffer pool other than the IBM default buffer pool.

bt_create_users.sql

Creates the WEBLOGIC_EVENT schema owner user, establishes the user's password, default and temporary tablespaces, and grants privileges to that user.

Usage Notes: Edit the script to change the schema owner user name, password and tablespace names.

The default schema owner user name and password are the following:

schema owner user: WEBLOGIC

schema owner user password: WEBLOGIC

Script Name	Description
create_user.sql	Grants createtab, bindadd and connect DB2 privileges to the WEBLOGIC schema owner user. Usage Notes: Because IBM DB2 databases authenticate users using the operating system (OS), you need to create an OS user that owns database schema objects. Edit the script to change the schema owner user name. The default schema owner user name and password are the following: schema owner user: WEBLOGIC schema owner user password: WEBLOGIC
create_bufferpool.sql	Creates an 8K bufferpool, if one does not already exist. Usage Notes: You must stop and restart DB2 to utilize new bufferpools. Edit the script to change the 8K bufferpool name. The default bufferpool is BP8K.
create_tablespaces.sql	Creates 4K and 8K regular tablespaces. The default tablespace names are the following: WEBLOGIC_DATA_4K: Tables for WebLogic Portal and/or WebLogic Platform with a rowsize smaller than 4K. WEBLOGIC_DATA_8K: Tables for WebLogic Portal and/or WebLogic Platform with a rowsize larger than 4K and smaller than 8K. Usage Notes: Edit the script to specify valid physical disk locations for your environment (d:\db2\data\data4k), for a database user other than WEBLOGIC and to change buffer pool names.
create_temp_tablespaces.sql	Creates an 8K temporary tablespace. The default tablespace name is TEMPSPACE_8K. Usage Notes: Edit the script to specify valid physical disk locations for your environment (d:\db2\data\data4k), for a database user other than WEBLOGIC and to change buffer pool names.
statistics_build.sql	Builds a file of `runstats` commands for each table that will compute database statistics needed for the database optimizer. Run `runstats` whenever any significant changes in database data occur. Your database administrator typically schedules `runstats` to run periodically in your environment.
install_report.sql	Builds an informational installation report about the database objects created in the WEBLOGIC schema.
bt_create_tablespace.sql	Creates the WEBLOGIC_EVENT_DATA tablespace. Usage Notes: Edit the script to specify valid physical disk locations for your environment (`event_container`), and to use a buffer pool other than the IBM default buffer pool.
bt_create_users.sql	Creates the WEBLOGIC_EVENT schema owner user, establishes the user's password, default and temporary tablespaces, and grants privileges to that user. Usage Notes: Edit the script to change the schema owner user name, password and tablespace names. The default schema owner user name and password are the following: schema owner user: WEBLOGIC schema owner user password: WEBLOGIC

Start the CLP DB2 tool from the directory that contains the scripts.

From CLP, connect to the database that you want to work with. For example, type:

Db2 connect to database user username password password

From CLP, run create_bufferpool.sql, if needed. You might not need to create a new 8K bufferpool if you already have one to use. For example:

Db2 -tf create_bufferpool.sql -v

Restart your database instance.

From CLP, run create_temp_tablespaces.sql. For example:

Db2 -tf create_temp_tablespaces.sql -v

From CLP, run create_tablespaces.sql. For example:

Db2 -tf create_tablespaces.sql -v

From CLP, run create_user.sql. For example:

Db2 -tf create_user.sql -v

Follow the steps in Manually Creating Database Objects.

Manually Creating Database Objects

You can either manually create database objects or use the Configuration Wizard. For more information, see Overview of Database Configuration for WebLogic Portal.

Note: If you choose to use the WebLogic Configuration Wizard to configure and connect to the database that you will use to support WebLogic Portal, see http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

To manually create BEA Portal database objects, use the following steps:

From DB2-CLP, use the following command to verify that you can connect to the target database server with a valid user ID and password:

db2 connect to database user username password password

Open your domain's db_settings.properties file for edit and comment out the database settings for PointBase.

In the db_settings.properties file for your domain, uncomment the database settings for your new target database and update the following settings for your database:

server=
dblogin=
password=

Create the database.

For Windows, navigate to the BEA_HOME\user_projects\domains\portalDomain directory, and double-click the create_db.cmd file.

For UNIX, navigate to the BEA_HOME\user_projects\domains\portalDomain directory, run create_db.sh.

Verify the results in the create_db.log file.

Note: If you are using the sample domain, run the create_db.cmd/sh file from the following directory: WL_HOME\samples\domains\portal.

Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.

Manually Configuring Your Domain's JDBC Driver Settings

You can either manually configure your domains JDBC driver settings using the WebLogic Server Console, or use the Configuration Wizard. For more information., see Overview of Database Configuration for WebLogic Portal.

To manually configure your JDBC driver settings using WebLogic Server Console:

Start the WebLogic Server for your domain.

Configure your new connection pools.

Go to Services —> JDBC —> Connection Pools.

Click Configure a new Connection Pool.

Select the appropriate database type and non-XA database driver from the drop-down list boxes and click Continue. For more information, see the Supported Configuration documentation for JDBC drivers supported by WebLogic Portal located at http://download.oracle.com/docs/cd/E13196_01/platform/docs81/support/supp_plat.html#1085671.

For an XA configuration, see "Creating XA Domains Using Configuration Templates" in the "Creating WebLogic Configurations Using the Configuration Wizard documentation, http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

Choose a name for the new connection pool (for example, cgPoolN) and fill in the blanks for your vendor database. Click Continue.

Test your connection to verify that you can successfully connect to your database.

Create and deploy your new connection pool.

Note: You must maintain a one-to-one mapping of JDBCTxDataSource to JDBC connection pool in the domain's config.xml file. Create one new JDBC connection pool for each JDBCTxDataSource and another JDBC connection pool for the domain's JDBCDataSources.

Update your data sources.

From Services —> JDBC —> Data Sources, click each data source and switch each to the newly created connection pool. Be sure to apply each change.

Verify that each data source is changed by clicking on Data Sources and then verifying that Pool Name has been set to the new connection pool for each.

From Services —> JMS —> Stores —> cgJMSStore, switch cgJMSStore to use the new connection pool.

Stop your domain's WebLogic Server, then restart it.

In the WebLogic Server Console, delete the original connection pools.

Go to Services —> JDBC —> Connection Pools.

Right-click each connection pool and select Delete.

Creating a Separate Database for Behavior Tracking Events

For improved performance, you might want to store behavior tracking events in a different location from other WebLogic Portal database objects. For more information about behavior tracking, see http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/adminportal/help/SA_BehavTrackServ.html.

Note: By default, behavior tracking database objects are created in the same database as other WebLogic Portal database objects. You need to perform these steps only if you are configuring a separate database for behavior tracking events.

Edit the bt_create_tablespaces.sql file and the bt_create_users.sql file for your environment, as indicated in the instructions contained in the files. .

From CLP, run the bt_create_tablespaces.sql script. For example, type:

Db2 -tf bt_create_tablespaces.sql -v

From CLP, run the bt_create_users.sql script. For example, type:

Db2 -tf bt_create_users.sql -v

Navigate to the appropriate database directory based on your environment: WL_HOME\portal\db\db2\8

Connect as the user WEBLOGIC_EVENT and run the following scripts:

bt_create_tables.sql
bt_create_fkeys.sql
bt_create_indexes.sql
bt_create_views.sql
bt_create_triggers.sql

Run the following script from the path WL_HOME\portal\db\data\required:

bt_insert_system_data.sql

Configure a connection pool to access your behavior tracking database and associate the p13n_tracking JDBC data source with that connection pool. Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.