Generating SQL to drop views, materialized views and run SQL in product databases

You can use the Reports SQL Generator to generate drop table SQL commands and run them against the appropriate product system table database. Complete this task before you upgrade the reporting schemas.

About this task

Note: This procedure applies to Unica Campaign, Unica Deliver, and Unica Interact.
The following table shows which object types are supported in Unica Campaign, Unica Deliver, and Unica Interact for Oracle, DB2, and SQL server.
Table 1. Supported object types

Supported object types

Unica Campaign Unica Deliver Unica Interact
Oracle

Views

Materialized Views

Materialized Views

Views

Materialized Views

DB2

Views

Materialized Views

Materialized Views

Views

Materialized Views

SQL Server

Views

Views

Views

Procedure

Complete the following tasks to generate and run the SQL commands:
  1. Log in to Unica as the platform_admin user (or another user with access to the Report SQL Generator menu item).
  2. Optional: Only if you did not use the default JNDI names for the JDBC data sources you created in an earlier step, do the following.
    1. Select Settings | Configuration | Reports | Schemas | ProductName.
    2. Change the default values of the JNDI property to match the JNDI names you gave the JDBC connections in an earlier step.
  3. Select Settings | Reports SQL Generator.
  4. In the Product field, select the appropriate HCL application.
  5. In the Schema field, select one or more reporting schemas.
  6. Select the Database Type.
  7. In the Generate Type field, select the appropriate option (views, materialized views).
    Notes:
    • 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.
  8. Ensure that Generate Drop Statement is set to Yes.
  9. Optional: To examine the SQL that is generated, click Generate. The SQL Generator creates the script and displays it in the browser window.
  10. 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 (Deliver_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.

  11. 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.
  12. Repeat steps 5 through 11 for each drop table script you need to generate.
    Note: The Unica Interact reporting schemas reference more than one data source. Generate a separate SQL script for each data source.

    There might be times when you want to disable script validation. For example, perhaps Unica 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.

  13. Run the drop table SQL in your product system table database. Repeat for each product for which you are upgrading reports.