For eMessage only: Creating stored procedures, staging tables, and indexes

After you install or upgrade reporting templates, you must run specific SQL scripts before you generate eMessage reports. The SQL scripts create stored procedures and staging tables.

About this task

The Campaign_reportspack_home\cognos11\emessage-ddl directory is on the server that hosts the IBM® Cognos® Content Manager. This directory contains the following database scripts for Oracle, IBM DB2®, and Microsoft™ SQL Server:
  • acer_indexes_dbname.sql
  • acer_tables_dbname.sql
  • acer_scripts_dbname.sql

Run the following scripts against the Campaign database in the order listed.

Procedure

  1. acer_indexes_dbname.sql

    Make sure that you allow sufficient time for the script to complete. The time depends on the volume of data that is stored in the eMessage system tables.

  2. acer_tables_dbname.sql

    This script creates the delta processing staging tables in the eMessage system schema.

  3. acer_scripts_dbname.sql
    Important: For DB2 databases, change the termination character from ; (semicolon) to ! (exclamation point).

    This script creates the stored procedures that you must configure after you install reports for eMessage.

What to do next

Note: You must configure the stored procedures to run on a regular basis to populate the staging tables. You cannot see data in the reports until you run the stored procedures for eMessage reports.

For more information about running and scheduling the stored procedures, see For eMessage only: How to schedule and run stored procedures.