Linux clustered server: Create a remote or local DB2 database manually

A remote database resides on a different system than WebSphere Portal. When you use a remote DB2server, manually create the databases that are required by WebSphere Portal.

Before creating these databases, note the following information:

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

  1. Log in as a DB2 instance system authority. For example, you can log in as db2inst1 as the DB2 instance owner.

  2. Initialize a DB2 command environment by opening a command prompt and executing the db2profile of the DB2 instance. For example, execute . /home/db2inst1/sqllib/db2profile, where db2inst1 is the DB2 instance owner of DB2 instance.

      The prompt changes to OS shell prompt, for example: $. In this mode, type db2 at the beginning of each command and use double quotation marks ("") around the entire command. The following example shows how to use the double quotes; it is not an actual command that you run:

      db2 "CREATE REGULAR TABLESPACE SMS PAGESIZE 4 K MANAGED BY SYSTEM USING ('sms') BUFFERPOOL SMSPOOL"
      If you are using the Command Line Processor (CLP), refer to 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 you are not using the CLP and are entering commands from OS shell prompt, for example: $.

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

    Environment Description
    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. (Important) Failure to complete this step will result in an unsuccessful database transfer. Run the following commands on the DB2 server system to create the necessary 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"

  5. Complete the following:

    1. On the DB2 server system, run the following commands. This step is only required for the IBMJava 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 ID that has administrative authority.

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

        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"

    2. For JDBC Type 2 connections only: On the DB2 client, 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:

        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 that is assigned to the DB2 connection service in 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. For JDBC Type 2 connections only: 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 that is specified above, .

    4. For JDBC Type 2 connections only: On the DB2 client, set DB2COMM to TCP/IP by using the db2set command db2set DB2COMM=tcpip.

    5. For JDBC Type 2 connections only: 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 you 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 database server system.

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

    6. For JDBC Type 2 connections only, 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 you 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. For JDBC Type 2 connections only: Log out of DB2 Connect by entering db2 "terminate".

    8. For JDBC Type 2 connections only, on DB2 Connect, test 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 that you defined above, username is the database user, and password is the password assigned to the database user.


Parent

Linux clustered server: Create DB2 databases

 


+

Search Tips   |   Advanced Search