AIX stand-alone: Create a remote or local DB2 database manually

When we use a remote DB2 server, manually create the required databases.

Before creating these databases:

• If we are using DB2 JDBC Type 4 driver:

  • 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.

  • Copy the Type 4 jar files from the remote DB2 server to the WebSphere Portal server.
    db2_home/java

  • These instructions assume that a remote DB2 server is already installed and running.

• For Type 2 drivers:

  • The DB2 client software must be correctly configured to connect to the remote DB2 server instance, for example, db2inst1.

  • These instructions assume that a remote DB2 server and DB2 client are already installed and running.

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

All DB2 instructions in this topic apply to DB2 PureScale except where specifically noted.

1. Log in as a DB2 instance system authority.

   For example, we can log in as db2inst1 as the DB2 instance owner.

2. Initialize a DB2 command environment.

   For example, execute...

   . /home/db2inst1/sqllib/db2profile

   db2inst1 is the DB2 instance owner of the DB2 instance.

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

   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. Create 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. On the DB2 server systems to create bufferpools and table spaces for the IBM Java Content Repository database (jcrdb). This step is only required for the IBM Java Content Repository database.
   • 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 Ithat has administrative authority.

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

   The commands in this step are different for DB2 and DB2 pureScale. Run the appropriate commands for the environment.

   DB2

   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"
   

   DB2 pureScale

   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 BUFFERPOOL ICMLSMAINBP32"
   db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
   db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K 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"
   

6. For JDBC Type 2 drivers, perform additional steps.

   See the Type 2 configuration section for more instructions.

Parent: AIX stand-alone: Create DB2 databases