Creating the DB2 database

WSRR can be configured using up to four databases. They are the Business Space database, the WSRR database, the activity logging database, and the service integration bus database. These databases can be separate or the WSRR, activity logging, and service integration bus tables can be created in the Business Space database.

About this task

attention You must create the database using the UTF-8 code set. No other code sets will function correctly.

These instructions are for a UNIX or LINUX server. You can replace the directory paths to use them as a guide for a Windows server. Windows-based systems use backslashes (\) to delimit directories in a directory path.

These instructions apply to both DB2® Restricted Enterprise Server Edition and DB2 Express® Edition.

Table 1. Values used to create DB2 database in WSRR
Value Description
$ACTDB_NAME Activity logging database name. Uppercase and 8 characters or less.
$ACTDB_SCHEMA Activity logging database schema. Uppercase and 8 characters or less.
$ACTDB_USER Activity logging database user ID. Must be lowercase.
$DB_NAME WSRR database name. Uppercase and 8 characters or less.
$DB_SCHEMA WSRR database schema. Uppercase and 8 characters or less.
$DB_USER WSRR database user ID. Must be lowercase.
$DB2INST The DB2 instance.
$DB2INST_OWNER The user ID of the owner of the DB2 instance (or of a DB2 administrator on Windows).
$DB2PATH The DB2 installation directory.
$DB2TSDIR The directory to store the DB2 tablespaces in. The $DB2TSDIR directory must already exist.
$SIBDB_NAME Service integration bus database name. Uppercase and 8 characters or less.
$SIBDB_SCHEMA Service integration bus database schema. Uppercase and 8 characters or less.
$SIBDB_USER Service integration bus database user ID. Must be lowercase.
$WSRR_SQLPATH Path to the WSRR SQL files, for example: install_root/WSRR/dbscripts/db2 or install_media_root/dbscripts/db2.
$BS_SQLPATH Path to the Business Space SQL files, for example: install_root/dbscripts/BusinessSpace/DB2 or install_media_root/dbscripts/BusinessSpace/DB2
For Linux operating system For Unix operating system If you are using DB2 V9.1 or DB2 V9.5 on UNIX or Linux, you must edit the configureDb2.sh file as follows:
  1. Locate the following section in the script file:
    # FOR DB2 versions prior to 9.7, the following settings are required:
#db2set DB2_SKIPINSERTED=ON
#db2set DB2_SKIPDELETED=ON
  2. Uncomment the lines as follows:
    # FOR DB2 versions prior to 9.7, the following settings are required:
db2set DB2_SKIPINSERTED=ON
db2set DB2_SKIPDELETED=ON
  3. Comment out the following lines:
    db2set DB2_SKIPINSERTED=
db2set DB2_SKIPDELETED=
    as follows:
    #db2set DB2_SKIPINSERTED=
#db2set DB2_SKIPDELETED=
  4. Save the changes to the file
For Windows operating system If you are using DB2 V9.1 or DB2 V9.5 on Windows, you must edit the configureDb2.bat file as follows:
  1. Locate the following section in the script file:
    rem FOR DB2 versions prior to 9.7, the following settings are required:
rem db2set DB2_SKIPINSERTED=ON
rem db2set DB2_SKIPDELETED=ON
  2. Uncomment the lines as follows:
    rem FOR DB2 versions prior to 9.7, the following settings are required:
db2set DB2_SKIPINSERTED=ON
db2set DB2_SKIPDELETED=ON
  3. Comment out the following lines:
    db2set DB2_SKIPINSERTED=
db2set DB2_SKIPDELETED=
    as follows:
    rem db2set DB2_SKIPINSERTED=
rem db2set DB2_SKIPDELETED=
  4. Save the changes to the file

Procedure

  1. Make sure your DB2 instance is configured for TCP/IP access. For example, as your DB2 instance owner, and replacing $DB2INST with your WSRR DB2 instance name, run: 
    db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME db2c_$DB2INST
    Ensure that the db2c_$DB2INST service is defined in /etc/services. An example of the definition in the /etc/services file is:
    db2c_db2inst1 50000/tcp

    After running the db2 update command, stop and restart the database:

    db2stop
db2start

    To check that the value has been set correctly, run the following command and check the value for TCP/IP Service name:

    db2 get database manager configuration

    For example, after setting SVCENAME to db2c_db2inst1, the database manager configuration would include this line:

    TCP/IP Service name (SVCENAME) = db2c_db2inst1
    On Windows run the following script to configure some essential database settings before creating the database:
    • For Windows operating system 
      $WSRR_SQLPATH\configureDb2.bat
  2. Create the Business Space database and tables by completing the following steps:
    1. Copy the database creation script files to your database server.
    2. Before you run the script, replace all instances of @SCHEMA@ in the following files with your chosen Business Space schema name: $BS_SQLPATH/createSchema_BusinessSpace.sql, $BS_SQLPATH/createTablespace_BusinessSpace.sql, $BS_SQLPATH/createTable_BusinessSpace.sql and $BS_SQLPATH/createGrant_BusinessSpace.sql.
    3. If you are creating a new database, edit the $BS_SQLPATH/createDatabase.sql file, and replace all instances of @DB_NAME@ with the name of the database that you want to create.
    4. As your DB2 instance owner run: For Linux operating system For Unix operating system 
      $BS_SQLPATH/createDBTables_BusinessSpace.sh DB_user DB_name createDB
      For Windows operating system 
      $BS_SQLPATH/createDBTables_BusinessSpace.bat DB_user DB_name createDB
      Where DB_name is the name of the database that will contain tables for Business Space. If you are creating a single database for your WSRR installation, then WSRR, activity logging, and service integration bus tables are created in this database too. If you are creating tables in an existing database, omit the createDB argument.
  3. Configure the WSRR database.
    1. Edit the file $WSRR_SQLPATH/createWsrrDb.sql
      • If you are creating a new database, uncomment all lines starting -- db --
      • Replace all instances of __DBNAME__ with the value of $DB_NAME
    2. As your DB2 instance owner run:
      db2 -tf $WSRR_SQLPATH/createWsrrDb.sql
      Note: If you are using DB2 Version 9.5 or earlier, then when you run the createWsrrDb.sql script, the following error is reported: 
      SQL0104N An unexpected token "cur_commit" was found following "USING".
