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
- Locate the SQL scripts that you generated and saved previously.
-
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.
-
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.
-
For eMessage, complete the following
steps:
-
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.
-
Edit the appropriate version of the script to remove the drop table statements and save the
script.
-
Run the appropriate version of the script against your Campaign system tables database.