7.3 Creating Database Definitions, Configuring the Event Data Store, and Connecting to External Databases

A database definition defines database settings required to establish a connection between Operations Center and a database that will be used by Operations Center for one or more of the following:

The following sections cover the steps necessary to setup and maintain a connection to an external database using a database definition:

7.3.1 Setting Up an External Database

Understanding General Requirements and Configurations

For database requirements, see Databases in the Operations Center 5.6 Getting Started Guide.

The following are general suggestions when setting up and configuring your external database for the Service Warehouse or the Event Data Store:

  • Create a formula user with appropriate create/drop/alter object permissions. This allows the Operations Center server to create the required database tables for the Service Warehouse.

  • Assign a separate default tablespace to the formula user instead of using the standard user tablespace. This isolate Operations Center from any existing data sources and provide a container for all performance and SLA data which can be managed separately.

  • The default tablespace for the formula database user should have enough space available to create the necessary tables and maintain at least 1 weeks worth of performance/alarm history data (approximately 200 MB).

  • Configure your database to handle a substantial amount of data. The CreateFormula.sql script provided for each database type creates a default user and tablespace settings for small Operations Center configurations. To build a database for a large configuration, consult the documentation provided by the database vendor. For additional sizing information, see Section 7.5.1, Sizing the Service Warehouse.

  • The schema is the type of data store. Define multiple schemas for a single database. When multiple schemas exist within the same database, a unique ID must be created for each schema. If you select multiple schemas for a single definition, do not select a schema that is already enabled in another database definition.

  • If auditing is enabled for your database, be aware that failed inserts to the BSAAlarmElements table will occur under some circumstances. Please disable logging of failed inserts to this table as it could negatively affect database performance.

Reviewing and Modifying Database Scripts

Operations Center ships with sample scripts for configuring an instance of each database type. These scripts are located in the appropriate database’s subdirectory under /OperationsCenter_install_path/databases/samples. The directories for sample databases are: dB2, Oracle, MSSQL (for Microsoft SQL Server), PostgreSQL, and Sybase.

A database administrator should review and modify these scripts to ensure creation of an appropriate database for the corporate infrastructure.

IMPORTANT:Before creating a database, refer to the subsequent section below on each specific database type for the information about setting up the specific database.

Configuring DB2

Refer to the following sections for steps on configuring an Operations Center database using a DB2 database:

DB2 Requirements

The database default bufferpool and table space size must be 16K or larger when DB2 is used as the Service Warehouse database.

Installing Generic JDBC Drivers

The drivers included with Operations Center do not support DB2. The generic JDBC drivers supplied with the DB2 distribution must be used. These drivers can be found in /DB2_root_directory/java.

To install the jdbc 2.0 drivers into the Operations Center installation:

  1. Verify your DB2 distribution is using the jdbc 2.0 drivers.

    1. Shutdown your DB2 server (including all services).

    2. Change directory to /DB2_root_directory/java12.

    3. Run the usejdbc20.bat script.

    4. Restart the DB2 server (including all services).

  2. Configure the DB2 database properties using the Operations Center Configuration Manager by entering the path to the DB2 driver archive in the properties panel.

    For example, /DB2_root_directory/java/db2java.zip.

    The Configuration Manager reads the archive and re-writes it to /OperationsCenter_install_path/classes/ext directory.

    This step is important since there are cases where the default IBM archive format is incompatible with the JRE distribution supplied with Operations Center.

  3. Restart the Operation Center server.

Running the Operations Center Script to Create the Database Instance

The Operations Center script used to create the sample Service Warehouse is 'CreateFormula.sql. The script must be executed by a user with admin privileges since it creates a new database, a bufferpool compatible with the Operations Center schema, and a sample tablespace for the Service Warehouse.

