Setting up an external database for production

You can configure your own external database to use with IBM® Spectrum Conductor. The Derby demo database is not supported for any production clusters. To produce regular reports for a production cluster, you must use a supported commercial database.

Before you begin

  • IBM Spectrum Conductor must be installed on the primary host before you can set up the external database.
  • A compatible third-party database must be properly configured and running, and have space to store all your data.
  • The database server account must have access to create triggers, sequences, tables, and stored procedures. To configure the database connection, you need information to access the database host.
  • You must have a user name, password, and URL to access the database server.
  • Your database server account must have access to create triggers, sequences, tables, and stored procedures.
  • Your database must have enough space allocated for the reporting feature.
  • For data security, each cluster should have a different database schema to store its historical data when sharing the same database.
  • Download the latest JDBC driver for the commercial database (see Supported databases).
Important: Set up the database server and the management host running the reporting framework services to be in the same time zone. If the database and reporting framework servers are not set to the same time zone, the system uses the time stamp on the reporting framework server to generate reports, which will then contain data for a different time range.

Procedure

  1. To create the EGO and GUI database schema, change to the applicable schema directory on the primary host and run the script. Refer to the following table for the location of schema directories.
    Refer to the following table for the location of schema directories.
    Table 1. Schema directories and creation scripts
    Data Schema Location Database schema creation script
    EGO $EGO_TOP/perf/ego/version_number/DBschema/database_type
    • egodata.sql: creates EGO-related schema tables, such as ALLOCATION_EVENT and RESOURCE_ATTRIBUTES.
    • egodroptables.sql: drops all EGO-related schema tables.
    GUI $EGO_TOP/gui/version_number/DBschema/database_type
    • create_schema.sql and init.sql used by the Rack View page for the cluster management console to create GUI-related schema tables, such as PMC_GROUP_ROLE and PMC_GROUP_ROLE.
    • drop_schema.sql: drops all GUI-related schema tables.
    • For Oracle databases, use sqlplus to run the scripts:
      sqlplus user/password@connect_string @egodata.sql data_tablespace index_tablespace
    • For DB2 databases:
      1. Set system temporary table space and user temporary table space on your DB2 server.
      2. Connect to the database: 
        db2 connect to database user user using password
        where:
        • user is the name of the database user
        • password is the password for the system user on the database
        • database is the name of the reporting database
      3. Set the database page size buffer pool to be at least 32768 (32K); otherwise, DB2 rejects it.
      4. Run the scripts:
        Note: The following scripts require that the database is configured with row-organized tables. If the database tables are column-organized, you must update the database configuration with the command update db cfg for database using dft_table_org row before running the scripts. 
        clpplus -nw user/password@localhost:50000/database @egodata.sql schematable_space
        where:
        • user is the user name on the database
        • password is the password for this user name on the database
        • database is the name of the reporting database
        • schema is the schema name of your choice
        • table_space is the table space name of your choice
      5. To drop the tables, run the drop table scripts:
        clpplus -nw user/password@localhost:50000/database @egodroptable.sql schema
  2. Stop the reporting services: derbydb (if you are using the Derby demo database), plc, and purger: 
    egosh service stop purger plc derbydb
  3. If you are using the Derby demo database, disable automatic startup of the derbydb service. See Disabling automatic startup of the reporting services.
  4. Copy the JDBC driver, which you downloaded, into the reporting framework and GUI library directories on all management hosts that are expected to run reporting framework and cluster management console services.
    You must copy the JDBC driver to the following directories:
    • $EGO_TOP/perf/version_number/lib
    • $EGO_TOP/gui/version_number/lib
  5. Configure your database connection.
    1. Launch the database configuration tool.

      Run $EGO_TOP/perf/version_number/bin/dbconfig.sh. If you do not use X-Windows, you can also run dbconfig.sh -console.

    2. In the User ID and Password fields, specify the user account name and password with which to connect to the database and to create your database table spaces.
      Note: Make sure that you specify the same database user as you did when you created the database schema. This user must have read and write access to the database tables.
    3. In the JDBC driver field, select the driver for your commercial database from the available list:
      • com.novell.sql.LDAPDriver
      • com.informix.jdbc.IfxDriver
      • org.axiondb.jdbc.AxionDriver
      • org.apache.derby.jdbc.ClientDriver*
      • com.mckoi.JDBCDriver
      • uk.co.thinksql.ThinkSQLDriver
      • weblogic.jdbc.mssqlserver4.Driver
      • sun.jdbc.odbc.JdbcOdbcDriver
      • com.lutris.instantdb.jdbc.idbDriver
      • com.sybase.jdbc2.jdbc.SybDriver
      • com.ibm.db2.jcc.DB2Driver
      • com.imaginary.sql.msql.MsqlDriver
      • interbase.interclient.Driver
      • oracle.jdbc.driver.OracleDriver
      • org.gjt.mm.mysql.Driver
      • org.postgresql.Driver
      • org.hsqldb.jdbcDriver
      If the JDBC driver is not in the list, enter 16 and locate another driver.
    4. In the JDBC URL field, enter the URL for your database. For example:
      For DB2 databases
      jdbc:db2://localhost:50002/database:currentSchema=schema;currentFunctionPath=schema;

      For a list of sample URL formats, review the Example URL section in the $EGO_TOP/perf/conf/driver.properties file.

      Note: For DB2 databases, you must add the currentSchema parameter in the JDBC URL. Check the currentSchema value in your database before adding this parameter.
    5. In the Maximum connections field, specify the maximum allowed number of concurrent connections to the database server.

      This is the maximum number of users who can produce reports at the same time.

  6. Restart the reporting services: 
    egosh service start plc purger
  7. Restart the cluster management console (WEBGUI service). The cluster management console is unavailable during this step. 
    egosh service stop WEBGUI
egosh service start WEBGUI

Results

The report data is now loaded into the production database and the cluster management console uses the data in this database. If you used non-standard SQL code, some of your custom reports might not be compatible with the production database.