This chapter discusses configuring the IBM Websphere portal v7.0 to use the DB2 for IBM i database. It contains the following topics:
Section 5.4, "Using ConfigEngine Tasks to Create Databases for a Local DB2 for IBM i Installation"
Section 5.6, "Transferring WebSphere Portal and Java Content Repository Databases to DB2 for IBM i"
Before beginning the database configuration, verify that a supported version of DB2 for IBM i Database software is installed. Refer to the Minimum Technical Requirements for this information. In addition, it is strongly recommended that you visit the IBM WebSphere Portal Version v7.0 Information Center and review the "Configuring WebSphere Portal to use a database" section:
For advanced configurations, refer to the IBM WebSphere Portal Version v7.0 Information Center.
View information on installing DB2 for IBM i for use with WebSphere Portal
Before you begin:
When you install DB2 for IBM i using the DB2 for IBM i installation program, it automatically creates a DB2 for IBM i administrative user with the correct operating system rights.
Ensure that you have enough disk space for the DB2 for IBM i instance home directory to be able to create the required databases.
If you are using the JDBC driver in type 2 mode, configure your DB2 for IBM i client with the following commands.
If you 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 your hostname is more than eight characters, use empty double quotes (" "). For example, db2 update dbm cfg using spm_name " ".
Before installing DB2 for IBM i, log in with a user ID that has administrative authority.
This user should have the following specifications:
Belong to the local Administrator group
Act as part of the operating system
Have permissions to create a token object
Have permissions to replace a process level token
To edit user rights:
For the first two specifications: Click Start -> Programs -> Administrative Tools -> Computer Management -> Local Users and Groups.
For the last four specifications: Click Start -> Programs -> Administrative Tools -> Local Security Policy. Then, click Local Policies -> User Rights Assignment.
To install DB2 for IBM i or the DB2 for IBM i client and the required fix pack, follow the instructions that are provided with the DB2 for IBM i documentation.
If DB2 for IBM i is installed on another system than WebSphere Portal, perform the following instructions:
For JDBC Type 2 drivers only: The appropriate DB2 for IBM i client must be installed on the same system as WebSphere Portal and have the same name as the server profile name.
For JDBC Type 4 drivers only: Copy the driver jar files to the Portal server. It is recommended that you place these driver files within the wp_profile_rootdirectory; for example:
wp_profile_root/PortalServer/dbdrivers/db2jcc4.jar
wp_profile_root/PortalServer/dbdrivers/db2jcc_license_cu.jar
You must modify the approriate properties files before transferring your data from the default database to the DB2 for IBM i database.
Multiple databases can be used to hold information for applications such as Feedback and LikeMinds. For example, you could use the following property values:
release.DbName=reldb
jcr.DbName=jcrdb
feedback.DbName=fdbkdb
likeminds.DbName=lmdb
community.DbName=commdb
customization.DbName=custdb
If you are using a remote database, enter the values for the remote server.
Regardless of the operating system, use a forward slash (/) instead of a backslash (\) in the property files for file system paths.
There might be additional database properties other than those listed here. Only change the properties within this task and skip all other properties.
Some values, shown here in italics, might need to be modified to your specific environment.
The recommended value listed for each property represents the specific information that is required to configure WebSphere Portal to your target database.
Depending on which database domain has to be configured, replace dbdomain with:
release
customization
community
jcr
feedback
likeminds
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 you 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.
Locate the following files and create a backup copy of each before changing any values:
wp_profile_root/ConfigEngine/properties/wkplc.properties
wp_profile_root/ConfigEngine/properties/wkplc_dbdomain.properties
wp_profile_root/ConfigEngine/properties/wkplc_dbtype.properties
If you are transferring from a database other than Derby: wp_profile_root/ConfigEngine/properties/wkplc_sourceDb.properties
Default values are listed in these files. Unless otherwise noted, all values are of type alphanumeric text string. Print out the steps below for reference before modifying the properties files. Make sure to enter the appropriate values for each instance of each property. In wkplc_dbdomain.properties, most properties are repeated for each domain.
Use a text editor to open the properties file wkplc_dbdomain.properties and modify the values to correspond to your environment.
For dbdomain.DbType, type db2.
For dbdomain.DbName, type the name of the WebSphere Portal domain database.
Note:
This value is also the database element in the dbdomain.DbUrl property.For dbdomain.DbSchema, type the schema name of the database domain.
Note:
Review your target database management system documentation to define a valid schema name. Some database management systems have schema name restrictions that you need to understand.For dbdomain.DataSourceName, type the name of the data source that WebSphere Portal uses to communicate with its databases.
Do not use the following reserved words:
releaseDS
communityDS
customizationDS
jcrDSx
feedback
jcrDS
For dbdomain.DbUrl, type the database URL used to access the WebSphere Portal database with JDBC. The value must conform to the JDBC URL syntax specified by the database.
Note:
The database element of this value should match the value of DbName.For dbdomain.DbUser, type the user ID for the database configuration user.
For dbdomain.DbPassword, type the password for the database configuration user.
For dbdomain.DbConfigRoleName, type 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.
Optional: For dbdomain.DbRuntimeUser, type 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.
If dbdomain.DbRuntimeUser is specified, you must set dbdomain.DbRuntimePassword to be the password of the runtime database user.
For dbdomain.DbRuntimeRoleName, type 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.
Optional: For dbdomain.DBA.DbUser, type the database administrator user ID for privileged access operations during database creation. If you do not need this parameter, you can either accept the default value or leave blank.
Optional: For dbdomain.DBA.DbPassword, type the database administrator password for privileged access operations during database creation. If you do not need this parameter, you can either accept the default value or leave blank.
Save and close the file.
Update the following properties in the file wkplc_dbtype.properties.
For db2.DbDriver, type the name of the JDBC driver class.
For db2.DbLibrary, type the directory and name of the .zip or .jar file that contains the JDBC driver class.
For db2.JdbcProviderName, type the name of the JDBC provider that WebSphere Portal uses to communicate with its databases.
Save and close the file.
Update the WasPassword value in the wkplc.properties file. This value is the password for the WebSphere Application Server security authentication used in your environment.
Save and close the file.
Before transferring the databases to DB2 for IBM i, you must first create the users and groups you have 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.
Create a user for dbdomain.DbUser. If you have provided a value in the wkplc_dbdomain.properties file 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 the wkplc_dbdomain.properties file.
Create a group for dbdomain.DbConfigRoleName. If you have provided a value in the wkplc_dbdomain.properties file for dbdomain.DbRuntimeRoleName, create a group for dbdomain.DbRuntimeRoleName.
Assign the created user for dbdomain.DbUser to the created group for dbdomain.DbConfigRoleName.
If dbdomain.DbRuntimeUser is specified, assign the created user for dbdomain.DbRuntimeUser to the created group for dbdomain.DbRuntimeRoleName.
This section provides information on using ConfigEngine tasks to create databases when using a local DB2 for IBM i installation. If you are using a remote DB2 for IBM i installation, you must create your databases manually and cannot create databases using the ConfigEngine task.
Before you begin, ensure that the following prerequisites are met:
The database management system software is installed.
To create a database, you must be a DB2 for IBM i System Administrator with sufficient database privileges (SYSADM or at a minimum SYSCTRL).
Log in as a DB2 for IBM i instance system authority. For example, you can log in as db2inst1 as the DB2 for IBM i instance owner.
Change to the directory wp_profile_root/ConfigEngine
To create the databases, type the following command:
./ConfigEngine.sh create-database -DWasPassword=password
This topic provides instructions on automatically setting up your database using the ConfigEngine task to create users, grant permissions, and create Java Content Repository table spaces.
You must create your DB2 for IBM i databases before running the configuration task in this topic.
As an alternative to automatically setting up the database, you can manually set up your database by referring to the link in the related tasks section of this topic.
Change to the directory wp_profile_root/ConfigEngine
To create the database users, type the following command:
Note:
The task setup-database assigns the minimum database privileges to the database configuration and runtime database users../ConfigEngine.sh setup-database -DWasPassword=password
View information on manually transferring data to the DB2 for IBM i database you have installed and set up. Follow these steps to transfer WebSphere Portal and Java Content Repository databases to DB2 for IBM i. As an alternative to the manual database transfer procedure that this topic describes, you can use the configuration wizard to complete the database transfer task. However, you cannot specify all settings through the configuration wizard. For this reason, you must specify the required settings in the appropriate property files before transferring the database with the configuration wizard.
Tips:
Tips:
If you are transferring from Oracle or Oracle RAC, the open_cursors setting should be set to 1500 by default. However, you might need to increase this value based on the table count in the Java Content Repository schema.
Be sure that DB2 for IBM i is started by checking the service. If attempts to restart result in a logon failure message, then go to the DB2 properties and reenter the password.
If you are running a type 2 connection, edit the db2cli.ini file that resides on the local system, where WebSphere Portal is installed, before you transfer data.
Important:
The database transfer becomes unresponsive at task action-process-constraints if you do not complete these steps.Locate the file C:\Program Files\IBM\SQLLIB\db2cli.ini.
Add the following lines to the end of the file:
Editing db2cli.ini:
If a section named [COMMON] already exists in the file, extend 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
Open a command prompt and change to the directory wp_profile_root\ConfigEngine.
Enter the ./ConfigEngine.sh validate-database -DWasPassword=password command to validate configuration properties.
Tip:
Add the -DTransferDomainList parameter to the above validating task to specify the domains you want to validate; for example: -DTransferDomainList=jcr. If you want to validate all domains, you do not need to specify this parameter on the command line.From the same command prompt as the previous steps, change to the directory wp_profile_root\bin.
Stop both the server1 and WebSphere_Portal servers:
./stopServer.bat server1 -username admin_userid -password admin_password
./stopServer.bat WebSphere_Portal -username admin_userid -password admin_password
Transfer the database:
Change to the directory wp_profile_root\ConfigEngine.
Enter the following command:
./ConfigEngine.sh database-transfer -DWasPassword=password
Note:
To select specific database domains to transfer, modify the -DTransferDomainList specified in the command to include only the domains that you want to transfer. For example, to transfer only the JCR domain you can enter the following command:
./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=password
If you have been storing data in Apache® Derby for a long time, database transfer could fail with OutOfMemory exceptions. If database transfer fails, add the following property to the command in this step:
./ConfigEngine.sh database-transfer -DDbtJavaMaxMemory=1536M -DWasPassword=password
After running the task, a message is added to the following log file for you to verify the task ran successfully:
wp_profile_root\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.
Optional: If you specified a runtime database user for the dbdomain.DbRuntimeUser parameter, that user must have sufficient database user privileges. To grant the database user privileges, choose either the manual steps or the command line steps:
Complete these steps to manually grant database user privileges:
Copy the appropriate template files to a work directory. Choose one of the following template files:
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.
JCR database domain: For the JCR database domain, you must also copy grantExtendedPermissionsToRuntimeRole.sql.
Locate these files in the following directories:
PortalServer_root\base\wp.db.impl\config\templates\setupdb\dbms\domain
PortalServer_root\pzn\prereq.pzn\config\templates\setupdb\dbms\domain
Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.
Run these statements.
Complete these steps to grant database user privileges with the ConfigEngine task:
Ensure the database administrator user ID is specified for domain.DBA.DbUser inwp_profile_root\ConfigEngine\properties\wkplc_dbdomain.properties. For example, domain.DBA.DbUser=dbadmin.
Run the following task:
./ConfigEngine.sh grant-runtime-db-user-privileges -DTransferDomainList=comma_separated_list_of_domains
After transferring the database tables, perform a reorg check to improve performance. Perform this step for each database alias in the property file.
Connect to a database with the following command:
db2 connect to database_alias user db2admin_userid using password
Note:
Additional options might be required if additional security has been installed. Refer to DB2 for IBM i Universal Database commands by example for links to the command reference.After it is connected, run the following command from the DB2 for IBM i prompt:
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.
For each line with *, note the tablename and run the following command for each tablename:
db2 reorg table tablename
After you have run the reorg command for each tablename, run the following commands:
db2 terminate db2rbind database_name -l db2rbind.out -u db2_admin -p password
The output file db2rbind.out is only created when there is an error for the db2rbind command.
Run the ./ConfigEngine.sh create-jcr-jms-resources-post-dbxfer -DWasPassword=password command to create JMS resources in the new database.
Note:
Regardless of the method used to transfer data (configuration wizard or the steps in this topic), you must run this task to create JMS resources.Change to the directory wp_profile_root\bin.
Start the Administrative Server (server1) and WebSphere Portal server.
If you are using Web Content Management, you must update the database configuration to support large files. Do this by setting thefullyMaterializeLobData property in the WebSphere Application Server administrative console.
Note:
You only need to perform these steps if you are using Web Content Management.Log into the WebSphere Application Server administrative console.
Click Resources -> JDBC -> Data sources.
Select all scopes (the default setting) or select a specific cell, node, or node/server.
Select the scope that corresponds to your instance of WebSphere Portal.
The view refreshes.
Select the name of the data source that is defined in wkplc_dbdomain.properties for the JCR database domain.
The default data source is wpdbDS.
Click Custom properties.
Ensure that the fullyMaterializeLobData property is set to false.
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 for IBM i.
Before you begin, ensure that the following conditions are met:
The WebSphere Portal database has been successfully transferred to DB2 for IBM i 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 you 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