Create schemas and users for DB2

 

Overview

A schema provides a logical grouping of SQL objects and consists of a library, a journal, a journal receiver, a catalog, and optionally, a data dictionary. Tables, views, and system objects (such as programs) can be created, moved, or restored into any system library.

 

Schema naming convention

When you create a schema, use the following schema naming conventions on the iSeries:

The default schema names may be used with the product. The IBM Web Administration for iSeries tool validates new schema names for you.

 

Create a schema on a local database

After you install WebSphere Portal, run...

WpsConfig.sh create-all-db
WpsConfig.sh basic-config

...to create the local or remote schemas .

You are not required to create the user profile to own the database before running configuration. Make sure you know what valid schema names are and do not use a schema name which already exists on the local or remote system. The tool will automatically check schema names for you.

You only need to manually configure the local and remote schemas for the product if you do not use the Portal scripts or the tool. Before creating local schemas, make sure you have installed all required software.

Use the IBM Web Administration for iSeries tool when configuring WebSphere Portal instances. The wizard creates the necessary servers (HTTP and WAS), configures the server for Portal, configures the database for Portal, configures security (LDAP) for Portal, and deploys the portlets installed with the WebSphere Portal product. You can edit the Portal instance configuration manually after you have used the WebSphere Portal wizard.

For ease of use, it is recommended that you follow these guidelines when you create database schemas for WebSphere Portal:

The default schema for WebSphere Portal is *LOCAL/QWPS50. The WebSphere Portal schemas are created in one library (QWPS50) to consolidate database ownership under one profile. These libraries will be owned by the user profile name specified during installation.

All database tables for WebSphere Portal, WebSphere Member Manager, and Portal Document Manger will be created in the default schema when the basic configuration (basic-config) steps are completed. After you have completed the security configuration steps, the database tables are updated.

Before you manual configure the database schemas, first edit...

/QIBM/UserData/WebAS5/Base/<instance>/PortalServer5/config/wpconfig.properties

...and configure the names and locations of the database schemas, as well as the user IDs. is located in the OS/400

Open the properties file with a text editor, such as WordPad. You can also modify the wpconfig.properties file locally on the iSeries system by typing the following on an OS/400 command line in a 5250 session: 

EDTF '/qibm/userdata/webas5/base/<instance>/portalserver5/config/wpconfig.properties'

Make a copy of the wpconfig.properties file before editing the file. Use the backup-main-cfg-file task.

Change the following property values in the wpconfig.properties file. The properties listed in the table must be changed to create a database schema on a local or remote iSeries server.

You must have a user profile on the iSeries server and must have at least *USE special authority to edit the properties file.

Value Description Example
DbUser The database administrator user ID. DbUser=MYNAME
DbPassword The database administrator password. DbPassword=abc123
WpcpDbUser The WPCP schema user ID. WpcpDbUser=MYNAME
WpcpDbPassword The WPCP schema user password. WpcpDbUser=abc123
FeedbackDbUser The Feedback schema user ID. FeedbackDbUser=MYNAME
FeedbackDbPassword The Feedback schema user password. FeedbackDbPassword=abc123
WmmDbUser The WMM schema user ID. WmmDbUser=MYNAME
WmmDbPassword The WMM schema user password. WmmDbPassword=abc123
PortalAdminIdShort The Portal administrator user ID. PortalAdminIdShort=MYADMIN
PortalAdminId The Portal administrator user ID. PoralAdminId=uid=MYADMIN, o=default organization
PortalAdminPwd The Portal administrator user password. PortalAdminPwd=xyz123

If you do not want to use the default schema names you can change the following properties:

Do not remove *LOCAL from the property value.

Value Description Example
WpsDbName

The name of the WebSphere Portal database.

This value should also appear as the database element in DbUrl.

WpsDbName=*LOCAL/WPS
WpsDbSchema The WebSphere Portal database schema name. WpsDbSchema=WPS
DbUrl The database URL. DbUrl=jdbc:db2:*LOCAL/WPS
WmmDbName

WmmDbName: The name of the WebSphere Portal database.

This value should also appear as the database element in WmmDbUrl.

WmmDbName=*LOCAL/WMM

WmmDbSchema The WMM database schema name WmmDbSchema=WMM
WmmDbUrl

WmmDbUrl: The database URL.

The database element of this value should match the value of WmmDbName.

WmmDbUrl=jdbc:db2:*LOCAL/WMM
WpcpDbName The name of the WPCP database. WpcpDbName=*Local/WPC
WpcpDbSchema The WPCP database schema name. WpcpDbSchema=WPC
WpcpDbUrl The WPCP database URL. WpcpDbUrl=jdbc:db2:*Local/WPC
FeedbackDbName The name of the Feeback database. FeedbackDbName=*LOCAL/QWPS50
FeedbackSchema The Feeback database schema name. FeedbackSchema=QWPS50
FeedbackDbUrl The Feedback database URL. FeedbackDbUrl=jdbc:db2:*LOCAL/QWPS50
PznDbName The name of the PZN database. PznDbName=*LOCAL/QWPS50
PznSchema The PZN database schema name. PznSchema=QWPS50
PznDbUrl The PZN database URL. PznDbUrl=jdbc:db2:*LOCAL/QWPS50
BrbDbName

