Creating the Message Connector Tables

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

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 Interact runtime server. See the worksheets you completed in the IBM® Interact Installation Guide for details about connecting to the database that contains the Interact runtime tables.

Launch your database client and connect to the database in which your Interact runtime tables are currently stored.

Run the appropriate script in the <Interact_home>/msgconnector/scripts/ddl directory. The following table lists the sample SQL scripts you can use to manually create the Message Connector tables:

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

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:

Information 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.

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