Expected tokens may include: "ADSM_MGMTCLASS". SQLSTATE=42601
      You can ignore this error.
      • For Windows operating system Restart DB2 using: 
        db2stop
db2start
      • For Linux operating system For Unix operating system Run the following script as your database instance owner to configure some essential database settings and to restart the database: 
        $WSRR_SQLPATH/configureDb2.sh
  4. Configure the activity logging database.
    1. Edit the file $WSRR_SQLPATH/createActDb.sql
      • If you are creating a separate database, uncomment all lines starting -- db --
      • If $ACTDB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • Replace all instances of __DBNAME__ with the value of $ACTDB_NAME
      • Replace all instances of __DB2TSDIR__ with $DB2TSDIR
      • Replace all instances of __DBSCHEMA__ with $ACTDB_SCHEMA
      • Replace all instances of __DBUSER__ with $ACTDB_USER
      • Replace all instances of __DBSYSUSER__ with $DB2INST_OWNER
    2. As your DB2 instance owner run: 
      db2 -tf $WSRR_SQLPATH/createActDb.sql
    3. For Windows operating system Restart DB2 using: 
      db2stop
db2start
    4. For Linux operating system For Unix operating system Run the following script as your database instance owner to configure some essential database settings and to restart the database: 
      $WSRR_SQLPATH/configureDb2.sh
  5. Configure the service integration bus database.
    Note: Using WSRR without a service integration bus is not supported.
    • Edit the file $WSRR_SQLPATH/createSibDb.sql
      • If you are creating a separate database, uncomment all lines starting -- db --
      • If $SIBDB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • Replace all instances of __DBNAME__ with the value of $SIBDB_NAME
      • Replace all instances of __DB2TSDIR__ with $DB2TSDIR
      • Replace all instances of __DBSCHEMA__ with $SIBDB_SCHEMA
      • Replace all instances of __DBUSER__ with $SIBDB_USER
      • Replace all instances of __DBSYSUSER__ with $DB2INST_OWNER
    • As your DB2 instance owner run: 
      db2 -tf $WSRR_SQLPATH/createSibDb.sql
    • For Windows operating system Restart DB2 using: 
      db2stop
db2start
    • For Linux operating system For Unix operating system Run the following script as your database instance owner to configure some essential database settings and to restart the database: 
      $WSRR_SQLPATH/configureDb2.sh
  6. Create the WSRR Tablespaces.
    1. Edit the file $WSRR_SQLPATH/createWsrrTablespace.sql
      • If $DB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • Replace all instances of __DBNAME__ with the value of $DB_NAME
      • Replace all instances of __DBSCHEMA__ with $DB_SCHEMA
      • Replace all instances of __DBSYSUSER__ with $DB2INST_OWNER
    2. As your DB2 instance owner run: 
      db2 connect to $DB_NAME
      db2 -tf $WSRR_SQLPATH/createWsrrTablespace.sql
  7. Create the database tables for the WSRR database.
    1. In each of createWsrrTables1.sql, createWsrrTables2,sql and createWsrrProcs.sql:
      • Replace all instances of __DBNAME__ with $DB_NAME
      • Replace all instances of __DBSCHEMA__ with $DB_SCHEMA
      • Replace all instances of __DBUSER__ with $DB_USER
      • If the user creating the tables (the DB2 instance owner) is different to the db user (__DBUSER__), then uncomment all the lines starting -- GRANT.
    2. As $DB2INST_OWNER run: 
      db2 connect to $DB_NAME
      db2 -tf $WSRR_SQLPATH/createWsrrTables1.sql 
      db2 -tf $WSRR_SQLPATH/createWsrrProcs.sql 
      db2 -tf $WSRR_SQLPATH/createWsrrTables2.sql
      db2 terminate
  8. Create the database tables for the activity logging database.
    Note: If you did not create the separate activity logging database in step 3, these tables are still required but must instead go in the Business Space database.
    1. In $WSRR_SQLPATH/createActTables.sql :
      • Replace all instances of __DBSCHEMA__ with $ACTDB_SCHEMA
      • Replace all instances of __DBUSER__ with $ACTDB_USER
      • If the user creating the tables (the DB2 instance owner) is different to the db user (__DBUSER__), then uncomment all the lines starting -- GRANT.
    2. As $DB2INST_OWNER run: 
      db2 connect to $ACTDB_NAME 
      db2 -tf $WSRR_SQLPATH/createActTables.sql  
      db2 terminate
  9. Create the database tables for the service integration bus database.
    Note: If you did not create the separate service integration bus database in step 4, these tables are still required but must instead go in the Business Space database.
    1. In $WSRR_SQLPATH/createSibTables.sql:
      • Replace all instances of __DBSCHEMA__ with $SIBDB_SCHEMA
      • Replace all instances of __DBUSER__ with $SIBDB_USER
      • If the user creating the tables (the DB2 instance owner) is different to the db user (__DBUSER__), then uncomment all the lines starting -- GRANT.
    2. As $DB2INST_OWNER run: 
      db2 connect to $SIBDB_NAME
      db2 -tf $WSRR_SQLPATH/createSibTables.sql  
      db2 terminate

Results

The DB2 database is created.
Parent topic: Creating the WSRR database from scripts you have created