+

Search Tips   |   Advanced Search


Set up a DB2 database


Install DB2

All DB2 instructions apply to DB2 pureScale except where specifically noted.

  1. On the DB host, update OS kernel parameters.

  2. Install DB2 or DB2 client.

    When you install DB2 it automatically creates a DB2 administrative user with the correct operating system rights.

  3. If required, based on the size of anticipated system, adjust upward the size and number of transaction logs.

  4. Set NUMDB to 30

    UPDATE DATABASE MANAGER CONFIGURATION USING NUMDB 30

  5. From DB2 host, copy driver jar files to portal host...

      cd /path/to/DB2_HOME/java/*.jar
      scp -r db2jcc4.jar db2jcc_license_cu.jar portalhost:/path/to/wp_profile/PortalServer/dbdrivers/

  6. On the DB2 system, ensure that...

      /etc/services

    ...contains...

      DB2_db2inst1 50000/tcp

    ...where db2inst1 is the DB2 instance name. If absent, manually add.

    Ensure that the port number used is not in use...

      netstat -a | grep portnumber


Modify DB2 database properties

Modify the approriate properties files before transferring the data from the default database to the DB2 database. Property files are read by ConfigEngine when tasks are executed. They are not read during Portal appserver startup. Multiple databases can be used to hold information for applications such as Feedback and LikeMinds.

For example:

  • The values for at least one of the following properties must be unique for the release, customization, community, and JCR domains:

    • dbdomain.DbName
    • dbdomain.DbUrl
    • dbdomain.DbSchema

    If we use the same values for all three properties across the release, customization, community, and JCR domains, the database-transfer task fails due to ambiguous database object names.

  • If DbUser, DbUrl, and DbPassword are not the same across domains, the value for DataSourceName must differ from the DataSourceName of the other domains. In other words, this value must be unique for the database domain.

    DbName cannot exceed 8 characters)

    For JDBC Type 2 drivers, perform additional steps.

    See the Type 2 configuration section for more instructions.

    1. Make backup copies of the following files:

      • WP_PROFILE/ConfigEngine/properties/wkplc.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_dbtype.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_sourceDb.properties

    2. Set properties in wkplc_dbdomain.properties

      1. dbdomain.DbType=db2

      2. dbdomain.DbName=domain_db_name

        • This value is also the database element in dbdomain.DbUrl.

        • TCP-IP alias for the database.

      3. dbdomain.DbSchema=domain_schema_name

        Some database management systems have schema name restrictions.

      4. dbdomain.DataSourceName=data_source_name

        Do not use the following reserved words:

        • releaseDS
        • communityDS
        • customizationDS
        • jcrDS
        • lmdbDS
        • feedback

      5. dbdomain.DbUrl=JDBC_DB_URL

        The value must conform to the JDBC URL syntax specified by the database.

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

      6. For dbdomain.DbUser, set the user ID for the database configuration user.

      7. For dbdomain.DbPassword, set the password for the database configuration user.

      8. For dbdomain.DbConfigRoleName, set the name of the group for database configuration users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbUser must be assigned to this group.

      9. Optional: For dbdomain.DbRuntimeUser, set the user ID of the database user that should be used by WebSphere Portal to connect to the database at runtime. If no value is specified for this setting, the database configuration user will be used to connect to the databases at runtime.

      10. If dbdomain.DbRuntimeUser is specified, set dbdomain.DbRuntimePassword to be the password of the runtime database user.

      11. For dbdomain.DbRuntimeRoleName, set the name of the group for database runtime users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbRuntimeUser must be assigned to this group.

      12. Optional: For dbdomain.DBA.DbUser.

        DB administrator user ID for privileged access operations during database creation and setup. Required.if you run the create-database and setup-database ConfigEngine tasks . If you do not need this parameter, we can either accept the default value or leave blank.

      13. Optional: For dbdomain.DBA.DbPassword, set the database administrator password for privileged access operations during database creation. If you do not need this parameter, we can either accept the default value or leave blank.

      14. For dbdomain.DbNode, type the value for the database node name. Set this value to call create-database.

        Required only for local databases.

    3. Save and close the file.

    4. Update the following properties in wkplc_dbtype.properties.

      db2.DbDriver Name of the JDBC driver class.
      db2.DbLibrary Directory and name of the .zip or .jar file containing the JDBC driver class.
      db2.JdbcProviderName Name of the JDBC provided that WebSphere Portal uses to communicate with its databases.

    5. Save and close the file.

    6. Update the WasPassword value in wkplc.properties.

      This value is the password for the WAS security authentication used in the environment.

    7. Save and close the file.


    Create groups and assign users

    Before transferring the databases to DB2 , create the users and groups specified in wkplc_dbdomain.properties and assign the users to their corresponding group. The user and group names must comply with both the database management system software requirements and WebSphere Portal requirements.

    1. If you have provided a value in wkplc_dbdomain.properties indicating that a runtime user should be used to connect to the database at runtime, create a user for...

        dbdomain.DbRuntimeUser

      When creating these users, use the same user ids and passwords entered in wkplc_dbdomain.properties.

    2. If you have provided a value in wkplc_dbdomain.properties for dbdomain.DbRuntimeRoleName, create a group for...

        dbdomain.DbRuntimeRoleName

    3. Assign the created user for dbdomain.DbUser to the created group for dbdomain.DbConfigRoleName.

    4. If dbdomain.DbRuntimeUser is specified, assign the created user for dbdomain.DbRuntimeUser to the created group for dbdomain.DbRuntimeRoleName.


    Create DB2 databases

    When created DB2 databases locally, we can create these databases using a configuration task or we can create these databases manually. When created the DB2 databases remotely, we can only create the databases manually.


    Create a local DB2 database automatically

    This section provides information on using ConfigEngine tasks to create databases when using a local DB2 installation. If we are using a remote DB2 installation, we cannot create databases using ConfigEngine. Create the databases manually.

    Before beginning, ensure that the following prerequisites are met:

    • The database management system software is installed.

    The create-database task cannot be run by a non-root user.

    1. Create the databases:

        cd WP_PROFILE/ConfigEngine
        ./ConfigEngine.sh create-database -DWasPassword=foo

    2. Check the services file on the DB2 server system. If it does not specify DB2 connection and interrupt service ports, specify the ports for the operating system.

      1. Edit /etc/services

      2. Add the text db2c_db2 50000/tcp, where db2 is the default instance.

        Ensure the port number used is not already in use. If 50000 is already is use, select a different port number.


    Create a remote or local DB2 database manually

    When we use a remote DB2 server, manually create the required databases.

    If we are using DB2 JDBC Type 4 driver you do not need to install the DB2 client software. Copy the Type 4 jar files from the remote DB2 server to the WebSphere Portal server.

      db2_home/java

    For Type 2 drivers, configure DB2 client to connect to the remote DB2 server instance, for example, db2inst1.

    To create a database, you must be a DB2 System Administrator with sufficient database privileges (SYSADM or at a minimum SYSCTRL).

    All DB2 instructions in this topic apply to DB2 PureScale except where specifically noted.


    Create a remote or local DB2 database manually

    1. Log in as a DB2 instance system authority.

      For example, we can log in as db2inst1 as the DB2 instance owner.

    2. Initialize a DB2 command environment.

      For example, execute...

        . /home/db2inst1/sqllib/db2profile

      db2inst1 is the DB2 instance owner of the DB2 instance.

        db2set DB2COMM=TCPIP
        db2set DB2_EVALUNCOMMITTED=YES
        db2set DB2_INLIST_TO_NLJN=YES
        db2 "UPDATE DBM CFG USING query_heap_sz 32768"
        db2 "UPDATE DBM CFG USING sheapthres 0"

    3. Create databases:

      • Replace dbname with the actual name of the database. Run the commands and each time replace dbname with the actual values for release, community, customization, Java Content Repository, Feedback, and Likeminds. You will need to run the commands once for each database for a total of six times.

        DB2 database names cannot exceed eight characters. Therefore, consider using these database names: release, commun, custom, jcrdb, fdbkdb, and lmdb. 

      db2 "CREATE DB dbname using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR dbname USING applheapsz 4096"
db2 "UPDATE DB CFG FOR dbname USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR dbname USING stmtheap 32768"
db2 "UPDATE DB CFG FOR dbname USING dbheap 2400"
db2 "UPDATE DB CFG FOR dbname USING locklist 1000"
db2 "UPDATE DB CFG FOR dbname USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR dbname USING logprimary 12"
db2 "UPDATE DB CFG FOR dbname USING logsecond 20"
db2 "UPDATE DB CFG FOR dbname USING logbufsz 32"
db2 "UPDATE DB CFG FOR dbname USING avg_appls 5"
db2 "UPDATE DB CFG FOR dbname USING locktimeout 30"
db2 "UPDATE DB CFG FOR dbname using AUTO_MAINT off"

    4. On the DB2 server systems to create bufferpools and table spaces for the IBM Java Content Repository database (jcrdb). This step is only required for the IBM Java Content Repository database.

      • jcrdb is the name of the database used to store user data and objects
      • jcr is the jcr user for jcrdb

        This value can be replaced with any Ithat has administrative authority.

      • dbpassword is the password for the jcr user for the jcrdb

      The commands in this step are different for DB2 and DB2 pureScale. Run the appropriate commands for the environment.

        DB2

        db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlsusrtspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

        DB2 pureScale

        db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

    5. For JDBC Type 2 drivers, perform additional steps.

      See the Type 2 configuration section for more instructions.


    Set up DB2 automatically or manually

    After creating the DB2 databases, we can set up the databases automatically using a configuration task or manually. The setup path that you choose after the DB2 database is created is independent of whether createdd the DB2 databases locally or remotely


    Run a task to automatically setup DB2 databases

    Create the database users, permissions, and table spaces:

      cd WP_PROFILE/ConfigEngine
      ./ConfigEngine.sh setup-database -DWasPassword=foo


    Set up DB2 manually


    If you have configured the database automatically by running ConfigEngine, you do not need to run manual tasks for granting privileges or creating table spaces, but you may decide to perform additional manual configuration for items that are not provided to you when you automatically when you run ConfigEngine.


    Create DB2 database schemas

    To create database schemas, we can create a copy of the template SQL scripts and edit this copy to manually create the database schemas. The template SQL scripts should be used as a guide for creating executable scripts and contain invalid SQL syntax.

    Before beginning: You should have completed Installing DB2 .

    For all databases, use one user with administrative rights on the operating system and the DB2 installation. This user can be the database administrative user created automatically by the DB2 installation program. This user is the database configuration user that will be used for configuration tasks: creating database tables and performing database transfer. T have WebSphere Portal also create the databases, the database user should be given SYSADM rights. You only need to manually create an administrator ID when you do not want to use an existing DB2 administrator ID.

    A common user name is db2inst1, but we can assign any user name as long as it has administrative access and follows the limitations listed here. Do not change the user name after creating it.

    The user and group names must comply with both the database management system software requirements and WebSphere Portal requirements. The limitations on user names are:

    • User names can contain one to eight characters.

    • Group and instance names can contain one to eight characters.

    • Names cannot be any of the following: 
      users
admins
guests
public
local

    • Names cannot begin with: 
      IBM
SQL
SYS

    • Names cannot include accented characters.

    • Create users in an environment that has the same settings as the actual runtime environment.

      For example, avoid creating a user in an English environment if you plan to use that user in a Turkish environment.

    Refer to the following locations to refer to the SQL script templates:

    Database domain Location of template
    Release PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createSchema.sql
    Community PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createSchema.sql
    Customization PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createSchema.sql
    JCR PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createSchema.sql
    Feedback PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createSchema.sql
    Likeminds PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createSchema.sql


    Grant privileges to DB2 database users

    Configuration and runtime database users are granted a different set of privileges, depending on whether these users are schema owners or not. We can create a copy of the SQL scripts and edit this copy to manually grant permissions to configuration and runtime database users.


    Required privileges of the configuration database user

    When a configuration database user is a schema owner, the property...

      domain.DbUser

    ...is assigned the same value as the property...

      domain.DbSchema

    ...and a role is created for a configuration user in each database domain.

    This role is created and assigned automatically when you run...

      ConfigEngine.sh setup-database

    As an alternative to creating and assigning this role automatically, we can create a copy of the SQL scripts templates located in the installation directory of IBM WebSphere Portal to use as a guide for creating executable scripts for manually granting permissions. These read-only templates should not be modified and contain invalid SQL syntax. Create our own version of these files to create runnable scripts.

    Permissions granted to the schema-owning configuration database user:

    Database domain Location of template
    Release PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createConfigRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/grantRoleToConfigUser.sql

    Community PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createConfigRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/grantRoleToConfigUser.sql

    Customization PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createConfigRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/grantRoleToConfigUser.sql

    JCR PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createConfigRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/grantRoleToConfigUser.sql

    Feedback PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createConfigRoleForSameSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/grantRoleToConfigUser.sql

    Likeminds PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createConfigRoleForSameSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/grantRoleToConfigUser.sql

    Non-schema-owning configuration database user:

    Database domain Location of template
    Release PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/grantRoleToConfigUser.sql

    Community PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/grantRoleToConfigUser.sql

    Customization PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/grantRoleToConfigUser.sql

    JCR PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/grantRoleToConfigUser.sql

    Feedback PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/grantRoleToConfigUser.sql

    Likeminds PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createConfigRoleForDifferentSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/grantRoleToConfigUser.sql


    Required privileges for the runtime database user

    When the runtime database user is a schema owner, the property...

      domain.DbUser

    ...is assigned the same value as the properties domain.DbRuntimeUser and domain.DbSchema. The runtime database user typically does not create tables used to query and manipulate data and does not by default have access to these tables. To grant minimum privileges to a runtime database user to work with these tables, access needs to be provided for the objects individually. A role is created for runtime database users in each database domain. These roles are created and assigned automatically when you run...

      ConfigEngine.sh setup-database

    before database transfer and later run grant-runtime-db-user-privileges configuration after database transfer. Before you run these configuration tasks, the runtime database user can only access the database to validate configurations. As an alternative to creating and assigning this role automatically, we can create a copy of the SQL scripts templates located in the installation directory of IBM WebSphere Portal to use as a guide for creating executable scripts for manually granting permissions. These read-only templates should not be modified and contain invalid SQL syntax. Create our own version of these files to create runnable scripts.

    Permissions granted to the schema-owning runtime database user:

    Database domain Location of template
    Release PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/grantRoleToRuntimeUser.sql

    Community PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/grantRoleToRuntimeUser.sql

    Customization PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/grantRoleToRuntimeUser.sql

    JCR PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/db2/jcr/grantPermissionsToRuntimeRoleStatic.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/grantRoleToRuntimeUser.sql

    Feedback PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createInitialRuntimeRole.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/grantRoleToRuntimeUser.sql

    Likeminds PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createInitialRuntimeRole.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createRuntimeRoleForSameSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/grantRoleToRuntimeUser.sql

    Permissions granted to the non-schema-owning runtime database user:

    Database domain Location of template
    Release PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/release/grantRoleToRuntimeUser.sql

    Community PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/community/grantRoleToRuntimeUser.sql

    Customization PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/customization/grantRoleToRuntimeUser.sql

    JCR PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createInitialRuntimeRole.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/db2/jcr/grantPermissionsToRuntimeRoleStatic.sql

    PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/db2/jcr/grantRoleToRuntimeUser.sql

    Feedback PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createInitialRuntimeRole.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/feedback/grantRoleToRuntimeUser.sql

    Likeminds PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createInitialRuntimeRole.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/createRuntimeRoleForDifferentSchema.sql

    PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/db2/likeminds/grantRoleToRuntimeUser.sql


    Related:
    Database users


    Assign custom DB2 table spaces

    The repository of WebSphere Portal consists of many tables and indicecreated in default table spaces. When using an existing set of table spaces for the objects of the repository, specify this when executing the database transfer to the target database system.

    Before beginning:

    • The custom table spaces must exist prior to the execution of database transfer.

    • To see which table spaces can be customized in each domain, reference...

        WP_PROFILE/PortalServer/config/tablespaces/dbdomain.space_mapping.properties

    • The page size of table spaces used by WebSphere Portal must be 8192 bytes.

    If custom table spaces are assigned, each must be assigned explicitly. The default table spaces can be used to contain database objects; however the name of the default table space must be specified in the corresponding mapping files. This applies to all database domains that are transferred in a single database transfer.


    Configure custom table space assignments

    1. Determine the names of the custom table spaces.

    2. Open the mapping file...

        WP_PROFILE/PortalServer/config/tablespaces/dbdomain.space_mapping.properties

        /ul> that specifies the table space and index space property pairs for each database table:

        • dbdomain.table_name.tablespace
        • dbdomain.table_name.index_name.indexspace

        For the file name and each table space and index space property pair, dbdomain can be any one of the following values:

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

        For jcr, edit mapping file:

          WP_PROFILE/PortalServer/jcr/config/jcr.space_mapping.properties

        ...which contains additional table space and index space property pairs for each jcr.table_name.tablespace database table.

      • Assign a table space to each entry in the mapping file. The table space name must be prepended by the keyword IN and a space.

        For example:

          community.COMP_INST.tablespace=IN COMM8KSPACE

        Repeat this step for each domain that we are transferring.

      • Save and close dbdomain.space_mapping.properties

      • From a command prompt, specify the option...

          -DuseCustomTablespaceMapping=true

        ...when starting the database transfer.

        For example,

          ./ConfigEngine.sh database-transfer -DuseCustomTablespaceMapping=true


    Configure JCR collation support

    JCR collation is recommended when the language locales of the users do not natively collate correctly in the DB2 database and when language locale correct ordering is important.

    1. Stop the WebSphere Portal server.

    2. Copy the following files from the WebSphere Portal server to a temporary directory on the DB2 server: 
      PortalServer/jcr/wp.content.repository.install/lib/wp.content.repository.install.jar

      wp_profile/PortalServer/jcr/config/registerCollationUDFTemplate.sql

    3. Set up collation on the database where the JCR domain is located.

      1. cd db2_instance_owner_home/sqllib/function

      2. Run the command:

        db2home/sqllib/java/jdk/bin/jar -xvf temporary location/wp.content.repository.install.jar icm/CollationUDF.class

      3. Change to the temporary directory where you copied the files in a previous step.

      4. Edit registerCollationUDFTemplate.sql and change all SCHEMA references to the JCR schema; for example, JCR.

        The value set for SCHEMA should match the value set for jcr.DbSchema in wkplc_dbdomain.properties .

      5. Connect to the JCR database...

          db2 connect to jcrdb user user_ID using password

      6. Run the script:

        db2 -tvf temporary location/registerCollationUDFTemplate.sql

      7. Disconnect from the JCR database.

      8. Restart the DB2 instance.

    4. Verify the UDF is registered properly.

      1. Log in as the db2instanceID.

        1. Open a DB2 terminal window.

        2. Run the following command...

            db2 connect to jcrdb user user_ID using password

          Connect to the JCR database as a database runtime user with the user ID and password for the WebSphere Portal JCR data source.

        3. Register the UDF: values schema.sortkeyj('abc','en')

    5. Edit...

        WP_PROFILE/PortalServer/jcr/lib/com/ibm/icm/icm.properties

      Add the following section to the end of the file: 

        # Enable/Disable collation support for all DB2 platforms   # Disabled by default   jcr.query.collation.db2.enabled = true 
  # Database specific collation mappings   

# These mappings apply map a Java locale name into a collation name   
  # supported by the underlying database.
  # Example mappings for DB2 platform 
  # English   jcr.query.collation.en = en
    # Swedish   jcr.query.collation.sv = sv

  jcr.query.collation.zh = zh
  jcr.query.collation.de = de
  jcr.query.collation.da = da
  jcr.query.collation.hu = hu
  jcr.query.collation.jp = jp

    6. Start the WebSphere Portal server.


    Manual DB2 transfer

    1. To run tasks as a non-root user...

        chown -R non-root_user /path/to/my/WebSphere/AppServer

    2. If there are a lot of tables in JCR schema, increase open_cursors from default of 1500.

    3. There are additional steps for JDBC Type 2 drivers

    4. For multiple instances of WebSphere Portal, increase default configured number of databases...

        set client MAX_NETBIOS_CONNECTIONS 254

      A message indicates success if the number was increased.

    5. Validate database configuration properties...

        cd WP_PROFILE/ConfigEngine
        ./ConfigEngine.sh validate-database -DWasPassword=foo

      Add -DTransferDomainList to the validating task to specify the domains to validate. For example...

        -DTransferDomainList=jcr

      To validate all domains, do not specify this parameter.

    6. Stop the WebSphere_Portal server:

        cd WP_PROFILE/bin
        ./stopServer.sh WebSphere_Portal -username wpadmin -password foo

    7. Transfer the database:

      Do not execute the database-transfer task as a background process. This might cause the task to stall.

        cd WP_PROFILE/ConfigEngine
        ./ConfigEngine.sh database-transfer -DWasPassword=foo

      Set -DTransferDomainList to include only the domains to transfer. For example, to transfer only the JCR domain...

        ./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=foo

    8. If you have been storing data in Apache Derby for a long time, database transfer could fail with OutOfMemory exceptions. To fix:

        ConfigEngine.bat database-transfer -DDbtJavaMaxMemory=1536M -DWasPassword=foo

    9. Log output is written to:

        WP_PROFILE/ConfigEngine/log/ConfigTrace.log

      If the configuration fails, verify the values in the wkplc.properties, wkplc_dbdomain.properties, and wkplc_dbtype.properties files and then repeat this step.

    10. If dbdomain.DbRuntimeUser is set, for the user...

      • Grant database user privileges manually:

        1. Copy SQL template files in...

          • PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/dbms/domain
          • PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/dbms/domain

          ...to a working directory.

          If the name of the database user and the schema name are not the same, copy...

            createRuntimeRoleForDifferentSchema.sql

          If the name of the database user and the schema name are the same, copy...

            createRuntimeRoleForSameSchema.sql

          ...where dbms is the database system, and domain is database domains (release, customization, community, jcr, feedback, and likeminds).

          For the JCR database domain, copy grantPermissionsToRuntimeRoleStatic.sql from...

            PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/dbms/jcr/

        2. Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.

        3. Execute the SQL statements.

      • Grant database user privileges automatically:

        1. Set domain.DBA.DbUser in...

            WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties

          For example...

            domain.DBA.DbUser=dbadmin

        2. Grant privileges...

            ./ConfigEngine.sh grant-runtime-db-user-privileges -DTransferDomainList=release,customization,community,etc...

          You only need to add -DTransferDomainList when granting privileges across specific domains.

    11. For each database, to improve performance, perform a reorg check.

        db2 connect to dbName user db2wpadmin using password

      db2 reorgchk update statistics on table all > xyz.out

      Look in the reorg column for entries marked with a star or asterisk * in the file xyz.out.

      1. For each line with *, note the tablename and run the following command for each tablename:

          db2 reorg table tablename

      2. After you have run the reorg command for each tablenames:

          db2 terminate
          db2rbind database_name -l db2rbind.out -u db2_admin -p password

        The file db2rbind.out is created when there is an error.

    12. Start the WebSphere Portal server.

        cd WP_PROFILE/bin
        ./StartServer.sh WebSphere_Portal

    13. On each configured node, make sure the following file is identical to the same file on the primary node...

        WP_PROFILE/PortalServer/jcr/lib/com/ibm/icm/icm.properties

      If the files are not identical, copy icm.properties from the primary node, the one one which you ran the database-transfer, to the node(s) with the obsolete icm.properties file..

      1. Stop the portal server on the secondary nodes.

      2. From the primary node...

          scp /path/to/PortalServer/jcr/lib/com/ibm/icm/icm.properties user@secondary_node:/path/to/PortalServer/jcr/lib/com/ibm/icm/

      3. Start the portal server on the secondary nodes.


    Enable DB2 pureScale load balancing feature

    For multithreaded Java applications, set enableSysplexWLB to true in the connection string to take advantage of transaction level workload balancing feature for DB2 pureScale. As additional members are started, clients automatically route to the new member without any interruption of service. Members can be stopped without the application knowing.

  • Open the dmgr console...

      http://your_server:9060/ibm/console/

  • Click Resources > JDBC > Data Sources.

  • Click Data sources > data source name > Custom properties.

  • Search for enableSysplexWLB.

  • Click enableSysplexWLB, and enter true in the Value field.

  • Click OK.


    Configure DB2 for large file handling in WCM

    If we are using Web Content Manager, update the database configuration to support large files. We can update the fullyMaterializeLobData property by running a configuration task.

    You only need to perform these steps if we are using Web Content Manager.

      cd WP_PROFILE/ConfigEngine
      ./ConfigEngine.sh datasource-enable-fully-materialize-lob-data -DWasPassword=foo


    Type 2 driver support

    If we are currently using Type 4 drivers, follow the instructions in this section to change drivers from Type 4 to Type 2 drivers.

    WebSphere Portal requires the use of either the IBM DB2 Legacy JDBC driver in type 2 mode or the IBM DB2 Universal JDBC driver in type 4 mode when connecting to DB2.

    Before beginning, ensure that the following conditions are met:

    • The WebSphere Portal database has been successfully transferred to DB2 using the database-transfer configuration task.

    • The files wkplc_dbdomain.properties and wkplc_dbtype.properties have been modified to set the correct values for the DB2 drivers that we are switching to:

      In the file wkplc_dbdomain.properties set each <Domain>.DbUrl property using the following formats: 

      # db2 (type 2):        { jdbc:db2:wpsdb }
# db2 (type 4):        { jdbc:db2://<YourDatabaseServer>:50000/wpsdb:returnAlias=0; }

      In the file wkplc_dbtype.properties set the db2.DbLibrary property using the following format: 

      # For DB2 Type 2 driver use <SQLLIB>/java/db2jcc4.jar
# For DB2 Type 4 driver use <SQLLIB>/java/db2jcc4.jar:<SQLLIB>/java/db2jcc_license_cu.jar

      In the file wkplc_dbtype.properties set the db2.DbDriver property using the following format: 

      # For DB2 Type 2 driver use com.ibm.db2.jcc.DB2Driver
# For DB2 Type 4 driver use com.ibm.db2.jcc.DB2Driver

    If WebSphere Portal is installed on the same machine as the DB2 server and you switch from a JDBC Type 4 connection to a JDBC Type 2 connection, verify that you have created the alias names for the DB2 databases as described in Create remote databases and that the alias names are specified for the databases in the file wkplc_dbdomain.properties.

    When switching from a JDBC Type 2 connection to a JDBC Type 4 connection, remove the database alias names and refer to the databases directly. This is required because of a limitation in the DB2 Universal JDBC driver.

    1. Export the DB2 user profile that createdd when installing DB2 onto the administrative user. This command exports the DB2 user's profile onto the administrative user so that they can access the DB2 utilities.

        . /home/db2inst1/sqllib/db2profile

      ...where db2inst1 represents the database instance

      Complete this step before running database tasks and before enabling security.

    2. Validate configuration properties...

        cd WP_PROFILE/ConfigEngine
        ./ConfigEngine.sh validate-database -DWasPassword=foo

      Add -DTransferDomainList to the validating task to specify the domains to validate; for example...

        -DTransferDomainList=jcr

      To validate all domains, do not specify this parameter.

    3. Stop the WebSphere_Portal server:

        cd WP_PROFILE/bin
        ./stopServer.sh WebSphere_Portal -username wpadmin -password foo

    4. To change from one supported driver to the other, run the following task to connect the database, including only the domains that require the switch. 

       
cd WP_PROFILE/ConfigEngine
./ConfigEngine.sh connect-database \
                  -Drelease.DbPassword=foo \
                  -Dcustomization.DbPassword=foo \
                  -Dcommunity.DbPassword=foo \
                  -Djcr.DbPassword=foo \
                  -Dfeedback.DbPassword=foo \
                  -Dlikeminds.DbPassword=foo \
                  -DWasPassword=foo 



       
    5.  Start the WebSphere Portal server.  


        
cd WP_PROFILE/bin

        ./startServer.sh WebSphere_Portal


    Configure Type 2 DB2 drivers

    This topic contains the instructions for a Type 2 configuration when the instructions differ from the Type 4 instructions. For general prerequisites and notes for each section, see the Type 4 version of topics using the same titles as the section headers. All topics in the DB2 section that are not in the Type 2 driver support section, are for a Type 4 configuration.

    Install DB2

    1. Install DB2 or DB2 client.

    2. If DB2 is installed on another system than WebSphere Portal, ensure the appropriate DB2 client is installed on the same system as WebSphere Portal and have the same name as the server profile name.

    3. On the DB2 system, verify the DB2 instance port was added to the services file during the DB2 installation.

      • Edit /etc/services

      • Ensure that...

          DB2_db2inst1 50000/tcp

        ...where db2inst1 is the DB2 instance name, is in the services file. If you do not see DB2_db2inst1 50000/tcp in the services file, add this entry to the services file.

        Ensure that the port number used is not in use. If the port number is already in use, select a different port number.

    4. Configure your DB2 client with the following commands. If we are using a remote database, complete this step separately from the WebSphere Portal installation.

        db2 update dbm cfg using tp_mon_name WAS
        db2 update dbm cfg using spm_name hostname, where hostname is the host name of WebSphere Portal.

      Because the default for spm_name is the hostname itself, specifying the hostname parameter is optional. If the hostname is more than eight characters, use empty double quotes (" ").

      For example, db2 update dbm cfg using spm_name " ".

    Modify database properties

    1. Make backup copies of the following files:

      • WP_PROFILE/ConfigEngine/properties/wkplc.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_dbtype.properties
      • WP_PROFILE/ConfigEngine/properties/wkplc_sourceDb.properties

      The last is if we are transferring from a database other than Derby: Default values are listed in these files. Unless otherwise noted, all values are of type alphanumeric text string. Set the appropriate values for each instance of each property. In wkplc_dbdomain.properties, most properties are repeated for each domain.

    2. Set properties in wkplc_dbdomain.properties

      1. dbdomain.DbType=db2

      2. dbdomain.DbName=domain_db_name

        DB2 database names cannot exceed eight (8) characters.

        • This value is also the database element in dbdomain.DbUrl.
        • TCP-IP alias for the database.

      3. dbdomain.DbSchema=domain_schema_name

        Some database management systems have schema name restrictions.

      4. dbdomain.DataSourceName=data_source_name

        Do not use the following reserved words:

        • releaseDS
        • communityDS
        • customizationDS
        • jcrDS
        • lmdbDS
        • feedback

      5. dbdomain.DbUrl=JDBC_DB_URL

        The value must conform to the JDBC URL syntax specified by the database.

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

      6. For dbdomain.DbUser, set the user ID for the database configuration user.

      7. For dbdomain.DbPassword, set the password for the database configuration user.

      8. For dbdomain.DbConfigRoleName, set the name of the group for database configuration users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbUser must be assigned to this group.

      9. Optional: For dbdomain.DbRuntimeUser, set the user ID of the database user that should be used by WebSphere Portal to connect to the database at runtime. If no value is specified for this setting, the database configuration user will be used to connect to the databases at runtime.

      10. If dbdomain.DbRuntimeUser is specified, set dbdomain.DbRuntimePassword to be the password of the runtime database user.

      11. For dbdomain.DbRuntimeRoleName, set the name of the group for database runtime users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbRuntimeUser must be assigned to this group.

      12. Optional: For dbdomain.DBA.DbUser, type the database administrator user ID for privileged access operations during database creation and setup. Required.if you run the create-database and setup-database ConfigEngine tasks . If you do not need this parameter, we can either accept the default value or leave blank.

      13. Optional: For dbdomain.DBA.DbPassword, set the database administrator password for privileged access operations during database creation. If you do not need this parameter, we can either accept the default value or leave blank.

      14. For dbdomain.XDbName, type the database loop back alias that needs to be set if you plan to use the create-database task.

      15. For dbdomain.DbNode, type the value for the database node name. Set this value to call create-database.

        Required only for local databases.

    3. Save and close the file.

    4. Update the following properties in wkplc_dbtype.properties.

      1. For db2.DbDriver, set the name of the JDBC driver class.

      2. For db2.DbLibrary, type the directory and name of the .zip or .jar file containing the JDBC driver class.

      3. For db2.JdbcProviderName, set the name of the JDBC provided that WebSphere Portal uses to communicate with its databases.

    5. Save and close the file.

    6. Update the WasPassword value in wkplc.properties. This value is the password for the WAS security authentication used in the environment.

    7. Save and close the file.

    Create a remote or local DB2 database manually

    1. Log in as a DB2 instance system authority.

      For example, we can log in as db2inst1 as the DB2 instance owner.

    2. Initialize a DB2 command environment.

      For example, execute...

        . /home/db2inst1/sqllib/db2profile

      db2inst1 is the DB2 instance owner of the DB2 instance.

      If we are using the Command Line Processor (CLP), refer to the DB2 documentation for details. The command prompt is db2=>. In this mode, commands can be entered without the db2 prefix or the double quotation marks. However, the following steps assume we are not using the CLP and are entering commands from the operating system shell prompt, for example: $.

    3. Run the following commands on the DB2 server system to configure the DB2 database instance:

      Environment Commands
      DB2 
      db2set DB2COMM=TCPIP
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_INLIST_TO_NLJN=YES
db2 "UPDATE DBM CFG USING query_heap_sz 32768"
db2 "UPDATE DBM CFG USING sheapthres 0"

    4. Create databases:

      • Replace dbname with the actual name of the database. Run the commands and each time replace dbname with the actual values for release, community, customization, Java Content Repository, Feedback, and Likeminds. You will need to run the commands once for each database for a total of six times.

        DB2 database names cannot exceed eight characters. Therefore, consider using these database names: release, commun, custom, jcrdb, fdbkdb, and lmdb. 

      db2 "CREATE DB dbname using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR dbname USING applheapsz 4096"
db2 "UPDATE DB CFG FOR dbname USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR dbname USING stmtheap 32768"
db2 "UPDATE DB CFG FOR dbname USING dbheap 2400"
db2 "UPDATE DB CFG FOR dbname USING locklist 1000"
db2 "UPDATE DB CFG FOR dbname USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR dbname USING logprimary 12"
db2 "UPDATE DB CFG FOR dbname USING logsecond 20"
db2 "UPDATE DB CFG FOR dbname USING logbufsz 32"
db2 "UPDATE DB CFG FOR dbname USING avg_appls 5"
db2 "UPDATE DB CFG FOR dbname USING locktimeout 30"
db2 "UPDATE DB CFG FOR dbname using AUTO_MAINT off"

    The following steps are only required for the IBM Java Content Repository database.

    1. On the DB2 server systems to create bufferpools and table spaces for the IBM Java Content Repository database (jcrdb).

      • jcrdb is the name of the database used to store user data and objects
      • jcr is the jcr user for jcrdb

        This value can be replaced with any Ithat has administrative authority.

      • dbpassword is the password for the jcr user for the jcrdb

      The commands in this step are different for DB2 and DB2 pureScale. Run the appropriate commands for the environment.

        DB2

        db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlsusrtspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

        DB2 pureScale

        db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

    2. On the DB2 client, edit /etc/services file. If it does not specify the DB2 connection service port, add the following text to specify the port for the remote DB2 instance:

        DB2_db2inst1 port1/tcp # DB2 connection service port

      ...where db2inst1 is the name of the DB2 instance on the system, and port1 with the actual port number assigned to the DB2 connection service in the DB2 server installation . The connection service port on the DB2 Client system, WebSphere Portal server, must match the connection service port on the DB2 server. The ports should match by number but not necessarily by name.

    3. Set up the correct service name by entering the following command on the DB2 server system: db2 "UPDATE DBM CFG USING svcename svce_name" where svce_name is the connection service port name specified previously.

    4. On the DB2 client, set DB2COMM to TCP/IP using the db2set command db2set DB2COMM=tcpip.

    5. Catalog the TCP/IP node with the IP address of the remote database server on DB2 Connect: db2 "catalog tcpip node remote_db_node_alias remote database_server_node server connection_service_port" where:

      • remote_db_node_alias is the alias name of the database server that we are defining for the WAS node name. The alias name can contain one to eight characters.
      • database_server_node is the fully qualified host name of the database server system.

      • connection_service_port is the name of the DB2 connection service port configured in the /etc/services file on the database server system.

    6. Catalog the WebSphere Portal databases on DB2 Connect, where:

      • remote_db_name_domain, is the cataloged name of the databases on the server system for each domain.

      • domain_alias_name, is the database alias names that we are defining.

      • remote_db_node_alias is the name that was used previously when you cataloged the TCP/IP node in the previous step.

      The alias for each database must be different from the actual database name and can only contain up to eight characters. 

      db2 "catalog db remote_db_name_release as release_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_community as comm_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_customization as cust_alias_name at node remote_db_node_alias"

db2 "catalog db remote_db_name_fdbkdb as fdbkdb_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_lmdb as lmdb_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_jcrdb as jcrdb_alias_name at node remote_db_node_alias"

    7. Log out of DB2 Connect by entering db2 "terminate".

    8. On DB2 Connect, test the remote connection by issuing the following command in the DB2 command window:

        db2 "connect to alias_name user username using password"

      ...where alias_name is the alias name definedd previously, username is the database user, and password is the password assigned to the database user.

    Configure a portal to use the database

    1. Edit the db2cli.ini file that resides on the local system, where WebSphere Portal is installed, before you transfer data.

      The database transfer becomes unresponsive at task action-process-constraints if you do not complete these steps.

      1. Locate the file /home/db2inst1/sqllib/cfg/db2cli.ini.

      2. Add the following lines to the end of the file:

        Edit db2cli.ini:

        • If a section named [COMMON] already exists in the file, extent that section by adding the following lines. Otherwise, add a [COMMON] section to the file.

        • Leave an empty line after ReturnAliases=0. 

        [COMMON]
DYNAMIC=1
ReturnAliases=0

    2. If we are installing multiple instances of WebSphere Portal increase the default configured number of databases.

      For example:

        set client MAX_NETBIOS_CONNECTIONS 254

      A message indicates success if the number was increased.

    3. cd WP_PROFILE/ConfigEngine

    4. Validate configuration properties...

        ./ConfigEngine.sh validate-database -DWasPassword=foo

      Add -DTransferDomainList to the validating task to specify the domains to validate; for example...

        -DTransferDomainList=jcr

      To validate all domains, do not specify this parameter.

    5. cd WP_PROFILE/bin

    6. Stop the WebSphere_Portal server:

        ./stopServer.sh WebSphere_Portal -username wpadmin -password foo

    7. Transfer the database:

      Do not execute the database-transfer task as a background process. This might cause the task to stall.

          cd WP_PROFILE/ConfigEngine
          ./ConfigEngine.sh database-transfer -DWasPassword=foo

        Set -DTransferDomainList to include only the domains to transfer.

        For example, to transfer only the JCR domain...

          ./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=foo

      1. If you have been storing data in Apache Derby for a long time, database transfer could fail with OutOfMemory exceptions. To fix:

          ConfigEngine.bat database-transfer -DDbtJavaMaxMemory=1536M -DWasPassword=foo

      2. Log output is written to:

          WP_PROFILE/ConfigEngine/log/ConfigTrace.log

        If the configuration fails, verify the values in the wkplc.properties, wkplc_dbdomain.properties, and wkplc_dbtype.properties files and then repeat this step.

    8. If dbdomain.DbRuntimeUser is set that user must have sufficient database user privileges:

      1. To manually grant database user privileges:

        1. Copy the appropriate template files to a work directory:

          • createRuntimeRoleForDifferentSchema.sql

            If the name of the database user and the schema name are not the same.

          • createRuntimeRoleForSameSchema.sql

            If the name of the database user and the schema name are the same.

          Files are in the following directories, where dbms is the database management system, and domain represents the database domains we are configuring (release, customization, community, jcr, feedback, and likeminds):

          • PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/dbms/domain

          • PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/dbms/domain

          JCR database domain: For the JCR database domain, copy grantPermissionsToRuntimeRoleStatic.sql from the following directory:

          PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/dbms/jcr/grantPermissionsToRuntimeRoleStatic.sql .

        2. Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.

        3. Execute the SQL statements.

      2. Grant database user privileges:

        1. Ensure the database administrator user ID is specified for domain.DBA.DbUser in...

            WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties

          For example, domain.DBA.DbUser=dbadmin.

          2. ./ConfigEngine.sh grant-runtime-db-user-privileges -DTransferDomainList=release,customization,community,etc...

          You only need to add -DTransferDomainList when granting privileges across specific domains.

    9. Perform a reorg check to improve performance. Perform this step for each database in the property file.

      1. Connect to a database:

          db2 connect to dbName user db2wpadmin using password

        Additional options might be required if additional security has been installed. Refer to DB2 Universal Database™ commands by example .

      2. Run the following commands from the DB2 prompt:

        db2 reorgchk update statistics on table all > xyz.out

      3. Look in the reorg column for entries marked with a star or asterisk * in the file xyz.out.

        1. For each line with *, note the tablename and run the following command for each tablename:

            db2 reorg table tablename

        2. After you have run the reorg command for each tablenames:

            db2 terminate

            db2rbind database_name -l db2rbind.out -u db2_admin -p password

      4. The file db2rbind.out is created when there is an error.

    10. cd WP_PROFILE/bin

    11. Start the WebSphere Portal server.


    See also:

    DB2 Quick Beginnings guide