To run the script to create the database instance:

  1. Edit the /OperationsCenter_install_path/database/samples/db2/CreateFormula.sql script to customize the database instance parameters:

    • Update the installation location to a suitable directory.

      The default directory for the database and tablespaces is /OperationsCenter_install_path/databases/samples/db2.

    • Confirm the size for each data file.

      Search on the FILE string to review each occurance.

    • Update to create new buffer pools as necessary. Examples are provided in the script.

  2. Run the CreateFormula.sql script by issuing the following command:

    db2cmd -c db2 -t -z create.log -f CreateFormula.sql

    This script requires admin privileges.

  3. After the DB2 database instance is created, create a new Database Defintion for the database instance.

    The default value for the Listener Port property is 50000. If you setup DB2 to listen on a different port, specify the new port in this property. See the DB2 documentation for instructions on how to change the default listener port.

    For more information on creating database definitions, see Section 7.3.2, Creating and Editing a Database Definition.

  4. If you did not enable the database definition as a part of Step 3, right-click the database definition and select Enable Database Definition.

  5. If the database definition is for a single schema for Event Data Store, it is necessary to manually initialize the schema. Right-click the database definition, then select Initialize Database Schema.

    Normally, this step is not necessary since the Operations Center server creates all appropriate schemas when required.

Configuring MSSQL

Refer to the following sections for steps on configuring an Operations Center database using a MSSQL database:

Requirements for MSSQL Server Installation

While installing SQL Server, select Mixed Mode when specifying the security mode (authentication). Mixed Mode enables users to connect using Windows Authentication or SQL Server Authentication. Users who connect through a Microsoft Windows user account can use trusted connections (connections validated by Windows) in either Windows Authentication Mode or Mixed Mode. SQL Server Authentication is provided for backward compatibility.

Running the Operations Center Script to Create the Database Instance

The Operations Center script used to create the sample Service Warehouse is 'CreateFormula.sql. The script must be executed by a user with dba privileges since it creates the default database and user.

To run the script to create the database instance:

  1. Edit the /OperationsCenter_install_path/database/samples/mssql/CreateFormula.sql script to customize the database instance parameters:

    • Update the dataDir variable to point to a pre-existing directory outside the Operations Center installation directory.

      SET @dataDir = 'd:/OperationsCenter_install_path/database/mssql'

      The default directory for the database and tablespaces is /OperationsCenter_install_path/databases/samples/mssql.

      The specified directory must exist prior to executing the script.

    • Configure the initial and maximum tablespace sizes. The defaults specify an initial database size of 200 MB and a maximum size of 2000. Change these values if required.

      SET @initialSize = 200

      SET @maximumSize = 2000

      These values depend on available disk space and any operating system imposed file size limits.

    • The default database user and password created by CreateFormula.sql are formula and sesame respectively. If you prefer to use different values, change them by resetting the following variables:

      SET @username = N'formula'

      SET @password = N'sesame'

  2. Run the CreateFormula.sql script by issuing one of the following commands:

    • If using isql.exe:

      isql -n -U sa -P your-sa-password -S your-server-name -i CreateFormula.sql

    • If using Microsoft's SQL Query Analyzer:

      1. Launch the SQL Server Enterprise Manager from the Microsoft SQL Server program menu.

      2. Click Tools > SQL Query Analyzer.

      3. Click File and select Open, then browse to the location of the CreateFormula.sql script and select it.

      4. Click Tools and select Execute.

        The new Operations Center database is created.

    This script requires dba privileges.

  3. After the MSSQL database instance is created, create a new Database Definition for the database instance.

    The default value for the Listener Port property is 1433. If you setup SQL Server to listen on a different port, specify the new port in this property. See the SQL Server documentation for instructions on how to change the default listener port.

    For more information on creating database definitions, see Section 7.3.2, Creating and Editing a Database Definition.

  4. If you did not enable the database definition as a part of Step 3, right-click the database definition and select Enable Database Definition.

  5. If the database definition is for a single schema for Event Data Store, it is necessary to manually initialize the schema. Right-click the database definition, then select Initialize Database Schema.

    Normally, this step is not necessary since the Operations Center server creates all appropriate schemas when required.

Configuring Oracle

Refer to the following sections for steps on configuring an Operations Center database using an Oracle database:

Requirements for Oracle Database Installation

Check your current maximum number of connections parameter in the Service Warehouse database connection properties.The following properties must be set in the init<SID>.ora or init.ora in order for the Operations Center database connection pool to operate correctly:

  • processes = 200

  • open_cursors = 500

