Database Administration Guide

Scripts and Properties Files

This appendix describes the SQL scripts and properties files that specify the settings for database creation and upgrade, and perform Database Definition Language (DDL) commands for WebLogic Portal and personalization data structures. It includes the following sections:

WebLogic Portal DDL Modules

WebLogic Portal DDL modules (SQL scripts) are provided in the following directories:

<WLPORTAL_HOME>\portal\db\db2

<WLPORTAL_HOME>\portal\db\mysql

<WLPORTAL_HOME>\portal\db\oracle

<WLPORTAL_HOME>\portal\db\pointbase

<WLPORTAL_HOME>\portal\db\sql_server

<WLPORTAL_HOME\portal\db\sybase

Each of these directories, except for pointbase, has an admin subdirectory, which contains the scripts that create the database user or database, and the appropriate database objects (such as tablespaces, bufferpools, and so on, depending on the database requirements).

Insert commands for bootstrap data that must be inserted into tables in each WebLogic Portal database are provided in the following directory:

<WLPORTAL_HOME>\portal\db\data\required\xx_insert_system_data.sql

WebLogic Portal DDL is provided in files named as follows:

xx_create_fkeys.sql

xx_create_indexes.sql

xx_create_tables.sql

xx_create_triggers.sql

xx_create_views.sql

xx_drop_constraints.sql

xx_drop_fkeys.sql

xx_drop_indexes.sql

xx_drop_tables.sql

xx_drop_views.sql

In these file names, xx is one of the prefixes listed Table A-1.

Table A-1 WebLogic Portal DDL Module File Prefixes
Prefix	Description
au	Anonymous user
comm	Communities
groupspace	GroupSpace
dep9	Deprecated Collaboration portlets
pf, pf9, pf10, and pf102	Framework and localization
wlcs	Deprecated; WebLogic Commerce Services (See Tip below)
wps	WebLogic Portal Services

Tip: In WebLogic Portal 10.2, the SQL scripts that automatically created the WebLogic Commerce Services database objects were removed from the jdbc.index files. Prior to Portal 10.2, the Configuration Wizard and the create_db script used these index files to create the database objects. You can manually add the WebLogic Commerce Services database objects in Portal 10.2 and later versions for the PointBase, Oracle, SQL Server, Sybase, DB2, and MySQL databases. Run the following scripts for the DBMS from the <WLPORTAL_HOME>/portal/db/<DBMS> directory: wlcs_create_tables.sql, wlcs_create_fkeys.sql, wlcs_create_indexes.sql, wlcs_create_views.sql, wlcs_create_triggers.sql, wlcs_insert_system_data.sql.

To remove deprecated WebLogic Commerce Services database objects from a database, run the following scripts: <WLPORTAL_HOME>/portal/db/<DBMS>/wlcs_drop_fkeys.sql and <WLPORTAL_HOME>/portal/db/<DBMS>/wlcs_drop_tables.sql.

The scripts with a 10 suffix contain new and updated DDL for WebLogic Portal 10.0. Scripts with a 102 suffix contain new and updated DDL for WebLogic Portal 10.2.

Content Management DDL Modules

Content Management DDL modules (SQL scripts) are provided in the following directories:

<WLPORTAL_HOME>\content-mgmt\db\db2

<WLPORTAL_HOME>\content-mgmt\db\mysql

<WLPORTAL_HOME>\content-mgmt\db\oracle

<WLPORTAL_HOME>\content-mgmt\db\pointbase

<WLPORTAL_HOME>\content-mgmt\db\sql_server

<WLPORTAL_HOME>\content-mgmt\db\sybase

Insert commands for bootstrap data that must be inserted into tables in each content management database are provided in the following directory:

<WLPORTAL_HOME>\content-mgmt\db\data\required\xx_insert_system_data.sql

Content Management DDL is provided in files named as follows:

xx_create_fkeys.sql

xx_create_indexes.sql

xx_create_tables.sql

xx_create_triggers.sql

xx_drop_constraints.sql

xx_drop_fkeys.sql

xx_drop_indexes.sql

xx_drop_tables.sql

In these file names, xx is one of the prefixes listed Table A-3.

Table A-2 Content Management DDL Module File Prefixes
Prefix	Description
cm, cm9, cm10, and cm102	Content management and GroupSpace
cmv and cmv9	Content management

Tip:

The scripts with a 10 suffix contain new and updated DDL to Content Management for WebLogic Portal 10.0. Scripts with a 102 suffix contain new and updated DDL to Content Management for WebLogic Portal 10.2.

Personalization DDL Modules

Personalization DDL modules (SQL scripts) are provided in the following directories:

<WLPORTAL_HOME>\p13n\db\db2

<WLPORTAL_HOME>\p13n\db\mysql

<WLPORTAL_HOME>\p13n\db\oracle

<WLPORTAL_HOME>\p13n\db\pointbase

<WLPORTAL_HOME>\p13n\db\sql_server

<WLPORTAL_HOME>\p13n\db\sybase

Insert commands for bootstrap data that must be inserted into tables in each personalization database are provided in the following directory:

<WLPORTAL_HOME>\p13n\db\data\required\xx_insert_system_data.sql

Personalization DDL is provided in files named as follows:

xx_create_fkeys.sql

xx_create_indexes.sql

xx_create_tables.sql

xx_create_triggers.sql

