Create schemas and users for DB2 for iSeries

 

+
Search Tips   |   Advanced Search

 

 

Procedure

  1. Plan for DB2 for iSeries
  2. Create schemas and users for DB2 for iSeries (Current task)
  3. Transfer between databases manually
  4. Verify database connections for iSeries

 

Contents

  1. Overview
  2. Schema naming convention
  3. Prepare the CCSID value prior to creating schema
  4. Prepare database domain property values prior to creating schemas
  5. Create a schema on a local database
  6. Create a schema on a remote database
  7. Create user profiles
  8. Next steps

 

Overview

This topic includes information on manually creating DB2 schemas to work with WebSphere Portal on iSeries. Complete the steps in this topic prior to either creating a database manually, or transferring and connecting to a database manually or with the configuration wizard.

A schema provides a logical grouping of SQL objects. A schema consists of...

  • library
  • journal
  • journal receiver
  • catalog
  • data dictionary (optional).

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

The default schema names may be used with the product.

  • Length cannot exceed 10 characters

  • All alphanumerical characters are allowed ( "A" through "Z" and "1" through "0")

  • Invalid characters include:

    spaces null
    * "
    : >
    < |
    + ;
    ' ?

Verify valid schema names. Do not use a schema name which already exists on the local or remote system. Follow the documentation of the target database management system in order to define a valid schema name as restrictions apply.

The Create WebSphere Portal wizard will automatically check schema names for you.

 

Prepare the CCSID value prior to creating schema

Whether local or remote, a necessary preparation task is to change the Coded Character Set Identifier (CCSID) value to one that is supported by DB2 Universal Database for iSeries.

The default CCSID #65535 is not supported by DB2 Universal Database for iSeries.

Complete all of the following steps to set a valid CCSID for every existing database administrator user profile.

  1. Query the current system value of CCSID by typing...

    DSPSYSVAL SYSVAL(QCCSID)

    If the CCSID value shown is 65535 then continue with these steps. Otherwise these steps are not required.

  2. Type the following command, including the parentheses, once for every existing database administrator user profile, including the user ID currently logged on and running the installation:

        DSPUSRPRF USRPRF(usrprf)
    

    ...where usrprf is the database administrator user ID, for example, DB2ADMIN. This command displays the user's profile settings, including the CCSID value that may need to change. If the value is *SYSVAL, *HEX, or 65535, you are finished with this step and can continue to the next step, which is to display job settings. Otherwise, you are finished with this CCSID section.

  3. Prior to running a job in any given session, type DSPJOB OPTION(*DFNA) and scroll to find the default coded character set identifier value used by the database administrator profile running jobs. Write down that value if it is not 65535.

  4. For the database administrator user profile to be used to run jobs, type in the following change user profile command and specify the value you wrote down in the previous step, or a CCSID value based on the language to be used, for example 037 for English. The value cannot be 65535:

        CHGUSRPRF USRPRF(usrprf) CCSID(ccsid-value)
    

  5. Log off the system for the profile changes to take effect.

  6. Log back on before attempting to run any database configuration tasks.

  7. Repeat these commands for every existing database administrator user profile on every portal system and every remote database server machine.

 

Prepare database domain property values prior to creating schemas

Before configuring database schemas, edit database domain properties files...

wpconfig_dbdomain.properties Names and locations of the database schemas
wpconfig.properties Database user ID and the WAS password (WasPassword)

Supplying WasPassword is even needed prior to transferring and connecting to a database using the configuration wizard.

wpconfig_dbtype.properties Driver, library and JDBC for each database type

All properties files are located in...

portal_server_root/config

Open each properties file with a text editor, such as WordPad.

To modify each properties file locally on the iSeries system, from a 5250 session

EDTF 'portal_server_root/config/property filename.properties'

...where property filename is either...

  • wpconfig_dbdomain
  • wpconfig
  • wpconfig_dbtype

Use the backup-main-cfg-file task make a copy of each properties file before editing the file.

Change the database domain property values for names and locations of the database schemas in the wpconfig_dbdomain.properties file.

Do not remove *LOCAL from the property value. The properties listed here must be changed to create a database schema on a local or remote iSeries server. Depending on which database domain is to be configured, the variable dbdomain will be replaced by:

  • release
  • customization
  • community
  • jcr
  • wmm
  • feedback
  • likeminds

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

After you edit the property values in the appropriate file, save the file.

 

Create a schema on a local database

After installing WebSphere Portal, use...

WPSConfig.sh -profileName profile_root create-all-db

...to create the local or remote schemas on the DB2 for iSeries database.

Create the user profile to own the database before running configuration.

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

