For Campaign and eMessage only: Creating views or materialized views

You can use SQL scripts to create views or materialized views for Campaign and eMessage. The reports application uses views or materialized views to extract reportable data.

About this task

Note: For Oracle and DB2®, eMessage requires materialized views. For SQL Server, eMessage requires views.

To create views or materialized views for Campaign or eMessage, complete the following steps.

Procedure

  1. Locate the SQL scripts that you generated and saved previously.
  2. Use the database administration tools to run the appropriate script against the appropriate application database(s) for the report package that you are configuring.
    Note: When you run a script that creates materialized views on a DB2 database, you might see the following error:

    SQL20059W The materialized query table-name may not be used to optimize the processing of queries.

    However, the materialized view is successfully created.

    Proceed to step 3 for Campaign with a DB2 database.

    Proceed to step 4 for eMessage.

  3. For Campaign with a DB2 database, increase the DB2 heap size to at least 10240. The default heap size is 2048. Use the following command to increase the heap size:
    db2 update db cfg for databasename using stmtheap 10240

    where databasename is the name of the Campaign database.

    Increasing the heap size ensures that HCL® Cognos® does not display SQL error messages if a user selects all the campaigns when running a report such as the Financial Summary report.

  4. For eMessage, complete the following steps:
    1. In the ReportsPackCampaign\tools directory under your reports pack installation, locate the uare_lookup_create_DB_type.sql script, where DB_type is the database type that is appropriate for your installation of Campaign.
    2. Edit the appropriate version of the script to remove the drop table statements and save the script.
    3. Run the appropriate version of the script against your Campaign system tables database.

What to do next

Continue with Setting up data synchronization.