Creating the Message Connector Tables

Before you can deploy the Unica Interact Message Connector, you must first create the tables in the database where the Unica Interact runtime data is stored. You'll create one table for each audience level you have defined. For each audience level, Unica Interact will use the tables you create to record information about Message Connector transactions.

About this task

Use your database client to run the Message Connector SQL script against the appropriate database or schema to create the necessary tables. The SQL scripts for your supported database are installed automatically when you install the Unica Interact runtime server. See the worksheets you completed in the Unica Interact Installation Guide for details about connecting to the database that contains the Unica Interact runtime tables.

Procedure

  1. Launch your database client and connect to the database in which your Unica Interact runtime tables are currently stored.
  2. Run the appropriate script in the <Unica Interact_home>/msgconnector/scripts/ddl directory. The following table lists the sample SQL scripts you can use to manually create the Message Connector tables:
    Table 1. Scripts for creating the Message Connector tables
    Data source type Script name
    IBM® DB2® db_scheme_db2.sql
    Microsoft™ SQL Server db_scheme_sqlserver.sql
    Oracle db_scheme_oracle.sql
    MariaDB db_scheme_mariadb.sql

    Note that these scripts are provided as samples. You may use a different naming convention or structure for audience ID values, so you may need to modify the script before running it. In general, it is a best practice to have one table dedicated to each audience level.

    The tables are created to contain the following information:

    Table 2. Information created by the sample SQL scripts

    A listing of the columns and descriptions created by the sample SQL scripts.
    Column Name Description
    LogId The primary key of this entry.
    MessageId The unique identifier of each messaging instance.
    LinkId The unique identifier of each link in the electronic media (such as an email message).
    OfferImageUrl The URL to the related image of the returned offer.
    OfferLandingPageUrl The URL to the related landing page of the returned offer.
    TreatmentCode The treatment code of the returned offer.
    OfferExpirationDate The expiration date and time of the returned offer.
    OfferContactDate The date and time that the offer was returned to the client.
    AudienceId The audience ID of the electronic media.

    Note the following about this table:

    • Depending on the audience level, there will be one AudienceId column for each component of the audience key.

    • The combination of MessageId, LinkId, and AudienceId(s) forms a unique key of this table.

    When the script has finished running, you have created the necessary tables for the Message Connector.

Results

You are now ready to deploy the Message Connector web application.