Create remote databases

 

+

Search Tips   |   Advanced Search

 

Overview

This topic discusses manually creating portal databases for remote DB2 database servers.

If an IBM DB2 Universal Database Enterprise Server Edition JDBC driver, JCC, type 4 driver is used, you do not need to install the DB2 client software, and you do not need to complete the steps related to the DB2 client.

For type 2 drivers, DB2 Connect client software must be configured on the portal server in order to to connect to the remote DB2 server instance


Procedure

  1. Install DB2

  2. Create users

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

  4. Initialize a DB2 command environment by opening a command prompt and typing db2cmd.

    In this mode, type db2 at the beginning of each command and use double quotation marks ("") around the entire command.

    For example:

      db2 "create regular tablespace sms pagesize 4 k managed by system using ('sms') bufferpool smspool"

    For the Command Line Processor (CLP). The command prompt is: db2=>. In this mode, commands can be entered without the db2 prefix or the double quotation marks.

    The following steps assume you are not using the CLP and are entering commands from the $ prompt.

  5. On the DB2 server system, run...

      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 0"

  6. Create the necessary databases.

      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"

    You can create one database for all features...

      db2 "create db wpsdb using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for wpsdb using applheapsz 4096"
      db2 "update db cfg for wpsdb using app_ctl_heap_sz 1024"
      db2 "update db cfg for wpsdb using stmtheap 32768"
      db2 "update db cfg for wpsdb using dbheap 2400"
      db2 "update db cfg for wpsdb using locklist 1000"
      db2 "update db cfg for wpsdb using logfilsiz 4000"
      db2 "update db cfg for wpsdb using logprimary 12"
      db2 "update db cfg for wpsdb using logsecond 20"
      db2 "update db cfg for wpsdb using logbufsz 32"
      db2 "update db cfg for wpsdb using avg_appls 5"
      db2 "update db cfg for wpsdb using locktimeout 30"
      db2 "update db cfg for wpsdb using AUTO_MAINT off"

    You can create a distinct DB for each feature...

      db2 "create db release using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for release using applheapsz 4096"
      db2 "update db cfg for release using app_ctl_heap_sz 1024"
      db2 "update db cfg for release using stmtheap 32768"
      db2 "update db cfg for release using dbheap 2400"
      db2 "update db cfg for release using locklist 1000"
      db2 "update db cfg for release using logfilsiz 4000"
      db2 "update db cfg for release using logprimary 12"
      db2 "update db cfg for release using logsecond 20"
      db2 "update db cfg for release using logbufsz 32"
      db2 "update db cfg for release using avg_appls 5"
      db2 "update db cfg for release using locktimeout 30"
      db2 "update db cfg for release using AUTO_MAINT off"

      db2 "create db commun using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for commun using applheapsz 4096"
      db2 "update db cfg for commun using app_ctl_heap_sz 1024"
      db2 "update db cfg for commun using stmtheap 32768"
      db2 "update db cfg for commun using dbheap 2400"
      db2 "update db cfg for commun using locklist 1000"
      db2 "update db cfg for commun using logfilsiz 4000"
      db2 "update db cfg for commun using logprimary 12"
      db2 "update db cfg for commun using logsecond 20"
      db2 "update db cfg for commun using logbufsz 32"
      db2 "update db cfg for commun using avg_appls 5"
      db2 "update db cfg for commun using locktimeout 30"
      db2 "update db cfg for commun using AUTO_MAINT off"


      db2 "create db custom using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for custom using applheapsz 4096"
      db2 "update db cfg for custom using app_ctl_heap_sz 1024"
      db2 "update db cfg for custom using stmtheap 32768"
      db2 "update db cfg for custom using dbheap 2400"
      db2 "update db cfg for custom using locklist 1000"
      db2 "update db cfg for custom using logfilsiz 4000"
      db2 "update db cfg for custom using logprimary 12"
      db2 "update db cfg for custom using logsecond 20"
      db2 "update db cfg for custom using logbufsz 32"
      db2 "update db cfg for custom using avg_appls 5"
      db2 "update db cfg for custom using locktimeout 30"
      db2 "update db cfg for custom using AUTO_MAINT off"


      db2 "create db jcrdb using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for jcrdb using applheapsz 4096"
      db2 "update db cfg for jcrdb using app_ctl_heap_sz 1024"
      db2 "update db cfg for jcrdb using stmtheap 32768"
      db2 "update db cfg for jcrdb using dbheap 2400"
      db2 "update db cfg for jcrdb using locklist 1000"
      db2 "update db cfg for jcrdb using logfilsiz 4000"
      db2 "update db cfg for jcrdb using logprimary 12"
      db2 "update db cfg for jcrdb using logsecond 20"
      db2 "update db cfg for jcrdb using logbufsz 32"
      db2 "update db cfg for jcrdb using avg_appls 5"
      db2 "update db cfg for jcrdb using locktimeout 30"
      db2 "update db cfg for jcrdb using AUTO_MAINT off"


      db2 "create db fdbkdb using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for fdbkdb using applheapsz 4096"
      db2 "update db cfg for fdbkdb using app_ctl_heap_sz 1024"
      db2 "update db cfg for fdbkdb using stmtheap 32768"
      db2 "update db cfg for fdbkdb using dbheap 2400"
      db2 "update db cfg for fdbkdb using locklist 1000"
      db2 "update db cfg for fdbkdb using logfilsiz 4000"
      db2 "update db cfg for fdbkdb using logprimary 12"
      db2 "update db cfg for fdbkdb using logsecond 20"
      db2 "update db cfg for fdbkdb using logbufsz 32"
      db2 "update db cfg for fdbkdb using avg_appls 5"
      db2 "update db cfg for fdbkdb using locktimeout 30"
      db2 "update db cfg for fdbkdb using AUTO_MAINT off"


      db2 "create db lmdb using codeset UTF-8 territory us PAGESIZE 8192"
      db2 "update db cfg for lmdb using applheapsz 4096"
      db2 "update db cfg for lmdb using app_ctl_heap_sz 1024"
      db2 "update db cfg for lmdb using stmtheap 32768"
      db2 "update db cfg for lmdb using dbheap 2400"
      db2 "update db cfg for lmdb using locklist 1000"
      db2 "update db cfg for lmdb using logfilsiz 4000"
      db2 "update db cfg for lmdb using logprimary 12"
      db2 "update db cfg for lmdb using logsecond 20"
      db2 "update db cfg for lmdb using logbufsz 32"
      db2 "update db cfg for lmdb using avg_appls 5"
      db2 "update db cfg for lmdb using locktimeout 30"
      db2 "update db cfg for lmdb using AUTO_MAINT off"

  7. On the DB2 server system, run the following commands. This step is only required for the Java Content Repository database (jcrdb).

      db2 "connect to jcrdb USER jcr 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"

    ...where...

      jcrdb name of the database used to store user data and objects
      jcr jcr user for jcrdb.
      dbpassword password for the jcr user for the jcrdb

    For one DB for all features,

      db2 "connect to wpsdb user db2admin using db2admin"
      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 wpsdb"
      db2 "terminate"

  8. On the DB2 server system, check the services file and specify DB2 connection and interrupt service ports...

      db2c_db2inst1 port1/tcp

    ...where db2inst1 is the default instance and port1 is the TCP port DB2 listens on. Replace port1 with the port number that assigned to the DB2 connection service in your DB2 server installation

    The /etc/services file is located under...

      %systemroot%/system32/drivers/

    ...where %systemroot% is the location of the operating system.

    For example...

      C:/Windows/system32/drivers/etc/services

  9. For JDBC Type 2 connections, on DB2 Connect, edit /etc/services file and specify the DB2 connection service port for the remote DB2 instance:

      db2c_db2inst1 port1/tcp

    ...where db2inst1 is the name of the DB2 instance ID on the system, and port1 with the port number that is assigned to the DB2 connection service in your 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.

  10. On the DB2 server system, set DB2COMM to TCP/IP...

      db2set DB2COMM=TCPIP

  11. 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 in substep b, such as db2c_db2inst1.

  12. For JDBC Type 2 connections, on DB2 Connect, set DB2COMM to TCP/IP...

  13. For JDBC Type 2 connections, 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 alias name of the database server that you are defining for the WAS node name. The alias name can contain one to eight characters.
      database_server_node fully qualified host name of your database server system.
      connection_service_port name of the DB2 connection service port that is configured in the /etc/services file on the database server system.

  14. For JDBC Type 2 connections, catalog the WebSphere Portal databases on DB2 Connect...

    The alias for each database must be different from the 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"

    ...where...

      remote_db_name_domain cataloged name of the databases on the server system for each domain.
      domain_alias_name database alias names that you are defining.
      remote_db_node_alias name that was used previously when you cataloged the TCP/IP node in the previous step.

  15. For JDBC Type 2 connections, log out of DB2 Connect by entering:

      db2 "terminate"

  16. For JDBC Type 2 connections, on DB2 Connect, test your remote connection from the DB2 command window:

      db2 "connect to alias_name user username using password"

    ...where alias_name is the alias name that you defined above and username is the database user.


Parent topic:

Configure WebSphere Portal to use DB2


Previous topic:

Create users


Next topic:

Assigning custom table spaces


Related information

IBM DB2 Database for Linux, UNIX, and Windows Information Center