> Database Administration Guide > Using Sybase

Database Administration Guide

     Previous  Next    Open TOC in new window  Open Index in new window  View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Using Sybase

This chapter describes the steps necessary to use a Sybase database with WebLogic Portal, and includes the following sections:

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

 

Configuring Sybase Databases

Before proceeding, read Overview of Enterprise-Quality Database Configuration for WebLogic Portal.

The database creation scripts install domain-specific tables. It is recommended that you work with your database administrator to modify the sample scripts, and to create the database users and devices needed for your Sybase environment.

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 enterprise-quality system from a single Sybase installation). GroupSpace requires a separate database, as do any additional content management repositories.

Notes: You must define a page size of at least 8K to support WebLogic Portal's use of wide tables, wide columns, and larger indexes. For Sybase the default page size is 2K, and Sybase does not allow rows to span pages. 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.
Note: If a Sybase instance is defined with a page size smaller than 8K, the WebLogic Portal tables are created but warning messages may be issued at creation time, indicating that the row size could exceed the row size limit. These warnings may result in runtime 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.

To configure a Sybase database:

  1. Be sure to back up your database before installing any new database objects. See your database vendor documentation for details.
  2. Review the provided sample scripts, located in the <WLP_HOME>\db\sybase\admin directory. See Table 8-1, Database Scripts and Usage Notes, on page 8-7 and the comments in the scripts for additional information.
  3. Copy and modify the sample scripts appropriately for your environment to create each of the following databases:
    1. Follow the steps in Configuring the Main WebLogic Portal Database to create the main WebLogic Portal database and database objects.
    2. If you want to use GroupSpace, follow the steps in Configuring the GroupSpace Database to create the GroupSpace database and database objects.
    3. If you want to create a separate behavior tracking database, follow the steps in Manually Creating a Separate Database and Database Objects for Behavior Tracking.

 

Configuring the Main WebLogic Portal Database

To configure the main WebLogic Portal database, follow these steps:

  1. Copy the create_devices.sql script and modify it appropriately for your environment. See Table 8-1, Database Scripts and Usage Notes, on page 8-7 and the comments in the script for additional information.
  2. Run the modified create_devices.sql as a user with system administrator privileges (normally the sa user). For example, using isql:
    3. isql -Usa -SMYSYBASE -e -icreate_devices.sql -ocreate_devices.log
  3. Copy the create_database.sql script and modify it appropriately for your environment. See Table 8-1, Database Scripts and Usage Notes, on page 8-7 and the comments in the script for additional information.
  4. Run the modified create_database.sql as a user with system administrator privileges (normally the sa user). For example, using isql:
    5. isql -Usa -SMYSYBASE -e -icreate_database.sql -ocreate_database.log

    The output from running create_database.sql is written to create_database.log. Verify that there are no errors in the log file before proceeding.

Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Creating WebLogic Configurations Using the Configuration Wizard.

  1. Open your domain's database.properties file for edit.
  2. Set database=sybase.
  3. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:
    4. sybase.user=@DB_USER@
    sybase.password=@DB_PASSWORD@
    sybase.url=jdbc:bea:sybase://@DB_HOST@:@DB_PORT@;DatabaseName=@DB_NAME@
  4. Create the database objects. You can use the Configuration Wizard or follow these steps:
    1. Navigate to the BEA_HOME\user_projects\domains\myPortalDomain directory.
    2. Enter one of the commands:
      • On Windows, double-click create_db.cmd.
      • On UNIX, run create_db.sh.

        • If any error messages are displayed, check the create_db.log file for additional information.

        Note: After running create_db script, you need to update the database administrator password. See Note About Creating or Refreshing Database Objects for detailed information.
  5. Replace the JDBC data sources in your domain (except for appsGroupSpaceDataSource), which point to PointBase by default, with data sources that point to SQL Server. You can configure them using the WebLogic Server Administration Console or choose from the samples provided and update them for your database environment. Sample jdbc.xml definition files for each database and driver that BEA supports are available in the <WLP_HOME>\db\jdbc\database_driver directory; for example, sybase_bea. Follow the instructions in the <WLP_HOME>\db\jdbc\README.txt file.

 

Configuring the GroupSpace Database

To configure the GroupSpace database, follow these steps:

  1. Copy the create_devices.sql script and modify it appropriately for your environment. See Table 8-1, Database Scripts and Usage Notes, on page 8-7 and the comments in the script for additional information.
  2. Run the modified create_devices.sql as a user with system administrator privileges (normally the sa user). For example, using isql:
    3. isql -Usa -SMYSYBASE -e -icreate_devices.sql -ocreate_devices.log
  3. Copy the create_database.sql script and modify it appropriately for your environment. See Table 8-1, Database Scripts and Usage Notes, on page 8-7 and the comments in the script for additional information.
  4. Run the modified create_database.sql as a user with system administrator privileges (normally the sa user). For example, using isql:
    5. isql -Usa -SMYSYBASE -e -icreate_database.sql -ocreate_database.log

    The output from running create_database.sql is written to create_database.log. Verify that there are no errors in the log file before proceeding.

Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Creating WebLogic Configurations Using the Configuration Wizard.

  1. Open your domain's groupspace_database.properties file for edit.
  2. Set database=sybase.
  3. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your GroupSpace database:
    4. sybase.user=@DB_USER@
    sybase.password=@DB_PASSWORD@
    sybase.url=jdbc:bea:sybase://@DB_HOST@:@DB_PORT@;DatabaseName=@DB_NAME@
  4. Create the database objects.
    1. Navigate to the BEA_HOME\user_projects\domains\myPortalDomain directory.
    2. Enter one of the commands:
    • On Windows:
      • create_db.cmd -database.properties=groupspace_database.properties
    • On UNIX:
      • create_db.sh -database.properties=groupspace_database.properties

      If any error messages are displayed, check the create_db_groupspace.log file for additional information.

      Note: After running create_db script, you need to update the database administrator password. See Note About Creating or Refreshing Database Objects for detailed information.
  5. Replace the appsGroupSpaceDataSource JDBC data source (which points to PointBase by default) with a data source that points to Sybase. Use the WebLogic Server Administration Console or update the sample jdbc.xml definition file provided for each database and driver that BEA supports, in the <WLP_HOME>\db\jdbc\database_driver directory; for example sybase_bea. Follow the instructions in the <WLP_HOME>\db\jdbc\README.txt file.

 

