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:

WL_HOME\portal\db\db2

WL_HOME\portal\db\oracle

WL_HOME\portal\db\pointbase

WL_HOME\portal\db\sql_server

WL_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:

WL_HOME\portal\db\data\required\xx_insert_system_required_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.

Personalization DDL Modules

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

WL_HOME\common\p13n\db\db2

WL_HOME\common\p13n\db\oracle

WL_HOME\common\p13n\db\pointbase

WL_HOME\common\p13n\db\sql_server

WL_HOME\common\p13n\db\sybase

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

WL_HOME\common\p13n\db\data\required\xx_insert_system_required_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-2.

The database.properties File

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

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.

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.WL_HOME}/common/p13n/db/${database}/jdbc_index/jdbc.index,
${env.WL_HOME}/portal/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.WL_HOME}/portal/db/${database}/jdbc_index/CM/jdbc.index,
${env.WL_HOME}/portal/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

Scripts to Create an Additional Content Management Repository

If you need to create an additional content management repository, you use create_db.cmd/.sh. However, you must use a properties file that you have customized instead of the database.properties properties file. You must also update the jdbc.index files= setting to specify the content management SQL scripts to run in the file properties file you create. For more information, see the Content Management Guide.

Scripts to Manually Upgrade from Version 8.1 SP4 or SP5

If you did not use the WebLogic Upgrade Wizard to perform the database upgrade from 8.1 SP4 or SP5, 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 9.2, you can drop the tables associated with Compoze/Collaboration using the following script:

WL_HOME\portal\db\dbms_name\dep9_drop_tables.sql

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. The script to upgrade from the WebLogic Portal-specific RDBMS Authenticator to the WebLogic SQL Authenticator is:

WL_HOME\common\p13n\db\dbms_name\upgrade_fromdbmsauth_tosqlauth.sql

After you have upgraded to the WebLogic SQL Authenticator, you can drop the tables associated with the WebLogic Portal RDBMS Authenticator using the following script:

WL_HOME\common\p13n\db\dbms_name\dep9_drop_tables.sql