If Operations Center is configured to use more than 10 database connections, the 'processes' and 'open_cursors' properties should be adjusted as follows:

  • processes: number-of-connections * 20

  • open_cursors: number-of-connections * 50

The default rollback segment is usually too small for the Service Warehouse purge process to work. We recommend increasing it by * 4 to start, then monitor and adjust as the warehouse gets larger and starts purging old records as defined by the profiles.

Requirements for Database User Accounts, Table Spaces and Privileges

One Oracle database instance is sufficient for Operations Center's database requirements, but it is necessary to configure 4 different user accounts pointing to dedicated individual table spaces.

Add IDs and table spaces for Configstore, Service Warehouse, Event Data Store and Dashboard. The user privileges are the same for all databases, except Configstore which needs additional privileges. All table spaces must have auto extend enabled.

The following are basic examples to illustrate and understand the requirement:

User ID: Dashboard_username
Tablespace: DASHBOARD_FILESPACE
Expected Size: <5 GB
Role: APPLICATION_DEVELOPER
Privileges: CREATE SESSION and CREATE TABLE
User ID: Warehouse_username
Tablespace: WAREHOUSE_FILESPACE
Expected Size: Unknown but could be very large with hundreds of GBs depending
on your profiles and volume.
Role: APPLICATION_DEVELOPER
Privileges: CREATE SESSION and CREATE TABLE
User ID: EventDataStore_username
Tablespace: EVENTDATASTORE_FILESPACE
Expected Size: <5 GB
Role: APPLICATION_DEVELOPER
Privileges: CREATE SESSION and CREATE TABLE
User ID:Configstore_username
Tablespace: CONFIGSTORE_FILESPACE
Expected Size: <5 GB
Role: APPLICATION_DEVELOPER
Privileges: CREATE SESSION, CREATE TABLE, CREATE SEQUENCE and CREATE TRIGGER

Running the Operations Center Script to Create the Database Instance

The Operations Center script used to create the sample Service Warehouse is 'CreateFormula.sql. The script must be executed by an Oracle system user since it creates a default role, user, and a set of sample tablespace data files for the Service Warehouse.

To run the script to create the database instance:

  1. Verify you have a database instance setup for the Service Warehouse.

    To setup a special instance, use Oracle's Database Configuration Assistant.

  2. Edit the /OperationsCenter_install_path/database/samples/oracle/CreateFormula.sql script to customize the database instance parameters:

    • Change the userID, passwd,' and roleName variables if the defaults are not acceptable. The defaults are:

      • DEFINE userID = formula

      • DEFINE passwd = sesame

      • DEFINE roleName = formula_role

    • Customize the installation location by updating the dataDir variables.

      The default directory for the Operations Center database and tablespace data is $ORACLE_HOME/oradata.

      If the tablespaces are to be spread across multiple filesystems, set each variable individually. Otherwise, leave the dataDir[2-4] variables set to the defaults:

      • DEFINE dataDir1 = /fs01/oradata/FORMULA

      • DEFINE dataDir2 = /fs02/oradata/FORMULA

      • DEFINE dataDir3 = &dataDir1

      • DEFINE dataDir4 = &dataDir2

      The specified directory must exist and be owned by the Oracle user prior to executing the script..

    • Configure the initial and maximum tablespace sizes if necessary.

      The defaults specify an initial tablespace size of 200M and a maximum tablespace of 2GB:

      • DEFINE initialSize = 200M

      • DEFINE maximumSize = 2000M

      These values depend on available disk space and any operating system imposed file size limits. Because the AUTOEXTEND feature is enabled on all Operations Center tablespaces, the maximum size variable should be set.

  3. Run the CreateFormula.sql script by issuing the following command:

    sqlplus system

    Password: your-system-password

    SQL> @CreateFormula.sql

    SQL> exit;

    This script requires oracle system privileges.

  4. After the Oracle database instance is created, create a new Database Defintion for the database instance.

    The default value for the Listener Port property is 1521. If you setup Oracle to listen on a different port, specify the new port in this property. See the Oracle documentation for instructions on how to change the default listener port.

    For more information on creating database definitions, see Section 7.3.2, Creating and Editing a Database Definition.

  5. If you did not enable the database definition as a part of Step 3, right-click the database definition and select Enable Database Definition.

  6. If the database definition is for a single schema for Event Data Store, it is necessary to manually initialize the schema. Right-click the database definition, then select Initialize Database Schema.

    Normally, this step is not necessary since the Operations Center server creates all appropriate schemas when required.

