Creating views or materialized views for Campaign or eMessage

You can use SQL scripts to create views or materialized views for Campaign or eMessage. Reports uses views or materialized views to extract reportable data.

For Oracle and DB2®, eMessage requires mateiralized views. For SQL Server, eMessage requires views.

1.	Complete the following steps to create views or materialized views for Campaign or eMessage:

2.	Locate the SQL scripts that you generated and saved previously.

3.	Use your database administration tools to run the appropriate script against the appropriate application database(s) for the report package that you are configuring.

When you run a script that creates materialized views on a DB2® database, your database might return 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.

4.	For Campaign with a DB2® database, increase the DB2® heap size to 10240 or higher. 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 IBM® Cognos® does not display SQL error messages if a user selects all the campaigns when running a report such as the Financial Summary report.

5.	Complete the following actions for eMessage:

a.	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.

b.	Edit the appropriate version of the script to remove the drop table statements and save the script.

c.	Run the appropriate version of the script against your Campaign system tables database.

Setting up data synchronization

Make sure that you use your database administration tools to schedule regular data synchronization between the production databases of the IBM® EMM application and the materialized views.

For eMessage, the materialized views for Oracle and DB2® are refreshed by stored procedures. Stored procedures also update the uare_delta_refresh_log table. If the refresh process for DB2® fails, the log table displays the error. See Running and scheduling stored procedures for eMessage for information about stored procedures.