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

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
    Populate the following scripts as per your locale.
    • uare_lookup_populate.sql (For English)
    • uare_lookup_populate_de.sql
    • uare_lookup_populate_es.sql
    • uare_lookup_populate_fr.sql
    • uare_lookup_populate_it.sql
    • uare_lookup_populate_ja.sql
    • uare_lookup_populate_ko.sql
    • uare_lookup_populate_pt.sql
    • uare_lookup_populate_ru.sql
    • uare_lookup_populate_zh.sql

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.