Oracle RAC

Refer to the following sections for steps on configuring an Operations Center database using an Oracle RAC:

Configuring the Oracle RAC

If using Oracle RAC as the database for the Service Warehouse, a few configurations are required. These configurations are the same as when you configure Oracle RAC for configuration storage. For instructions, see Oracle RAC.

Running the Operations Center Script to Create the Database Instance

The Operations Center script used to create the sample Service Warehouse is 'CreateFormula.sql. The script must be executed by an Oracle system user since it creates a default role, user, and a set of sample tablespace data files for the Service Warehouse.

For more information on running the CreateFormula.sql on Oracle databases, see Running the Operations Center Script to Create the Database Instance.

Configuring PostgreSQL

Refer to the following sections for steps on configuring an Operations Center database using a PostgreSQL database:

Running the Operations Center Script to Create the Database Instance

The Operations Center script used to create the sample Service Warehouse is 'CreateFormula.sql.

To run the script to create the database instance:

  1. Edit the /OperationsCenter_install_path/database/samples/postgresql/CreateFormula.sql script to customize the database instance parameters:

    • Change the userID and passwd variables if the defaults are not acceptable. You must change the location variable in the CREATE TABLESPACE command as it must point to an existing directory to which the PostgreSQL server can modify. The defaults are:

      • CREATE USER formula PASSWORD 'formula' ;

      • CREATE TABLESPACE BSAWarehouse OWNER formula LOCATION '/var/lib/pgsql/data/BSAWarehouse';

      • CREATE DATABASE formula WITH OWNER formula TABLESPACE BSAWarehouse ;

  2. Run the CreateFormula.sql script by issuing the following command:

    psql -U admin_account -f CreateFormula.sql

  3. After the PostgreSQL database instance is created, create a new Database Defintion for the database instance.

    The default value for the Listener Port property is 5432. If you setup PostgreSQL to listen on a different port, specify the new port in this property. See the PostgreSQL documentation for instructions on how to change the default listener port.

    For more information on creating database definitions, see Section 7.3.2, Creating and Editing a Database Definition.

  4. If you did not enable the database definition as a part of Step 3, right-click the database definition and select Enable Database Definition.

  5. If the database definition is for a single schema for Event Data Store, it is necessary to manually initialize the schema. Right-click the database definition, then select Initialize Database Schema.

    Normally, this step is not necessary since the Operations Center server creates all appropriate schemas when required.

Configuring Sybase

Refer to the following sections for steps on configuring an Operations Center database using a Sybase database:

Requirements for Sybase Server Installation

The scripts in the sample Sybase directory create a moderate database consisting of a 200MB Operations Center log device and a 100M data device. Also, since the Service Warehouse uses a substantial amount of temp space for sorting during large queries, a new 50MB device for the tempdb database is created.

To support JDBC DatabaseMetaData methods, Sybase provides a set of stored procedures that jConnect calls for metadata about a database. These stored procedures must be installed on the Sybase server for the JDBC metadata methods to work. If the stored procedures for providing metadata are not already installed on your Sybase server, install them using the stored procedure scripts provided with jConnect. For complete instructions on installing stored procedures, see the Sybase jConnect for JDBC Installation Guide and Release Bulletin from your Sybase installation or go to Sybase’s online documentation for installing stored procedures.

Sybase Configuration Requirements

The following server configuration changes are required:

  • max_network_packet_size: set to 16384. Default is 2048.

  • procedure_cache_size: set to 256000. Default is 7000.

  • statement_cache_size: set to 256000. Default is 7000.

  • user_log_cache_size: set to 524288. Default is 4096.

The following PostgreSQL database configuration changes are required:

  • ddl_in_tran: set to true.

Running the Operations Center Script to Create the Database Devices and Database Instance

