You can use the Report SQL Generator to generate drop table
SQL commands and run them against the appropriate product system table
database. Complete the task before you upgrade the reporting schemas.
About this task
This procedure only applies to Campaign, eMessage, and Interact. The following table shows if the object types are
supported in
Campaign,
eMessage, and
Interact for
Oracle, DB2, and SQL server.
Table 1. Supported object typesSupported object types
|
Campaign |
eMessage |
Interact |
Oracle |
Views Materialized Views Table |
Materialized Views |
Views Materialized Views Table |
DB2 |
Views Materialized Views Table |
Materialized Views |
Views Materialized Views Table |
SQL Server |
Views Table |
Views |
Views Table |
Procedure
Complete the following tasks to generate and run the
SQL:
- Log in to HCL®
EMM as the
platform_admin user (or another user with access to the Report SQL
Generator menu item).
- Only if you did not use the default JNDI names for the
JDBC data sources you created in an earlier step, do the following.
- Select Settings | Configuration | Reports
| Schemas | ProductName.
- Change the default values of the JNDI property to match
the JNDI names you gave the JDBC connections in an earlier step.
- Select Settings | Reports SQL Generator.
- In the Product field, select the
appropriate HCL application.
- In the Schema field, select one
or more reporting schemas.
- Select the Database Type.
- In the Generate Type field, select
the appropriate option (views, materialized views, or tables).
Materialized views are not an option when Database
Type is set to Microsoft™ SQL Server.
If the JNDI data source names are incorrect or
not configured, the SQL Generator cannot validate the SQL scripts
that create tables.
- Ensure that Generate Drop Statement is set to Yes.
- Optional: To examine the SQL that is generated,
click Generate. The SQL Generator creates the
script and displays it in the browser window.
- Click Download.
The
SQL Generator creates the script and prompts you to specify where
you want to save the file. If you selected a single reporting schema
from the Schema field, the script name matches
the name of schema (eMessage_Mailing_Performance.sql, for example). If you selected more than one reporting schema, the
script name uses the product name only (Campaign.sql, for example). For a complete list of names, see SQL scripts by data source.
- Specify the location where you want to save the script.
If you change the name of the file, be sure to use something that
clearly indicates which schemas you selected. Then, click Save.
- Repeat steps 5 through 12 for each drop table script you
need to generate.
Note: The Interact reporting
schemas reference more than one data source. Generate a separate SQL
script for each data source.
There may be times when you
want to disable script validation. For example, perhaps the Marketing Platform cannot
connect to the HCL application
database but you want to generate the scripts anyway. To disable validation,
clear the data source names from the data source fields (see step
3, above). When you generate the scripts, the SQL Generator displays
a warning that it cannot connect to the data source, but it still
generates the SQL script.
- Run the drop table SQL in your product system table database.
Repeat for each product for which you are upgrading reports.