Set up an Oracle database to work with the Rational® Connector for SAP Solution Manager.
Before you begin
To create and set up an Oracle database, it is assumed that
the user who is preparing the database must have database administration
authority over the database and that the database, table space storage,
and appropriate storage configuration is created by a user with system
administration authority.
Also, one of the following supported Oracle
editions must be installed on a computer to be used as the database
server. This computer can be different from the one that the Jazz™ Team Server runs on:
- Oracle Database 11g Standard or Enterprise Edition Release 2
- Oracle Database 10g Standard or Enterprise Edition Release 2
Important: - The required Java™ Database
Connectivity (JDBC) driver for both Oracle 10g and 11g is ojdbc5.jar 11.2.0.2.0
or higher. Also, always update the database to the latest database
version. For example, if the database server is 11.2, then you must
be running 11.2.0.2.0 or later.
- The following configuration parameters and table space options
are examples and might not entirely apply to your environment. For
example, if you cannot use the AUTOEXTEND setting,
then the files must be large enough to allow for growth. Or, if you
cannot use the UNLIMITED size quota for the IBM® Rational Connector for SAP Solution Manager
database user, then make sure to allow enough space for rapid growth.
About this task
These examples are for one Oracle database with separate
database users who are dedicated to each application.
Note: The database
that you create for the Rational Connector
must be formatted to use UTF-8 as the character set. By default, Oracle
does not create a database with the UTF-8 character set. When you
create a new database, ensure that you select AL32UTF8 in
the Oracle DB setup.
Procedure
- As a system administrator, create a database with UTF-8
encoding for the Rational Connector.
For scalability, select the Shared Server Mode option
as your database connection. With shared server mode, several client
connections share an allocated pool of resources.
- Open an SQL *Plus window, and create the database table
spaces:
CREATE TABLESPACE <sap user tablespace>
DATAFILE '<path and name of .dbf file>'
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE <sap user temp tablespace>
TEMPFILE '<path and name of the temp .dbf file>'
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
- Create an Oracle database user for each new table space.
After the table spaces are created, a special Oracle user
must be created for each application to use the Oracle database. For
multiple Jazz servers that run
on one Oracle database, an Oracle database user must be created for
each application. The Oracle database user must be able to create
database objects in its table space (either unlimited or with a space
quota) with privileges to create session, table, procedure, and views.
In
these examples, replace the phrases in the angle brackets.
CREATE USER <sap db user> IDENTIFIED BY <password> DEFAULT TABLESPACE <sap user tablespace>
QUOTA UNLIMITED ON <sap user tablespace> TEMPORARY TABLESPACE <sap user temp tablespace>;
After
the user is created, give the user the appropriate permissions to
initialize the connector database. The following SQL command can be
used to grant the appropriate permissions:
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO <sap db user>;
For
more information about Oracle data warehouse setup, see this wiki.
- You must specify the JAR file location for JDBC as follows:
- Locate the Oracle JDBC driver ojdbc6.jar JAR
file. The location can vary depending on the Oracle product and operating
system. You can download the supported Oracle JDBC driver (ojdbc6.jar
11.2.0.3) from www.oracle.com.
- Copy this file to a directory of your choice on the
application server.
- If you are connecting to your Oracle database by using WebSphere® Application Server
with JRE 6.0, set the ORACLE_JDBC_DRIVER_FILE environment
variable to point to the JRE 6.0 version of the JDBC driver named ojdbc6.jar.
This driver can be installed in a directory of your choice on the
application server. For Tomcat 7.x, put ojdbc6.jar into
the SAPCInstallDir/server/Tomcat/lib directory.
- If you are using WebSphere Application
Server, you must also add a custom property that points to the ojdbc6.jar JDBC
driver. For more information, see Setting up WebSphere Application Server.
- To configure your database connections and create database
tables complete this step. There is a DDL SQL script at SAPCInstallDir/Oracle_Tables.ddl.
Run that script in the SQL *Plus window.
- Edit the <SAPCInstallDir/server/conf/sapconnector.properties file.
Comment out the default Derby DB lines, uncomment out the Oracle
lines and change the host name, instance, user, and password fields.
- Save the file and close it.