Database Administration Guide

Using a Sybase Database

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

Review this entire chapter and any release notes before proceeding. The steps in this chapter should be performed by a database administrator.

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 Sybase Database

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

The following sections contain special considerations for defining your Sybase instance, as well as setup instructions.

Using a Supported Version

Be sure that you are using a supported version; see http://download.oracle.com/docs/cd/E13196_01/platform/docs81/support/supp_plat.html#1085671.

Defining an 8K Page Size

You must define a page size of at least 8K to support WebLogic Portal's use of wide tables, wide columns, and larger indexes. An 8K page size is the default for most databases. However, for Sybase the default page size is 2K, and Sybase does not allow rows to span pages.

If a Sybase instance is defined with a page size smaller than 8K, the WebLogic Portal tables will be created but warning messages might occur at creation time, indicating that the row size could exceed the row size limit. These warnings may result in run time exceptions, depending on the data being inserted or updated.

Indexes will fail to create if they are larger than the maximum page size for the Sybase instance. This could result in data issues as well as performance problems.

If your Sybase instance uses 2k or 4k pages, create a new Sybase instance with an 8K page size. Sybase provides a migration utility to migrate data between servers of different page sizes. You can find a technical white paper on the Sybase migration process at http://www.sybase.com/detail/printthis/1,6907,1021203,00.html.

Running Upgrade Script for 7.0 to 8.1

For WebLogic Portal 7.0 users who are upgrading to Version 8.1, ensure that the following WebLogic Portal 7.0 script has been run: bea\weblogic700\portal\db\sybase\125\migrate\migrate_to_125.sql

Install and Configure the Sybase Client

Install the Sybase client software on the WebLogic Platform host and do the following:

Configure the client so that it connects to the target Sybase instance.

Verify that you can connect to the target instance using isql. For example,

isql -Usa -Ppassword -SMySybase

Prepare the Sybase database. The database creation scripts install domain-specific tables. You should work with your database administrator to adjust the sample scripts, and to create the database schema owner users and devices needed for your environment.

Notes: Multiple databases are required if you have multiple domains, or to run multiple environments using the same Sybase instance (for example, if you want to run your test environment and production system from a single Sybase installation).

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

Review and modify the provided sample scripts to suit your environment. These scripts are provided in the WL_HOME/portal/db/sybase/125/admin directory.

Script Name	Description
create_devices.sql	Creates database devices. Usage Notes: Database devices must be created by a user with system administrator privileges (normally the `sa` user). `D:\DATAFILE`, `E:\LOGFILE`, and `F:\INDEXFILE` specifications in this script must be changed to reflect valid disk locations for your environment. Optimally, data, log, and index devices would be placed on separate physical disks that reside on separate controllers, unless you are using RAID devices. Edits are required to change file sizes and device names. The following default names are used: data device: `WEBLOGIC_DATA` log device: `WEBLOGIC_LOG` index device: `WEBLOGIC_INDEX`
create_database.sql	Creates the database and login. An alias is added to the database owner (dbo) user of the database. The devices created by `create_devices.sql` are used and a `WEBLOGIC_INDEX` is added. Usage Notes: Edit the script to reflect name or size changes from create_devices.sql. Edits are required to change the default database name and/or dbo user. The following defaults are used: data device: `WEBLOGIC_DATA` log device: `WEBLOGIC_LOG` database name: `WEBLOGIC` database owner user: `WEBLOGIC` password: `WEBLOGIC` If the database you are creating is a development database, your database administrator might want to uncomment and set the `truncate log on checkpoint` database option. If your application will use WebLogic Workshop page flows or RowSet controls, uncomment and set the `DDL` `in` `transaction` option to true to allow database table `create` commands to work properly.
statistics_build.sql	Builds `statistics.sql` to update table and index statistics for the database optimizer. Update statistics whenever any significant changes in database data occur. Your database administrator should schedule update statistics to run periodically in your environment.
install_report_build.sql install_report_static.sql	Builds an informational installation report about the database objects created by the `WEBLOGIC` user.
bt_create_devices.sql	Creates behavior tracking database devices. Usage Notes: Database devices must be created by a user with system administrator privileges (normally the `sa` user). `D:\DATAFILE` and `E:\LOGFILE` specifications in this script must be changed to reflect valid disk locations for your environment. Optimally, data and log devices would be placed on separate physical disks that reside on separate controllers. Edits are required to change file sizes and device names. The following default names are used: data device: WEBLOGIC_EVENT_DATA log device: WEBLOGIC_EVENT_LOG
bt_create_database.sql	Create the WEBLOGIC_EVENT database and WEBLOGIC_EVENT database owner user login. An alias is created to make WEBLOGIC_EVENT the database owner (dbo) user in the database. Usage Notes: Edit the script to change database names, the dbo user, and password. Edits are required to reflect valid disk locations for DATA and the LOG devices, or to adjust file sizes. Put DATA and LOG files on separate physical disks and away from any system database files. The following defaults are used: data device: WEBLOGIC_EVENT_DATA log device: WEBLOGIC_EVENT_LOG database name: WEBLOGIC database owner user: WEBLOGIC password: WEBLOGIC

Run create_devices.sql as a user with system administrator privileges. For example:
isql -Usa -SMYSYBASE -e -icreate_devices.sql -ocreate_devices.log

Run create_database.sql using isql as a user with System Administrator privileges (that is, the sa user):

isql -Usa -SMYSYBASE -e -icreate_database.sql -ocreate_database.log Output is written to the file specified after the -o parameter. The log file is stored in the same directory in which the script resides. Verify that each log file contains no errors for database object creation.

Statistics and install report scripts are run automatically by the create_db.cmd/.sh scripts. Ensure that your database administrator schedules statistics updates to run periodically for your WebLogic Portal database.

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 WebLogic Portal database objects, use the following steps:

Verify that you can connect to the target database. Use the following command syntax to verify that you can connect to the target database server using the default schema owner user created by running create_database.sql.

isql -UWEBLOGIC -SMYSYBASE

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

Uncomment the database settings for your new target database and update the following settings for your database:

server=
dblogin=
password=

Initialize the database with the new settings.

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

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, see Manually Creating Database Objects and JDBC Settings for more information.

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

Start the WebLogic Server for your domain.

Log on to the WebLogic Server Console.

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_devices.sql file and the bt_create_database.sql file for your environment, as indicated in the instructions contained in the files. .

Run bt_create_devices.sql using isql as a user with system administrator privileges. For example:

isql -Usa -SMYSYBASE -e -ibt_create_devices.sql -obt_create_devices.log

Run bt_create_database.sql using isql as a user with system administrator privileges. For example:

isql -Usa -SMYSYBASE -e -ibt_create_database.sql -obt_create_database.log

Navigate to the appropriate database directory based on your environment: WL_HOME\portal\db\sybase\125

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.