For ease of use, IBM recommends that you use a separate domain database schema name for each domain you set up in WebSphere Portal.

The default schema for WebSphere Portal is:

release *LOCAL/REL60
customization *LOCAL/CUST60
community *LOCAL/COMM60
jcr *LOCAL/JCR60
wmm *LOCAL/wmm.DBUser (replace wmm.DBUser with the value in wpconfig_dbdomain.properties file)
feedback *LOCAL/FDB60
likeminds *LOCAL/LKM60

To create the database schemas locally, perform the following steps:

  1. Start a 5250 session on the local machine where WebSphere Portal is installed.

  2. Type strqsh on the command line.

  3. Press Enter.

  4. Type on the command line cd portal_server_root/config

  5. Press Enter.

  6. Verify that you have changed the property values in the configuration properties files as detailed in the section above before continuing with the next step.

  7. Type on the command line:

    WPSconfig.sh -profileName profile_root create-all-db -Drelease.DbPassword=password -Dcustomization.DbPassword=password -Dcommunity.DbPassword=password -Djcr.DbPassword=password -Dwmm.DbPassword=password -Dfeedback.DbPassword=password -Dlikeminds.DbPassword=password

    ...where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal is installed. This command creates all the domain database schemas.

  8. Press Enter.

 

Create a schema on a remote database

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

  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 WebSphere Portal release domain is:

release.DbName=wpsdb

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

release.DbName=hostname/wpsdb

To create all the domain database schemas, perform the following steps:

  1. Start a 5250 session on the local machine where WebSphere Portal is installed.

  2. Create a Relational Database Directory Entry on the local system for the remote system using i5/OS command WRKRDBDIRE.

    Then add an entry with the following values:

    Relational database hostname (use the short TCPIP hostname of the remote system)
    Remote location domain qualified hostname (use the full TCPIP hostname of the remote system)
    Type IP
    Port number or service name DRDA
    Remote authentication method

    • Preferred method: ENCRYPTED
    • Allow lower authentication: ALWLOWER

  3. Temporarily change the CCSID for jobs to 37 (an example for US English environment)...

    CHGJOB CCSID(37)

  4. Create the required DB2 packages on the remote database machine by running the following command from the local machine:

    JAVA CLASS(com.ibm.db2.jdbc.app.DB2PackageCreator) PARM('rdb_entry' 'userid' 'password') PROP((jdbc.drivers 'com.ibm.as400.access.AS400JDBCDriver'))

    ...where...

    rdb_entry matches the name of the Relational Database Entry you created in step 2,
    userid is the database administrator user ID on the remote machine,
    password is the database administrator password on the remote machine.

    The output should be:

    Java program completed

  5. Press F3 to exit Java Shell Display.

  6. Sign off from the 5250 session.

  7. Start a 5250 session on the remote database machine.

  8. Verify the required DB2 packages were created by running the following command:

    WRKOBJ OBJ(QGPL/QSQCL*) OBJTYPE(*SQLPKG)
    The output should be:

         Opt Object  Type      Library     Attribute   Text                        
         QSQCLIPKGA  *SQLPKG   QGPL        PACKAGE                                 
         QSQCLIPKGC  *SQLPKG   QGPL        PACKAGE                                 
         QSQCLIPKGL  *SQLPKG   QGPL        PACKAGE                                 
         QSQCLIPKGN  *SQLPKG   QGPL        PACKAGE                                 
         QSQCLIPKGS  *SQLPKG   QGPL        PACKAGE
    

  9. Start a 5250 session on the local machine where WebSphere Portal is installed.

  10. On the command line type:

    strqsh

  11. Press Enter.

  12. On the command line, change directory to the UserData path by typing:

    cd portal_server_root/config

  13. Press Enter.

  14. Verify that you have changed the property values in the configuration properties files as detailed in the section above before continuing with the next step.

  15. On the command line type:

    WPSconfig.sh -profileName profile_root create-all-db -Drelease.DbPassword=password -Dcustomization.DbPassword=password -Dcommunity.DbPassword=password -Djcr.DbPassword=password -Dwmm.DbPassword=password -Dfeedback.DbPassword=password -Dlikeminds.DbPassword=password

    ...where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal is installed.

  16. Press Enter.

 

Create user profiles

The user profile for the database owner (the one specified in the wpconfig_dbdomain.properties file as DbUser and wmm.DbUser, for example) should be different from the Administrator user profile used to perform the installation. The Administrator user profile may have more authority than is required and usually belongs to an individual, where as the database 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, WebSphere Portal must be re-configured to use the new password.

 

Next steps

You have completed this step. Continue to the next step, Transfer DB2 for iSeries manually.

 

Parent Topic

Create databases and users