The Operations Center script used to create the sample Service Warehouse database devices is FormulaDisk.sql. This script must be executed by a user with dba privileges since it creates the Operations Center disk devices. The Operations Center script used to create the sample Service Warehouse is CreateFormula.sql. The script must be executed by a user with dba privileges since it creates both the default database and user.

To create the database devices and instance on a Sybase database:

  1. Edit the /OperationsCenter_install_path/database/samples/sybase/FormulaDisk.sql script to customize the database devices parameters:

    Before running FormulaDisk.sql, you must change the location and size of the device data files to point to a desired directory. Note also, the specified directory must exist prior to executing the FormulaDisk.sql script.

    • Edit the following variable to change the name of the database.

      SELECT @dbName = "formula"

    • Change the location and size of the device data files for both the formulaDisk and formulaLog devices if necessary:

      SELECT @dskDev = "/opt/sybase/dbs/formulaLog.dat"

      SELECT @dskSize = 1024000 /* approx 200M */

      SELECT @logDev = "/opt/sybase/dbs/formulaDsk.dat"

      SELECT @logSize = 51200 /* approx 100M */

    • Configure the initial and maximum database size variables.

      These values depend on available disk space and any operating system imposed file size limits.

    • Update to create new buffer pools as necessary. Examples are provided in the script.

  2. Edit the /OperationsCenter_install_path/database/samples/sybase/CreateFormula.sql script to customize the database instance parameters:

    • Change the database name, user login, and password settings if the defaults are not desirable. The dbName must correspond to value of @dbName set in FormulaDisk.sql.

      SELECT @dbName = "formula".

      SELECT @userName = "formula"

      SELECT @password = "sesame"

  3. Run the FormulaDisk.sql script by issuing the following command:

    isql -Usa -Psa-password -Sserver-name -i FormulaDisk.sql

    Where, sa-password is the Sybase system administrator password and server-name is the Sybase server name.

  4. Run the CreateFormula.sql script by issuing the following command:

    isql -Usa -Psa-password -Sserver-name -i CreateFormula.sql

    Where, sa-password is the Sybase system administrator password and server-name is the Sybase server name.

  5. After the Sybase database instance is created, create a new Database Defintion for the database instance.

    The default value for the Listener Port property is 4100. If you setup Sybase to listen on a different port, specify the new port in this property. See the Sybase documentation for instructions on how to change the default listener port.

    For more information on creating database definitions, see Section 7.3.2, Creating and Editing a Database Definition.

  6. If you did not enable the database definition as a part of Step 3, right-click the database definition and select Enable Database Definition.

  7. If the database definition is for a single schema for Event Data Store, it is necessary to manually initialize the schema. Right-click the database definition, then select Initialize Database Schema.

    Normally, this step is not necessary since the Operations Center server creates all appropriate schemas when required.

Increasing Maximum Locks to Resolve Deadlock Issues with Sybase

To attempt to deal with deadlock issues in a Sybase environment, the database tables created by the data warehouse use the "row-level" locking feature. This reduces the number of lock collisions in a fairly busy system. In order to accommodate the table setting, the number of locks that can be held open by the system can be re-configured by the CreateFormula.sql script. Note that changing this parameter requires the Sybase server to be stopped and restarted.

7.3.2 Creating and Editing a Database Definition

Databases definitions are created and edited in the Operations Center console. Each database connection appears as an element in the hierarchy under Enterprise > Administration > Database Definitions.