xx_drop_constraints.sql

xx_drop_fkeys.sql

xx_drop_indexes.sql

xx_drop_tables.sql

In these file names, xx is one of the prefixes listed Table A-3.

Table A-3 Personalization DDL Module File Prefixes
Prefix	Description
bt	Behavior tracking
dep9	Deprecated WebLogic Portal RDBMS Authenticator
er	Visitor entitlements and delegated administration
p13n, p13n9, and p13n102	Users, groups, and user profiles
seq	Sequencer

Tip:

There are no DDL changes to personalization for WebLogic Portal 10.0. Scripts with a 102 suffix contain new and updated DDL to Personalization for WebLogic Portal 10.2.

Using the database.properties File

Database scripts use the database.properties file for the following purposes:

To connect to the database
To drop, create, or alter database objects
To perform data inserts

You can find the database.properties file in any domain directory that contains WebLogic Portal. Database scripts, such as create_db.cmd and create_db.sh, use the database.properties file that is located in the same directory from which you start the script.

Setting the Database Parameters in the Properties File

Use the database.properties file to specify the database you plan to use with WebLogic Portal.

Note: The groupspace_database.properties file, used to create the GroupSpace repository database, is described in the next section.

The following is the section in the file where you choose your database vendor.

# Set database= to a valid database.

# Valid databases are:  pointbase, oracle, sql_server, sybase and db2

#----------------------------------------------------------------------

database=pointbase

In addition to modifying the database name, you must specify the appropriate values for the database user, password, host, and so on. You can use an encrypted password, as described in Encrypting Passwords. The following shows the PointBase and Oracle sections of the properties file.

#----------------------------------------------------------------------

#  For the database specified above, fill in the appropriate @DB_USER@

#  and @DB_PASSWORD@ settings and complete the url parameters by

#  setting:  @DB_HOST@, @DB_PORT@, @DB_NAME@

#  Note: @DB_NAME@ for sql_server and sybase is often the same as user

#  To use an encrypted password for any database use:

#    weblogic.security.Encrypt to obtain the encrypted password.

#  The saltFile used to encrypt/decrypt passwords is the domains

#    <DOMAIN_HOME>/security/SerializedSystemIni.dat saltFile.

#----------------------------------------------------------------------

pointbase.user=WEBLOGIC

pointbase.password=WEBLOGIC

pointbase.driver=com.pointbase.jdbc.jdbcUniversalDriver

pointbase.url=jdbc:pointbase:server://localhost:9093/weblogic_eval

#----------------------------------------------------------------------

oracle.user=@DB_USER@

oracle.password=@DB_PASSWORD@

oracle.driver=weblogic.jdbc.oracle.OracleDriver

oracle.url=jdbc:bea:oracle://@DB_HOST@:@DB_PORT@;SID=@DB_NAME@

#----------------------------------------------------------------------

You can specify a log file other than the default.

#----------------------------------------------------------------------

# logFile= the file that output will be logged to

#----------------------------------------------------------------------

logFile=create_db.log

The files= setting indicates which SQL scripts to execute. The default setting is appropriate for creating the main WebLogic Portal database.

#----------------------------------------------------------------------

# files= points to the jdbc.index files, which points to the .sql

#   files, to be executed by create_db

#----------------------------------------------------------------------

files=${env.WEBLOGIC_HOME}/common/p13n/db/${database}/jdbc_index/ jdbc.index, ${env.WLP_HOME}/db/${database}/jdbc_index/jdbc.index

You can also change the debug setting or send all SQL to a file rather than executing it, as described in the comments in the properties file. You can then execute the SQL file against the database.

Scripts to Create the GroupSpace Repository Database

You create the GroupSpace database repository with the create_db.cmd/.sh command, but using the groupspace_database.properties properties file instead of the database.properties properties file. The format is the same as the database.properties file, described in the previous section. The jdbc.index files= setting specifies a content management repository database with the appropriate SQL scripts to support GroupSpace:

files=${env.WEBLOGIC_HOME}/cm/db/${database}/jdbc_index/CM/jdbc.index,
${env.WLP_HOME}/db/${database}/jdbc_index/GROUPSPACE/jdbc.index

When you create the GroupSpace database, you must use the -database.properties= parameter to indicate the correct properties file to use, for example:

create_db.cmd -database.properties=groupspace_database.properties

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.

Scripts to Create an Additional Content Management Repository

If you need to create an additional content management repository, use create_db.cmd/.sh command with the cmrepo_database.properties file. For more information, see the Content Management Guide.

Scripts to Manually Upgrade from Version 8.1, 9.2, 10.0, or 10.2

If you did not use the WebLogic Upgrade Wizard to perform the database upgrade from 8.1, you must upgrade manually. See the Upgrade Guide for details on the upgrade process.

Scripts to Drop Deprecated Compoze Database Tables

After you have upgraded to version 10.0, you can drop the tables associated with Compoze (Collaboration). See the Upgrade Guide for instructions.

Scripts to Drop Deprecated RDBMS Authenticator Tables

If you do not upgrade your user store using the WebLogic Upgrade Wizard during the domain upgrade process, you can perform a manual upgrade later.

After you have upgraded to the WebLogic SQL Authenticator, you can drop the tables associated with the WebLogic Portal RDBMS Authenticator.

See the Upgrade Guide for instructions.