Create databases and users for DB2

Create databases and users for DB2

+
Search Tips | Advanced Search

Plan for DB2
Install DB2
Create databases and users for DB2 (Current task)
Transfer DB2 manually or Transfer between databases manually
Verify database connections

This topic includes information on setting up IBM DB2 Universal Database Enterprise Server Edition to work with IBM WebSphere Portal.
If you use a remote DB2 server, manually create the databases that are required by WebSphere Portal before performing WebSphere Portal database transfer to work with DB2. If you use a local DB2 instance, WebSphere Portal can create and set up the required databases for you.

Before you begin

You should have completed Installing DB2.

Note the following information:

If WebSphere Portal Version 6.0 and an earlier version of WebSphere Portal coexist, the database user IDs for WebSphere Portal Version 6.0 must be different than earlier versions to avoid conflicts during database transfer.

When using the same database user ID, the name of all portal databases, the name of all TCPIP Alias for the portal databases, and the name of all the nodes must be different.

Accessing a local DB2 database on a UNIX machine can cause shared memory problems. To correct these problems, treat the local database as a remote database on the local system. Follow the instructions in the Creating remote databases section if you are manually creating a database.
If you create a local DB2 database, the remote database on the local system is configured by create-local-database-db2.

When creating database instances in DB2 for use with WebSphere Portal, we can create the database instances with a 32-bit or 64-bit setting. A 64-bit setting is supported if the client/driver that WebSphere Portal connects to is 32-bit.

The tablespace LMTABLESPACE is reserved for LikeMinds. LikeMinds and Portal Personalization will drop this tablespace during uninstallation of LikeMinds and certain database transfer tasks.

Create users

IBM recommends that one user with administrative rights on the operating system and the DB2 instance be used for all databases. This user can be the database administrative user that is created automatically by the DB2 installation program. If you choose to have WebSphere Portal create the databases, the database user should be given SYSADM rights.
When installing DB2, there is a limitation on what user names we can use. A common user name is db2inst1, but we can assign any user name as long as it has administrative access and follows the limitations set out here. IBM recommends that you do not change the user name after creating it.
The limitations are as follows:

User names on UNIX can contain one to eight characters.

User names on Windows can contain one to 20 characters.

Group and instance names can contain one to eight characters.

Names cannot be any of the following examples:

users
admins
guests
public
local

Names cannot begin with:

IBM
SQL
SYS

Names cannot include accented characters.

Following are the basic steps to create new users and grant privileges...

From the DB2 Control Center, expand the object tree until you find the DB Users folder.

Right-click the DB Users folder and select Create/Add from the pop-up menu. The Create DB User notebook opens.

In the User name field, type a 1-8 character user ID.

Click the tab for the type of object for which you want to grant privileges to the user ID. The privileges can be altered pertaining to the user.

Click OK to save changes and close the window.

Create local databases

If you use a local DB2 server, run the following tasks to automatically create the required databases as a remote database on the local system:

When using create-local-database-db2 to create a remote DB2 database on the local system in a non-Windows, you will need the following database properties:

Depending on which database domain has to be configured, dbdomain will be replaced by:

release
customization
community
jcr
feedback
likeminds
wmm

dbdomain.DbName is the TCPIP Alias for the database.
dbtype.XDbName is the name of the database.

While creating databases, the values for dbdomain.DbName and dbdomain.XDbName must be different in the wpconfig_dbdomain.properties file.
Open a command prompt, and change to the directory portal_server_root/config.

If WebSphere Portal is on UNIX, export the DB2 user profile (that you created when installing DB2 ) onto the administrative user. This gives that administrative user rights over DB2. It is important to complete this step before running database tasks...
/home/db2inst1/sqllib/db2profile

...where db2inst1 represents the database instance.

Perform the following steps to set up the configuration file with values specific to the environment:

Locate the portal_server_root/config/wpconfig.properties file and create a backup copy before changing any values.

Perform Step 2 of Transferring DB2 manually.

Save the file.

To create the WebSphere Portal, Member Manager, DB2 Content Manager Runtime Edition, Feedback and LikeMinds databases, type the following command:

UNIX:

./WPSconfig.sh create-local-database-db2 -DDbPassword=password -DJcrDbPassword=password -DFeedbackDbPassword=password -DLikemindsDbPassword=password -DWmmDbPassword=password

Windows:
WPSconfig.bat create-local-database-db2 -DDbPassword=password -DJcrDbPassword=password -DFeedbackDbPassword=password -DLikemindsDbPassword=password -DWmmDbPassword=password

Perform this step only if WebSphere Portal is on UNIX. Use the following steps to export the db2instance environment in the profile:

Open a command prompt, and change to the directory portal_server_root/config.

In your.bashrc,.dshrc, or.profile file, add...
if [ -f /home/db2inst1/sqllib/db2profile ]; then. /home/db2inst1/sqllib/db2profile; fi

