Table Of Contents

Creating tablespace and user in CEPM Database

Create tablespace and user in Oracle

Create tablespace and user in DB2

Creating tablespace and user in CEPM Database

---

It is assumed that the database is already installed and running, and you have the system password. Following examples contain sample scripts, wherein you can modify the parameters as per your requirement.

Create tablespace and user in Oracle

In this example, the CEPM tables are being installed in Oracle with userid 'cepmUser' on Linux.

I. Create tablespace for the CEPM data:

---

Step 1 Create a script called CreateCepmTableSpace.sql as given below:

REM connect "SYS"/"&&sysPassword" as SYSDBA

set echo on

spool /tmp/CreateCepmTbs.log

CREATE BIGFILE TABLESPACE CEPM.DATA

DATAFILE '/home/oracle/oracle/product/10.2.0/oradata/cepmdb/CEPM_DATA.dbf' SIZE 2048M

BLOCKSIZE  8192 

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1280K

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

alter database datafile

   '/home/oracle/oracle/product/10.2.0/oradata/cepmdb/CEPM_DATA.dbf'

autoextend ON next 32m maxsize 32048M

spool off

exit

Step 2 Modify the path for the datafile with the directory path where you wish your data to be resided in.

Step 3 Select filename from dba_data_files to find out where other datafiles in the database are.

II. Create the database user:

Step 4 Create a script called CreateCepmUser.sql to create a user as given below:

REM connect "SYS"/"&&sysPassword" as SYSDBA

set echo on

spool /tmp/CreateCepmUser.log

CREATE USER &&cepmUser IDENTIFIED BY &&cepmPassword

      default tablespace CEPM_DATA

      temporary tablespace temp;

grant create session to &cepmUser;

grant alter session to &cepmUser;

grant unlimited tablespace to &cepmUser;

grant create table to &cepmUser;

grant create cluster to &cepmUser;

grant create synonym to &cepmUser;

grant create view to &cepmUser;

grant create sequence to &cepmUser;

grant create database link to &cepmUser;

grant create procedure to &cepmUser;

grant create trigger to &cepmUser;

grant create type to &cepmUser;

grant create operator to &cepmUser;

grant create indextype to &cepmUser;

grant connect to &cepmUser;

grant create any type to &cepmUser;

grant create any view to &cepmUser;

grant query rewrite to &cepmUser;

grant create job to &cepmUser;

spool off

exit

Step 5 Run it with the command:

sqlplus system as sysdba @CreateCepmUser

•The prompt for "Enter password" is the system password. Let's create the account cepm/cepm by running this script.

•The prompt for 'Enter value for cepmuser' is the name of the cepm schema to be created.

•The prompt for 'Enter value for cepmpassword' is the new password that will be used for the new userid you are creating.

Make sure the user is created successfully and all grants succeed.

On execution of the above scripts, your Oracle DB tablespace and user are created with necessary privileges granted.

Create tablespace and user in DB2

In this example, the CEPM tables are being installed in DB2 with userid 'cepmUser'. To do this, run the following commands in the DB2 Editor or in command prompt:

---

Step 1 Create Database

CREATE DATABASE cepm ON 'C:' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM

CONNECT TO <DB NAME>;

This will create the database called `cepm' on C:folder.

Step 2 Create the required Buffepools

CREATE BUFFERPOOL <cepmbuf1> IMMEDIATE  SIZE 20000 PAGESIZE 32 K ;

CREATE BUFFERPOOL <cepmbuf2> IMMEDIATE  SIZE 20000 PAGESIZE 32 K ;

Step 3 Create the required Tablespaces

CREATE  REGULAR  TABLESPACE SECREG PAGESIZE 32 K  MANAGED BY SYSTEM  USING ('<CONTAINER 
PATH>' ) 

EXTENTSIZE 16 

PREFETCHSIZE 16 

OVERHEAD 24.100000

TRANSFERRATE 0.900000;

BUFFERPOOL  <cepmbuf1> 

CREATE  LARGE  TABLESPACE SECLRG PAGESIZE 32 K  MANAGED BY DATABASE  USING ( FILE '<PATH>' 
192000 ) 

EXTENTSIZE 16 

OVERHEAD 10.5 

PREFETCHSIZE 16 

TRANSFERRATE 0.14 

BUFFERPOOL  <SECBUF1>;

CREATE  SYSTEM TEMPORARY  TABLESPACE SECUSER1 PAGESIZE 32 K  MANAGED BY SYSTEM  USING 
('<CONTAINER PATH>' ) 

EXTENTSIZE 16 

OVERHEAD 10.5 

PREFETCHSIZE 16 

TRANSFERRATE 0.14 

BUFFERPOOL  <SECBUF2> ;

CREATE  USER TEMPORARY  TABLESPACE SECUSER2 PAGESIZE 32 K  MANAGED BY DATABASE  USING ( 
FILE '<PATH>' 160000 

 EXTENTSIZE 16 

 OVERHEAD 10.5 

 PREFETCHSIZE 16 

 TRANSFERRATE 0.14 

 BUFFERPOOL  <SECBUF2> ;

CONNECT RESET;

Step 4 Create Schema

CREATE SCHEMA <SCHEMA NAME> AUTHORIZATION <AUTHORISED USERNAME>

Step 5 Grant necessaryprivileges

GRANT DBADM ON DATABASE TO USER <USERNAME>

REVOKE DBADM ON DATABASE TO USER  <USERNAME>

GRANT USE OF TABLESPCE SECLRG TO USER <USERNAME>

GRANT USE OF TABLESPCE SECREG TO USER <USERNAME>

GRANT USE OF TABLESPCE SECUSR1 TO USER <USERNAME>

GRANT USE OF TABLESPCE SECUSR2 TO USER <USERNAME>

Step 6 After the necessary tablespace and schema are created in the DB, tune the DBM parameter by running the following command;

update dbm cfg using SHEAPTHRES 20000

Step 7 Tune the DB with the following DB parameter

update db cfg using DBHEAP 10000;

update db cfg using LOCKLIST 35000;

update db cfg using APP_CTL_HEAP_SZ 8192;

update db cfg using SORTHEAP 10000;

update db cfg using STMTHEAP 15000;

update db cfg using APPLHEAPSZ 60000;

update db cfg using AVG_APPLS 25;

update db cfg using LOGFILSIZ 15000;

update db cfg using LOGPRIMARY 10;

update db cfg using LOGSECOND 4;