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.
About this task
Note: For Oracle and DB2®, eMessage requires mateiralized
views. For SQL Server, eMessage requires
views.
Procedure
Complete the following steps to create views or materialized
views for Campaign or eMessage:
- Locate the SQL scripts that you generated and saved previously.
- Use your 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, 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.
- 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.
- Complete the following actions for eMessage:
- 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.
What to do next
Setting up data synchronization
Make sure
that you use your database administration tools to schedule regular
data synchronization between the production databases of the HCL
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.