To create a database definition:

  1. In the Operations Center console Explorer pane, expand the Administration root element.

  2. Right-click the Database Definitions element, then select Create Database Definition to open the Create Database Definition dialog box.

    Create a Database Definition Dialog
  3. Enter a name for the definition in the Name field.

    It is best practice to use a name without spaces or special characters.

  4. In the Schema(s) section, select one or more database schemas to indicate the type of data store.

    Use the Ctrl or Shift keys to select multiple schemas.

    If you select multiple schemas, do not select a schema that is already enabled in another database definition.

  5. Click the Type list, then select a database type.

    If selecting (SQL Server Domain Authentication), there are required configurations for Windows servers. For more information, see Section 7.1, Configuring Windows Servers for Single Sign On (SSO).

    Select Other if you are using a database type that is not supported by Operations Center.

  6. Deselect the Enable check box if you do not wish to activate the database definition. If the database is not enabled on create, then it needs to be manually enabled later. See Section 7.3.3, Enabling a Definition.

  7. Specify the required properties to connect to the database (properties vary depending on the database type selected):

    Database Properties

    Description

    Hostname

    The name of the database server.

    Listener Port

    The port on which the database listens for communications.

    Server ID (SID)

    The name of the database (used when defining an Oracle database connection).

    Database

    The name of the database (used when defining a Microsoft SQL Server, Sybase, or DB2 database connection).

    Domain

    The domain to use for domain authentication with single sign on (used when defining a Microsoft SQL Server database with Domain Authentication).

    User Name

    The name of the user account. When multiple Operations Center schemas exist within the same database, a unique ID must be created for each database schema.

    If using Microsoft SQL Server with Domain Authentication, specify the username of the Windows user account, or leave blank (on Windows servers) to attempt to use the credentials of the currently active Windows user account.

    Password

    The password for the user account.

    If using Microsoft SQL Server with Domain Authentication, specify the password of the Windows user account, or leave blank (on Windows servers) to attempt to use the credentials of the currently active Windows user account.

    Initial Connections

    The number of connections established upon initial connection.

    Maximum Connections

    The maximum number of connections allowed.

    Additional Properties

    (Optional) Additional JDBC URL properties for Microsoft SQL Server and Microsoft SQL Server (Domain Authentication) databases. Prefix each parameter entry with a semi-colon, such as:

    ;parameter1=value1;parameter2=value2

    For example,

    ;progName=NOC;domain=mosol

    Database Class, URL, and Driver File

    Name and location of the IBM DB2 driver. If using an IBM DB2 database, it is necessary to upload the JDBC driver file supplied by IBM. Contact Support to have the driver signed by Operations Center. Click the Install Driver button to browse, then select a driver file.

  8. If you are configuring an non-supported database and selected Other in Step 5, click Install Driver to install a custom driver.

    Because of the tightened security model introduced by Oracle Java 7 Update 45, custom drivers must be signed by Operations Center. Contact Support to have the driver signed.

  9. Click the Test button to test and verify that the database settings are valid, prior to creating the definition.

    If the database connection is valid, the Create button activates.

  10. Click the Create button to create and save the database definition.

    The new definition displays under the Database Definitions element in the hierarchy.

  11. If the database definition is only using the Event Data Store schema, then it must be initialized. Continue to Section 7.3.4, Initializing a Definition. If created in conjunction with the Service Warehouse schema, this step is not necessary.

To edit an existing definition, right-click the definition, then select Properties. On the Properties dialog box, select Database. The options are the same as the Create a Database dialog box.

7.3.3 Enabling a Definition

After creating a database definition, it must be enabled. Using default settings, Database Definitions are enabled on creation.

To manually enable a database definition:

  1. In the Operations Center console Explorer pane, expand the Administration root element.

  2. Right-click the database element under Database Definitions, then select Enable Database Definition.

7.3.4 Initializing a Definition

Initializing is not usually necessary after enabling a database definition since the Operations Center server creates all appropriate schemas when required. However, if creating a database definition with a single schema for Event Data Store, the schema must be manually initialized after creating the definition.

Any of the databases defined using a database definition can be reinitialized. However, reinitializing a database drops the entire database schema and all the data before recreating the schema.

WARNING:Use extreme caution when reinitializing a database because it drops the entire database schema, including all data, to re‑create the schema.

To initialize or reinitialize a database:

  1. In the Explorer pane, expand the Database Definitions element.

  2. If the database definition is not enabled, right-click the database definition and select Enable Database Definition.

  3. Right-click the database definition, then select Initialize Database Schema.

7.3.5 Disabling and Deleting a Definition

A database definition can be disabled or deleted. Disabling a definition retains the definition but the connection is no longer active.

To manually disable or delete a database definition:

  1. In the Operations Center console Explorer pane, expand the Administration root element.

  2. Right-click the database element under Database Definitions, then select one of the following: select Enable Database Definition.

    • Disable Database Definition to disable the database definition.

    • Delete Database Definition to delete the database definition.