...where db2inst1 represents the database instance.

Reopen all the shells.

Validate that the environment has set the DB2 profile environment variables, such as DB2INSTANCE=db2inst1 wheredb2inst1 represents the database instance by running the env command.

Perform this step only if WebSphere Portal is on UNIX. On the DB2 server machine, check the services file. If it does not specify DB2 connection and interrupt service ports, specify the ports for the operating system:

Windows:
Edit...
/etc/services

...and add the following text (where db2inst1 is the default instance)...
db2c_db2inst1 50000/tcp

The /etc/services file is located under...
%systemroot%/system32/drivers/

...where %systemroot% is the location of the Windows operating system. For example...
C:\Windows\system32\drivers\etc\services

UNIX:
Use a text editor to open the file /etc/services and add the following text (where db2inst1 is the name of the DB2 instance ID on the system):
db2c_db2inst1 50000/tcp # DB2 connection service port
db2i_db2inst1 50001/tcp # DB2 interrupt service port

Perform this step only if WebSphere Portal is on UNIX. On the DB2 server machine, set DB2COMM to TCP/IP by using the db2set command, as follows:
db2set DB2COMM=TCPIP

Perform this step only if WebSphere Portal is on UNIX. Set up the correct service name by entering the following command on the DB2 server machine:
db2 "UPDATE DBM CFG USING svcename svce_name"

...where svce_name is the connection service port name that is specified in step 7.

Continue to Creating users.

Create remote databases

A remote database resides on a different machine than WebSphere Portal. If you use a remote DB2 server, manually create the databases that are required by WebSphere Portal. See Planning for DB2 for more information on the database architecture recommendations.
WebSphere Portal Version 6.0 does not support the DB2 JDBC Type 3 driver, also known as Net Driver.
For Portal v6.0.1+, if the DB2 JDBC type 4 driver is used, only the instructions on the remote server are required. You do not need to install the DB2 client software, and you do not need to complete the steps related to the DB2 client.
If using client software, DB2 Client, must be properly configured to connect to the remote DB2 server instance, for example, db2inst1. The DB2 Client instance must use the same instance as the DB2 server. For example, if the instance that you connect to is db2inst1, the DB2 Client instance that connects to the DB2 server must also be db2inst1.

Log in to the DB2 server machine as a user with sufficient database privileges.

Ensure that all database users...

Have been created
Granted appropriate privileges
Have a password assigned

Initialize a DB2 command environment:

Windows: Open a command prompt and type...
db2cmd

UNIX: At a command prompt, type...
su - db2inst1

...where db2inst1 represents the database user.

The prompt changes to $. In this mode, type...
db2

...at the beginning of each command and use quotation marks ("") around the entire command. For example:
db2 "CREATE REGULAR TABLESPACE SMS PAGESIZE 4 K MANAGED BY SYSTEM USING ('sms') BUFFERPOOL SMSPOOL"

If you are using the Command Line Processor the command prompt is db2=>. In this mode, commands can be entered without the "db2" prefix or the quotation marks. However, the following steps assume you are not using the CLP and are entering commands from the $ prompt.

Execute on the DB2 server machine to configure the DB2 database instance:
db2set DB2_RR_TO_RS=yes
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_INLIST_TO_NLJN=YES
db2 "UPDATE DBM CFG USING query_heap_sz 32768"
db2 "UPDATE DBM CFG USING maxagents 500"
db2 "UPDATE DBM CFG USING sheapthres 50000"

Create databases.
Replace dbname with the actual name of the database; run the commands several times and each time replace dbname with the actual values for...

wpsdb
comm
cust
jcrdb
wmmdb
fdbkdb
lmdb

By default, all databases collate using the UCA400_NO collation of DB2. As this collation may not be suitable for certain needs, you may decide to change this collation in the 'CREATE DB' command below.
db2 "CREATE DB dbname using codeset UTF-8 territory us COLLATE USING UCA400_NO 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 8192"
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 1000"
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"

On the DB2 server machine, run the following commands, where:

jcrdb name of the database used to store user data and objects
icmadmin Database user for jcrdb.
This value can be replaced with any id that has administrative authority.
dbpassword password for jcrdb

These steps are only required for the database for jcrdb (DB2 Content Manager Runtime Edition)
db2 "CONNECT TO jcrdb USER icmadmin USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 8000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 8000 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 "DISCONNECT jcrdb"
db2 "TERMINATE"

On the DB2 server machine, check the services file. If it does not specify DB2 connection and interrupt service ports, specify the ports for the operating system:

Windows:
Edit...
/etc/services

...and add the following text (where db2inst1 is the default instance):
db2c_db2inst1 50000/tcp

The /etc/services file is located under...
%systemroot%/system32/drivers/

...where %systemroot% is the location of the Windows operating system. For example...
C:\Windows\system32\drivers\etc\services

UNIX:

Use a text editor to open...
/etc/services

...and add the following text, where db2inst1 is the name of the DB2 instance ID on the system,...
db2c_db2inst1 50000/tcp # DB2 connection service port
db2i_db2inst1 50001/tcp # DB2 interrupt service port

On the DB2 Client machine, use a text editor to open the /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:
db2c_db2inst1 50000/tcp # DB2 connection service port

...where db2inst1 is the name of the DB2 instance ID on the system.
The connection service port on DB2 Client must match the connection service port on the DB2 server. The ports should match by number but not necessarily by name.

On the DB2 server machine, set DB2COMM to TCP/IP by using the db2set command, as follows:
db2set DB2COMM=TCPIP

Set up the correct service name by entering the following command on the DB2 server machine:
db2 "UPDATE DBM CFG USING svcename svce_name"

...where svce_name is the connection service port name that is specified in step 7.

On the DB2 Client machine, set DB2COMM to TCP/IP by using the db2set command, as follows:
db2set DB2COMM=tcpip

On the DB2 Client machine, catalog the TCP/IP node with the IP address of the remote database server, as follows:
db2 "catalog tcpip node remote_db_node_alias remote database_server_node server connection_service_port"

...where remote_db_node_alias is the alias of the Database Server that you are defining for the WebSphere Application Server node name, database_server_node is the fully qualified host.name of the database server machine, and connection_service_port is the name of the DB2 connection service port that you specified in the /etc/services file on the database server machine in step 7.

On the DB2 Client machine, catalog the WebSphere Portal, JCR, Member Manager, Feedback and Likeminds databases as follows:

Perform this step for all installations:
db2 "catalog db remote_db_name_release as release_alias at node remote_db_node_alias"
db2 "catalog db remote_db_name_community as community_alias at node remote_db_node_alias"
db2 "catalog db remote_db_name_customization as customization_alias at node remote_db_node_alias"
db2 "catalog db remote_db_name_fdbkdb as fdbkdb_alias at node remote_db_node_alias"
db2 "catalog db remote_db_name_lmdb as lmdb_alias at node remote_db_node_alias"
db2 "catalog db remote_db_name_jcrdb as jcrdb_alias at node was_node"
db2 "catalog db remote_db_name_wmmdb as wmmdb_alias at node was_node"

...where remote_db_name_<domain>, is the cataloged name of the databases on the server machine, <domain>_alias, is the database alias names that you are defining, remote_db_node_alias and was_node 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.

Log out of the DB2 client machine...
db2 "quit"

On the DB2 Client machine, test the remote connection...
db2 "connect to alias user username using password"

...where alias is the alias name that you defined in step 13 and username is the database user.

Restart the database server.

Continue to Creating users.

Switching data sources to use DB2 JDBC type 4 driver (JCC) instead of DB2 JDBC type 2 (CLI)

To modify existing data sources of WebSphere Portal to use the DB2 JCC JDBC driver in type 4 mode instead of the DB2 CLI-based JDBC driver in type 2 mode...

Stop server WebSphere_Portal.
Start server WebSphere_Portal in recovery mode to clean up the transaction log...
/opt/IBM/WebSphere/AppServer/profiles/wp_profile/bin/startServer.sh WebSphere_Portal -recovery

When the cleanup process is completed, the server will stop automatically.
Create a backup copy of the WebSphere Application Server profile that contains the configuration of WebSphere Portal. By default this is "wp_profile".
/opt/IBM/WebSphere/AppServer/profiles/wp_profile/bin/backupConfig.sh

The backup can be found at the location that is specified in the output of the backup command.
Create backup copies of the database configuration settings in PortalServer/config...
wpconfig_dbdomain.properties
wpconfig_dbtype.properties

Although it is not expected to see any modifications to the database backend, a backup of the DB2 database is recommended.
Start server server1.
Log in to the WebSphere Application Server administrative console.
Delete the DB2 CLI type 2 JDBC provider including their data sources. The entries can be found in the "Resources" category.
Save the changes made to the configuration.
Log out and stop server server1.
Edit...
PortalServer/config/wpconfig_dbdomain.properties
PortalServer/config/wpconfig_dbtype.properties

...to define the settings needed for connections via DB2 JCC type 4 JDBC driver.
Execute...
PortalServer/config/WPconfig.sh connect-database

Start server WebSphere_Portal.
Verify that the log file of WebSphere Portal, i.e. "SystemOut.log", is clean.
If this is not the case, restore the WebSphere Application Server profile backup and restore the configuration files...
wpconfig_dbdomain.properties
wpconfig_dbtype.properties

...before starting another attempt.

Next steps

You have completed this step. Continue to the next step:

Transferring DB2 manually

Parent topic:
Creating databases and users

jcrdb	name of the database used to store user data and objects
icmadmin	Database user for jcrdb. This value can be replaced with any id that has administrative authority.
dbpassword	password for jcrdb