Manually Creating a Separate Database and Database Objects for Behavior Tracking

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 Setting Up Events and Behavior Tracking in the Interaction Management Guide.

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.

To create a separate database for behavior tracking:

  1. 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 scripts and in Table 8-1, Database Scripts and Usage Notes, on page 8-7.
  2. Run bt_create_devices.sql as a user with system administrator privileges. For example, using isql:

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

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

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

  4. Navigate to the appropriate database directory based on your environment; for example, <WLP_HOME>\db\sybase.
  5. 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_triggers.sql
  6. Run the following scripts from the path <WLP_HOME>\db\data\required:
    • bt_insert_system_data.sql
    • bt9_insert_system_data.sql
  7. Use the WebLogic Server Administration Console to configure a non-XA JDBC data source to access your behavior tracking database. Associate the JNDI name p13n.trackingDataSource with that data source and then remove p13n.trackingDataSource from p13nDataSource.

 

Database Scripts and Usage Notes

Table 8-1 describes the scripts that enable you to configure the Sybase database.

Table 8-1 Database Scripts and Usage Notes 
Script Name
Description
create_devices.sql
Creates database devices. Database devices must be created by a user with system administrator privileges (normally the sa user).
  1. Make a copy of this script and edit it to replace <<WEBLOGIC>> with the appropriate database name and device names for each database you create.
  2. Edit the script to reflect valid disk locations for D:\DATAFILE, E:\LOGFILE, and F:\INDEXFILE device locations.
  3. Modify file sizes as needed depending on the WebLogic Portal functionality you are using. The following values for a production system are provided as a general guide, but you can adjust these values as needed (for example, an individual developer's database can have smaller files):

    4. log segment: 2048mb

    data segment: 2048mb

    index segment: 500mb

Optimally, locate data, log, and index devices on separate physical disks (with separate controllers) and away from any system database files, unless you are using RAID devices.

Note: Do not change the name of the WEBLOGIC_INDEX index segment.

Typical device names for the main WebLogic Portal database are:
  • Data device: WEBLOGIC_DATA
  • Log device: WEBLOGIC_LOG
Typical device names for the GroupSpace database are:
  • Data device: WEBLOGIC_GROUPSPACE_DATA
  • Log device: WEBLOGIC_GROUPSPACE_LOG
create_database.sql
Creates the database and login. You must create a main WebLogic Portal database. If you want to use GroupSpace, you must create that database. You must also create a database for each additional content management repository. The devices created by create_devices.sql are used and indexes are placed in their own WEBLOGIC_INDEX segment. An alias is added to the database owner (dbo) user of the database.
Make a copy of this script and edit it to replace <<WEBLOGIC>> with the appropriate database name, database user, and password for each database you create. Also adjust database and log sizes as appropriate.
If the database you are creating is a development database (and therefore database recovery is not a concern), you can uncomment and set the truncate log on checkpoint option to true.
If your applications will use WorkSpace Studio for create database or RowSet controls, uncomment and set the DDL in transaction option to true; otherwise create commands will not work properly.
Typical names for the main WebLogic Portal database are:
  • Database name: WEBLOGIC
  • Database schema user: WEBLOGIC
  • Password: WEBLOGIC

Note: Do not change the name of the WEBLOGIC_INDEX index segment.

When you run the script with these values, it creates the WEBLOGIC database, the WEBLOGIC_INDEX file group, and WEBLOGIC database owner (dbo) user login. An alias is created to make WEBLOGIC the dbo user in the database. It also sets the WEBLOGIC database as the default database for the WEBLOGIC user.
To use GroupSpace, you must create an additional database.
Typical names for the GroupSpace database are:
  • Database name: WEBLOGIC
  • Database schema user: WEBLOGIC
  • Password: WEBLOGIC

Note: If you decide to create an additional content management repository, you must create a database with different user names for it. See the Content Management Guide.
statistics_build.sql
Builds statistics.sql to update table and index statistics for the database optimizer. Update statistics periodically and whenever any significant changes in database data occur.
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. Database devices must be created by a user with system administrator privileges (normally the sa user).
You must also edit the script to reflect valid disk locations for D:\DATAFILE, E:\LOGFILE, and F:\INDEXFILE device locations; you may also need to modify file sizes. Optimally, locate data, log, and index devices on separate physical disks (with separate controllers) and away from any system database files, unless you are using RAID devices.
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.
Modify the script to reflect devices names bt_create_devices.sql. Also adjust database and log sizes for your site specific needs. Put DATA and LOG files on separate physical disks and away from any system database files.
The following defaults are used:
  • Database name: WEBLOGIC_EVENT
  • Database owner user: WEBLOGIC_EVENT
  • Password: WEBLOGIC_EVENT


  Back to Top       Previous  Next