For Unica Deliver only: Creating stored procedures, staging tables, and indexes

After you install or upgrade reporting templates, you must run specific SQL scripts before you generate Unica Deliver reports. The SQL scripts create stored procedures and staging tables.

About this task

The Campaign/reports/Deliver-ddl directory is on the server. This directory contains the following database scripts for Oracle, IBM® DB2®, and Microsoft™ SQL Server:
  • acer_indexes_dbname.sql
  • acer_tables_dbname.sql
  • acer_scripts_dbname.sql
Note: If you observe any issues when executing the mentioned sql files, use the script terminator based on the database client. If your database client shows errors for acer_scripts_dbname.sql, create the procedures one after the other.

Run the following scripts against the Unica Campaign database in the order listed.

Procedure

  1. acer_indexes_dbname.sql

    Make sure that you allow sufficient time for the script to complete. The time depends on the volume of data that is stored in the Unica Deliver system tables.

  2. acer_tables_dbname.sql

    This script creates the delta processing staging tables in the Unica Deliver system schema.

  3. acer_scripts_dbname.sql
    Important: For DB2® databases, change the termination character from ; (semicolon) to ! (exclamation point).

    This script creates the stored procedures that you must configure after you install reports for Unica Deliver.

  4. Navigate to Campaign\reports\tools directory, under your Campaign installation and locate the following scripts.
    • uare_lookup_create_DB_type.sql
    • uare_lookup_populate*.sql: Run the script against your Unica Campaign system tables database for all languages.
      Note: Unica Insights supports four characters for locale. For example, in the Unica_home\Campaign\reports\tools directory, if there are two lookup populate SQL files for the french locale:
      • uare_lookup_populate_fr.sql
      • uare_lookup_populate_fr_FR.sql

      you must execute uare_lookup_populate_fr_FR.sql and ignore the other file.

What to do next

Note: You must configure the stored procedures to run on a regular basis to populate the staging tables. You cannot see data in the reports until you run the stored procedures for Unica Deliver reports.

For more information about running and scheduling the stored procedures, see For Unica Deliver only: How to schedule and run stored procedures.