The name of the BRB database.

BrbDbName=*LOCAL/QWPS50
BrbSchema The BRB database schaema name. BrbSchema=QWPS50
BrbDbUrl The BRB database URL. BrbDbUrl=jdbc:db2:*LOCAL/QWPS50

After you edit the property values, save the file.

To create the database schemas, start a 5250 session on the local machine where WebSphere Portal is installed, and run...

strqsh
cd /qibm/userdata/webas5/base/<instance>/portalserver5/config
wpsconfig.sh create-all-db

 

Passwords

For security reasons, not leave passwords in the wpconfig.properties file. It is recommended that you edit the wpconfig.properties prior to running a configuration task, inserting the passwords needed for that task. Then, after the task has run, delete all passwords from wpconfig.properties.

Alternatively, you can specify the password on the command line rather than update the wpconfig.properties file. For example:

WPSconfig.sh -DPortalAdminPwd=<password> validate-wps-admin-login

If you select the Install and Configure option when installing WebSphere Portal, or use the IBM Web Administration for iSeries tool, the passwords in the wpconfig.properties file are automatically removed after configuration.

 

Create a schema on a remote database

The same rules apply for a remote database and remote schemas with two exceptions.

Use the IBM Web Administration for iSeries tool when configuring WebSphere Portal instances. The wizard creates the necessary servers (HTTP and WAS), configures the server for Portal, configures the database for Portal, configures security (LDAP) for Portal, and deploys the portlets installed with the WebSphere Portal product. You can edit the Portal instance configuration manually after you have used the WebSphere Portal wizard.

  1. The user ID and password used must have the authority to create database schemas on the remote iSeries machine.

  2. For every property instance of a database that uses *LOCAL/<schema>, replace it with <HostName>/<schema>.

For example, the default database and schema name for WPS is:

WpsDbName=wps50

If you wanted to create this schema on a remote database, change the default value to this value:

WpsDbName=myserver.com/MYWPS50

The following table contains the property values change for remote database use.

Value Description Example
WpsDbName

The name of the WebSphere Portal database.

This value should also appear as the database element in DbUrl.

WpsDbName=mycompany.com/WPS
WpsDbSchema The WebSphere Portal database schema name. WpsDbSchema=WPS
WmmDbName

WmmDbName: The name of the WebSphere Portal database.

This value should also appear as the database element in WmmDbUrl.

WmmDbName=mycompany.com/WMM

WmmDbSchema The WMM database schema name WmmDbSchema=WMM
WmmDbUrl

WmmDbUrl: The database URL.

The database element of this value should match the value of WmmDbName.

WmmDbUrl=jdbc:db2:mycompany.com/WMM
WpcpDbName The name of the WPCP database. WpcpDbName=mycompany.com/WPC
WpcpDbSchema The WPCP database schema name. WpcpDbSchema=WPC
WpcpDbUrl The WPCP database URL. WpcpDbUrl=jdbc:db2:mycompany.com/WPC
DbDriver The name of class SqlProcessor will use to import SQL files, also known as "JDBC provider". DbDriver=com.co.system.access.AS400JDBCDriver
DbDriverDs The name of class SqlProcessor will use to import SQL files via data source. DbDriverDs=com.co.system.access.AS400JDBCConnectionPoolDataSource
DbUrl The database URL. DbUrl=jdbc:as400://mycompany.com/QWPS50
DbLibrary The directory and name of the zip file containing db.driver class DbLibrary=/QIBM/ProdData/PortalServer5/ co/lib/jr200.jar

DbDriver, DbDriverDs, DbUrl, and DbLibrary are also used for local databases.

After you edit the property values, save the file.

To create the WPS and WMM database schemas, perform the following steps:

The create-all-db configuration task only creates the WPS and WMM schemas. The other schemas are created by the basic-config configuration task.

strqsh
cd /qibm/userdata/webas5/base/<instance>/portalserver5/config
wpsconfig.sh create-all-db

 

Create User Profiles

The user profile for the database owner (the one specified in the wpconfig.properties file as DbUser, WmmDbUser, and WpcpDbUser, for example) should be different from the Admin user profile used to perform the installation. The Admin user profile may have more authority than is required and usually belongs to an individual, where as the DB user profile may have minimal authority and could be shared. You may also want to create a database user profile that does not require a password change over a period of time. If the password for the database user profile changes, Portal must be reconfigured to use the new password.

 

Related information