Preparing Amazon Redshift user data sources for Campaign

Follow these guidelines to use Amazon Redshift as a data source for Campaign.

About this task

Campaign supports Amazon Redshift for customer tables. Redshift is not supported for system tables.

Use the PostgreSQL ODBC driver for connectivity and configure the PostgreSQL template in Campaign for each datasource. See the IBM® Campaign Administrator's Guide for important information about configuring Campaign|partitions|partition[n]|dataSources|PostgreSQL. Some properties do not have default values, so you must supply them. Other properties might need to be adjusted for your specific configuration.

For information about supported versions, see the Recommended Software Environments and Minimum System Requirements. For information about Amazon Redshift, see http://aws.amazon.com/redshift/.

Procedure

  1. Download and configure the PostgreSQL ODBC driver for Redshift.
  2. Validate connection and authentication details: Use isql to test the connection on Linux™. Use the Microsoft™ Windows™ ODBC Driver Manager for Windows.
  3. Import PostgreSQLTemplate.xml using configTool (a utility supplied with HCL Marketing Platform).
  4. Fill in the newly added PostgreSQL template with the user database details.
  5. Configure the Amazon Redshift user database.
  6. Restart the application server and the HCL Campaign listener server.

What to do next

Follow the steps below to test the connection with Amazon Redshift.
Note: See the following link to get the cluster connection string: http://docs.aws.amazon.com/redshift/latest/mgmt/configuring-connections.html#connecting-drivers

On Windows:

  1. Open Windows ODBC Driver Manager.
  2. Add a System DSN and select the PostgreSQl Unicode(x64) driver.
    Note: For psqlodbc version 9.0.x onwards, use the 64-bit driver. For psqlodbc version 8.0.x onwards, you can use either the 32-bit or the 64-bit drivers.
  3. Provide the connection string and other details.
  4. Test the connection using isql or the HCL Campaign CxnTest utility.

On Linux:

  1. Configure LD_LIBRARY_PATH (or LIBPATH on AIX®) to include the location of the driver library files.
  2. Configure ODBCINI to point to the odbc.ini file location.
  3. Configure the odbc.ini file for the Redshift user database. For example:

    [PostgreSQL]
    Driver = [change-me, e.g. /usr/lib64/psqlodbc.so]
    DATABASE=[change-me]
    Servername=[change-me]
    PORT=[change-me]
    Username=[change-me]
    Password=[change-me]

  4. Configure the odbcinst.ini file for the driver.
  5. Test the connection using isql or the HCL Campaign CxnTest utility.

The following modes of SSL were tested with the PostgreSQL ODBC driver:

Windows64bit:

Disable
Allow
Require
Verify-ca