Creating the system and Encyclopedia volume schemas and iserver user in a pre-existing DB2 database
The following SQL scripts provide an example of DDL statements that create the database, schema owners, and iHub application user, then grant privileges in a pre-existing DB2 database. These steps are not necessary when adding an Encyclopedia volume to an existing schema.
The DB2 database administrator (DBA) may need to modify these SQL command examples for the specific DB2 installation. In the commands, substitute system and schema names appropriate to your environment.
Creating user accounts
DB2 uses operating system accounts instead of internally defined database users. A database user must exist as an operating system user account, using the native security mechanisms that the operating system provides, before a user can be referenced in a DB2 system. Once a user exists in the operating system, the DB2 system administrator can assign privileges to that user using DDL statements.
Creating a database
Actuate requires a DB2 database to support VARGRAPHIC columns. DB2 does not support UCS-2 or UTF-16 as the primary encoding for a database. DB2 also sizes the VARCHAR data type in bytes, not characters. To work around these issues, iHub uses VARGRAPHIC instead of VARCHAR. VARGRAPHIC stores UTF-16 data and sizes this data in characters.
Actuate also requires a DB2 database to use a case-insensitive collation, such as UCA500R1_LEN_S2, which is not the default. DB2 supports this functionality only in DB2 9.5 Fix Pack 1 and later versions.
To create the iserver database, connect to the DB2 system as a user with full administrator privileges and execute the following SQL command:
CREATE DATABASE iserver
AUTOMATIC STORAGE YES
USING CODESET UTF-8
TERRITORY US
COLLATE USING UCA500R1_LEN_S2
PAGESIZE 8192
Creating the system schema owner
The iHub system schema owner has all privileges on the schema used for the system data store and can grant privileges to other users. The system schema owner must be able to create database objects, such as tables and indexes.
To create the iHub system schema owner and grant privileges in a pre-existing DB2 database, execute the following command:
GRANT CONNECT, LOAD ON DATABASE TO USER ac_corp_system;
Creating the Encyclopedia volume schema owner
The Encyclopedia volume schema owner has all privileges on the schema used for the volume data store and can grant privileges to other users. The Encyclopedia schema owner must be able to create database objects, such as tables and indexes.
To create the Encyclopedia volume schema owner and grant privileges in a pre‑existing DB2 database, execute the following command:
GRANT CONNECT, LOAD ON DATABASE TO USER ac_corp;
Creating the iHub application user
iHub connects to the database as an application user. The application user requires only the privileges necessary to perform basic SQL Data Manipulation Language (DML) operations, such as SELECT, INSERT, UPDATE, and DELETE. This user does not require privileges to create or modify the structure of the database.
The iHub installation process automatically grants the schema privileges required by the application user. The RDBMS database administrator does not have to configure these privileges manually.
To create the iserver user in a pre-existing DB2 database, execute the following command:
GRANT CONNECT ON DATABASE TO USER iserver;
Creating the system schema
The system schema must have the same name as the system schema owner. To create a system schema named ac_corp_system and grant ownership to the user named ac_corp_system, execute the following command:
CREATE SCHEMA ac_corp_system AUTHORIZATION ac_corp_system;
Creating the Encyclopedia volume schema
In an iHub installation, the Encyclopedia volume schema must have the same name as the Encyclopedia volume schema owner. To create an Encyclopedia volume schema named ac_corp and grant ownership to the user named ac_corp, execute the following command:
CREATE SCHEMA ac_corp AUTHORIZATION ac_corp;