3.1 Database Configuration

Overview

In general, an empty database must be configured before installing Connect.

If installing on Oracle, you must create an Oracle schema user, and tablespaces for storing the Connect schema.
If installing on Microsoft SQL Server, you must create a new database, a login with the database owner role for the database, and file groups for storing the Connect schema.
If installing on MySQL, you must create a new database, and one or more logins so that Connect can access the MySQL database from every inside node.

Configuring Oracle

The Oracle database used for Connect should have its character set configured to UTF8. Make sure that the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters are both set to UTF8. If the UTF8 character set is not used, Connect may not be able to store content from all languages properly.

Installing Oracle Tablespaces

Before the Connect schema objects can be created, the Connect tablespaces must exist. Connect does not create these tablespaces automatically; they must be created by the Database Administrator at the customer site.

The Connect software distribution contains sample scripts that can be edited and run to create these tablespaces. The sample scripts are found in:

$KCHOME\kc\initdb\oracle\setup\tablespaces_10g.sql
$KCHOME\kc\initdb\oracle\setup\tablespaces_ETL_10g.sql

These sample scripts should be used for Oracle 10G and later. They use locally managed, unlimited tablespaces. Note that Oracle tablespace creation scripts provided with the software distribution are just guidelines. You will need to refine the scripts for your physical database layout. Also, please refer to the Connect Database Administration Guide for additional information on sizing tablespaces for schema growth.

Creating Oracle User

Log in as the system user and create a new Oracle user for the Connect application:

create user connect10 identified by PASSWORD;
grant alter session to connect10;
grant create procedure to connect10;
grant create sequence to connect10;
grant create session to connect10;
grant create table to connect10;
grant create trigger to connect10;
grant create view to connect10;

The following permissions are required by the ETL2 loader package and must be granted by an Oracle SYSDBA:

grant select on v_$session to connect10;
grant execute on dbms_lock to connect10;

In addition, the following statement should be executed for each tablespace:

alter user connect10 quota unlimited on <tablespace_name>;

For example:

alter user connect10 quota unlimited on CRM_TABLES;
alter user connect10 quota unlimited on CRM_INDEX;
alter user connect10 quota unlimited on CUST_STATS_TABLES;
alter user connect10 quota unlimited on CUST_STATS_INDEX;
alter user connect10 quota unlimited on CUST_QUEUE_TABLES;
alter user connect10 quota unlimited on CUST_QUEUE_INDEX;
alter user connect10 quota unlimited on CUST_PREF_TABLES;
alter user connect10 quota unlimited on CUST_PREF_INDEX;
alter user connect10 quota unlimited on CUST_ATTRIB_TABLES;
alter user connect10 quota unlimited on CUST_ATTRIB_INDEX;
alter user connect10 quota unlimited on CUST_SUBSCRIPTION_TABLES;
alter user connect10 quota unlimited on CUST_SUBSCRIPTION_INDEX;
alter user connect10 quota unlimited on CUST_CONVERSION_TABLES;
alter user connect10 quota unlimited on CUST_CONVERSION_INDEX;
alter user connect10 quota unlimited on CRM_CUST_TABLES;
alter user connect10 quota unlimited on CRM_CUST_INDEX;
alter user connect10 quota unlimited on CRM_QUEUE_TABLES;
alter user connect10 quota unlimited on CRM_QUEUE_INDEX;
alter user connect10 quota unlimited on CRM_HISTORY_TABLES;
alter user connect10 quota unlimited on CRM_HISTORY_INDEX;
alter user connect10 quota unlimited on CRM_AGG_TABLES;
alter user connect10 quota unlimited on CRM_AGG_INDEX;
alter user connect10 quota unlimited on CRM_ETL_TABLES;
alter user connect10 quota unlimited on CRM_VC_TABLES;
alter user connect10 quota unlimited on CRM_VC_INDEXES;

Note: Connect provides control over the table and index tablespaces used for each table. This mapping is controlled by the schema.properties file in $KCHOME/initdb/oracle. The initdb and updatedb programs (described in the following section) will substitute these tablespace names into any DDL statements executed.

Configuring SQL Server

Connect on SQL Server requires a SQL Server database and a login that is mapped to the dbo user for the database.

Creating a Database User

To create a login, use the create user statement or create a new login the SQL Server Management Console.

Creating a Database

Connect provides a sample script in $KCHOME\initdb\sql\setup that creates a database along with filegroups needed by Connect. You should edit the file names used by each filegroup before running this script.

Set Database Owner

The final step is to map the user login created above to the database's dbo user. This can be done using the sp_changedbowner procedure or using SQL Server Management Studio

Configuring MySQL

Connect on MySQL requires a MySQL user and database. The following commands should be performed as the root user:

create user 'connect10' identified by 'PASSWORD';
create database BRICKST_CONNECT character set 'UTF8';
grant all on BRICKST_CONNECT.* to 'connect10';