Creating databases and users for DB2

This topic includes information on setting up DB2 to work with WebSphere Portal and content publishing. 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, WebSphere Portal can create and set up the required databases for you.

• Creating local databases
• Creating remote databases
• Creating users
• Next steps

Creating local databases

You must specify a user ID with administrative privileges for the recommended databases. See the DB2 product documentation for details on creating databases. If you use a local DB2 server, WebSphere Portal can create the required databases for you. If you choose to use this option, you can skip the information in this section and continue to Next steps.

Creating remote databases

A remote database resides on a different machine than WebSphere Studio. 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. Before creating these databases, note the following information:

• The client software, DB2 Client, must be properly configured to connect to the remote DB2 server instance, e.g. 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.
• These instructions assume that a remote DB2 server and DB2 Client are already installed and running. For information on installing DB2 Client, see the DB2 product documentation.
• These instructions assume that you use DB2 Client to connect to a remote DB2 server, and that WebSphere Studio and DB2 Client will be installed on the same machine.

Note: WebSphere Portal Version 5.0.2.1 does not support the DB2 JDBC Type 3 driver, also known as net driver.

Follow these steps to create the remote DB2 databases that are recommended in Planning for DB2:

1. Log in to the DB2 server machine as a user with sufficient database privileges:
   • Log in as a user with administrative privileges.
2. On the DB2 server machine, open the DB2 command window and enter the following commands for the WebSphere Portal database:

   db2 create database wps50 using codeset UTF-8 territory us
   db2 update database configuration for wps50 using applheapsz 16384 app_ctl_heap_sz 8192
   db2 update database configuration for wps50 using stmtheap 60000
   db2 update database configuration for wps50 using locklist 400
   db2 update database configuration for wps50 using indexrec RESTART
   db2 update database configuration for wps50 using logfilsiz 1000
   db2 update database configuration for wps50 using logprimary 12
   db2 update database configuration for wps50 using logsecond 10
   db2set DB2_RR_TO_RS=yes

3. On the DB2 server machine, enter the following commands for the content publishing databases:

   db2 create database wpcp50 using codeset UTF-8 territory us collate using identity
   db2 create database fdbk50 using codeset UTF-8 territory us collate using identity
   db2 update database configuration for wpcp50 using applheapsz 4096
   db2 update database configuration for fdbk50 using applheapsz 4096
   db2 update database configuration for wpcp50 using logfilsiz 4096
   db2 update database configuration for fdbk50 using logfilsiz 4096
   db2 update database configuration for wpcp50 using logprimary 4
   db2 update database configuration for fdbk50 using logprimary 4
   db2 update database configuration for wpcp50 using logsecond 25
   db2 update database configuration for fdbk50 using logsecond 25

4. After you have created and configured the remote content publishing databases in the previous step, perform the following database commands on the DB2 server machine:

   db2 connect to wpcp50
   db2 create bufferpool wpcpBP8K size 8192 pagesize 8K
   db2 connect reset
   db2 connect to wpcp50
   db2 create system temporary tablespace wpcpTS8K pagesize 8K managed by system using('wpcpTS8K') bufferpool wpcpBP8K
   db2 connect reset

5. On the DB2 server machine, use a text editor to open the <windows_root>/system32/drivers/etc/services file. If it does not specify DB2 connection and interrupt service ports, add the following text to specify the ports:

   db2c_DB2 50000/tcp # DB2 connection service port
   db2i_DB2 50001/tcp # DB2 interrupt service port

   where DB2 is the name of the DB2 instance ID on the system.
6. On the DB2 Client machine, use a text editor to open the services file, typically found in the <windows_root>/system32/drivers/etc/ directory. If it does not specify the DB2 connection service port, add the following text to specify the port for the remote DB2 instance:

   db2c_DB2 50000/tcp # DB2 connection service port

   where DB2 is the name of the DB2 instance ID on the system.
   Note: 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.
7. On the DB2 server machine, set DB2COMM to TCP/IP by using the db2set command on the DB2 command window, as follows:
   

   db2set DB2COMM=TCPIP

8. On the DB2 server machine, set up the correct service name by entering the following command:
   

   db2 UPDATE DBM CFG USING svcename svce_name

   
   where svce_name is the connection service name that is specified in step 5.
9. Log in to the DB2 Client machine as a user with sufficient database privileges:
   • Log in as a user with administrative privileges.
10. On the DB2 Client machine, set DB2COMM to TCP/IP by using the db2set command at the DB2 command window, as follows:

    db2set DB2COMM=tcpip

11. 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 db2_alias remote database_server_node server connection_service_port

    where db2_alias is the alias_name 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 your database server machine, and connection_service_port is the name of the DB2 connection service port that you specified in the <windows_root>/system32/drivers/etc/services file on the database server machine in step 5.
12. On the DB2 Client machine, open the DB2 command window, catalog the WebSphere Portal and content publishing databases, as follows:

    db2 catalog db remote_db_name_wps50 as wps50_alias_name at node was_node
    db2 catalog db remote_db_name_wpcp50 as wpcp50_alias_name at node was_node
    db2 catalog db remote_db_name_fdbk50 as fdbk50_alias_name at node was_node

    where remote_db_name_wps50, remote_db_name_wpcp50, remote_db_name_fdbk50 are the catalogued name of the WebSphere Portal and content publishing databases on the server machine, wpcp50_alias_name, fdbk50_alias_name, wps50_alias_name, are the database alias names that you are defining, and was_node is the name that was used previously when you catalogued the TCP/IP node in the previous step.

    Note: If you are using a separate database for Member Manager, also catalog the Member Manager database.

13. On the DB2 Client machine, test your remote connection by issuing the following command in the DB2 command window:

    db2 connect to <alias_name> user <username> using <password>
    db2 terminate

    where <alias_name> is the alias name that you defined in step 12 and <username> is the database user.
14. Restart your database server and your database client.

Creating users

It is recommended 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 you can use. A common user name is db2admin, but you can assign any user name as long as it has administrative access and follows the limitations set out here. It is recommended that you do not change the user name after creating it.

The limitations are as follows:

• User names on Windows can contain one to 30 characters. The Windows 2000 operating systems currently have a limit of 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.

Refer to the DB2 product documentation for details on creating users and granting the privileges.

Next steps

You have completed this step. Continue to the next step by choosing one of the following topics: