MONITOR database considerations for Oracle

Globalization considerations

Oracle must be installed using the UTF-8 Universal character set (AL32UTF8) instead of the default database character set (WE8ISO8859P1 - ISO 8859-1 West European). Using this character set ensures that monitor model metadata and instance data containing native language characters can be saved to the database. Additionally, IBM® Cognos® BI requires a UTF-8 database.

The DIM_TIME table contains a column for populating dashboard reports containing time dimensions with a translated month name. By default, the locale code for the NLS_LANGUAGE setting is used for populating the DIM_TIME table entries. To change the default language, change the NLS_LANGUAGE for the Oracle instance or for the current session before running the createTables.sql script. There is also an SQL statement in createTables.sql that you can use to override the month name entries and define your own month names.

MONITOR security considerations

When you are using the Profile Management Tool (in the WebSphere® Customization Toolbox) or the manageprofiles command to create the Oracle database objects, the database administrative user specified in the profile creation creates the database objects and a MONITOR schema. On Oracle, a schema is both a collection of database objects and a user ID that can log in to the database.

By default, the MONITOR schema owner is also the runtime database user, and is granted privileges to create other schemas and database objects as part of the database creation. This enables the IBM Business Monitor server to automatically manage the monitor model database schema when models are deployed and removed. To secure the database, you can create the database manually. The MONITOR schema owner, or a different user, can be used as the IBM Business Monitor runtime database user. In a secured environment, you can grant the runtime database user only the privileges required for runtime operations. See the topics "Installing the database manually" and "Securing the MONITOR database environment" in the related links.

JDBC driver

JDBC support is provided by the Oracle JDBC drivers for JVM 1.6. The ojdbc6.jar JDBC driver file is the Oracle-supported JDBC driver for use with WebSphere Application Server. The ojdbc6.jar file can be used for both Oracle 10g and Oracle 11g. For information about minimum required settings for Oracle, see the related link.

By default, the Profile Management Tool points to the ojdbc6.jar file supplied in app_server_root\jdbcdrivers\Oracle. Alternatively, you can download another Oracle ojdbc6.jar JDBC driver file and point to it when you run the Profile Management Tool or the manageprofiles command.

XA recovery

grant select on pending_trans$ to <user>;
grant select on dba_2pc_pending to <user>;
grant select on dba_pending_transactions to <user>;
grant execute on dbms_system to <user>; 

Materialized Views

Dashboard page loading performance can degrade as you build up large volumes of monitoring data in your database. After you have more than approximately 10 million monitoring context instances, you might want to enable your database vendor's option to precompute the data.

You can use materialized views in Oracle to improve query performance. A materialized view can precompute the values of key performance indicators (KPIs) and of IBM Cognos BI cube measures on Oracle. When the dashboard requests the value, the precomputed value is returned almost immediately, instead of the aggregation, such as an average, being computed against every monitoring context instance.

You can find an example script that defines a FAST, ON COMMIT materialized view for the Showcase (Better Lender) sample in the following directory: app_server_root\installableApps.wbm\showcase\OracleMaterializedViews_Sample.