Setting up Unica Campaign to use database load utilities

About this task

Note: These instructions assume that you are using a DB2® database and MariaDB database on a supported operating system other than z/OS®. If you are using a different database, adjust the instructions accordingly. If you are using DB2® on z/OS®, see Using database load utilities with DB2 on z/OS.

Unica Campaign supports the use of database load utilities, which are available from your database vendors. You must obtain licensed copies of any database load utilities.

Database load utilities can improve performance when pushing ID lists into temporary tables and when exporting data from Unica Campaign to the database. For example, data is exported during a Snapshot, Mail List, or Call List process.

Load utilities can provide significant performance improvements. Testing on DB2® indicates that without the load utility, insertion of a million rows requires about 5 times more CPU utilization and significant disk I/O. Results vary depending on the hardware in use.

Important: The following adjustments can impact system resources and potentially impact performance numbers.

Procedure

To set up Unica Campaign to use a database load utility, there are three main steps to perform for each datasource: Create two load control file templates; create a script or executable to start the load utility; then set the loader configuration properties in Unica Campaign.

Create two load control file templates.

Most database load utilities require the use of a control file. Unica Campaign can generate control files dynamically based on control file templates that you create.
1. Create a load control file template for adding records. The template must consist of the following lines. This example template is named loadscript.db2:
```
connect to <DATABASE> user <USER> using <PASSWORD>;
load client from <DATAFILE> of del modified by coldel| insert into <TABLE>(
<FIELDNAME><,>
)
nonrecoverable;
```
2. Create a load control file template for appending records. The template must consist of the following lines. This example template is named loadappend.db2:
```
connect to <DATABASE> user <USER> using <PASSWORD>;
load client from <DATAFILE> of del modified by coldel| insert into <TABLE>(
<FIELDNAME><,>
)
nonrecoverable;
```
  You now have templates for loading data into a new or empty database table and appending data to an existing database table.
  
  Unica Campaign will fill in the DATABASE, USER, PASSWORD, DATAFILE, TABLE and FIELDNAME tokens in the templates and create a configuration file called CONTROLFILE for the DB2® Load.
Create a script or executable to start the load utility.

To invoke the load utility, Unica Campaign uses a shell script (or an executable, for )Windows, which is identified in the Loadercommand configuration property. You can specify either a direct call to the database load utility executable or a call to a script that launches the database load utility.
1. For this example, create a shell script called db2load.sh, to start the loader. For the /tmp path, you can substitute the directory of your choice:
```
#!/bin/sh
cp $1 /tmp/controlfile.tmp
cp $2 /tmp/db2load.dat
db2 -tvf $1 >> /tmp/db2load.log
```
2. Change permissions on the script file so it has execute permission:
```
chmod 755 db2load.sh
```
Set loader configuration properties in Unica Campaign.

The loader configuration properties identify the control file templates and indicate the location of the script or executable file. Be sure to adjust the configuration settings for each data source.
1. Choose Settings > Configuration, then select Campaign|partitions|partition1|dataSources|<datasourcename>.
  
  Note: When using UA_SYSTEM_TABLES (such as contact history) the bcp loader is invoked on any number of records.
  But when using other data sources, it is invoked based on the value of the parameter "MinReqForLoaderCommand".
2. Set the properties that begin with the word Loader. For important information, see Campaign | partitions | partition[n] | dataSources.
  - LoaderCommand: The path to the script or executable to invoke the database load utility. The script must be in CAMPAIGN_HOME/partition/partition[n]. Most database load utilities require several arguments to be launched successfully. The tokens required by DB2® are shown in angle brackets in the following example. Type the tokens exactly as shown. They are replaced by the specified elements when the command is run. Example: /HCL/Campaign/partition/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  - LoaderCommandForAppend: The path to the script or executable to invoke the database load utility for appending records to a database table. The script must be in CAMPAIGN_HOME/partition/partition[n]. Example: /HCL/Campaign/partition/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  - LoaderDelimiter and LoaderDelimiterForAppend: The delimiter used in the loader control file template.
  - LoaderControlFileTemplate: The control file template that is configured for Unica Campaign. Example: loadscript.db2
  - LoaderControlFileTemplateForAppend: The control file template for appending records. Example: loadappend.db2
  - All other Loader settings: Specify as required for your implementation, according to the information provided in the topic Campaign | partitions | partition[n] | dataSources.
3. Perform this step if you are also using Unica Optimize.
  Note: Unica Optimize does not update any user database datasources, so the following information does NOT apply to user database datasources.
  Unica Optimize uses the UA_SYSTEM_TABLES datasource loader settings to update Unica Optimize tables during session runs. Because these settings are common to Unica Campaign and Unica Optimize, you must configure the loader as follows:
  - Unica Optimize Loader settings: Avoid using relative paths for loader scripts in the UA_SYSTEM_TABLES datasource loader configuration. Instead, use absolute paths.
  - If Unica Campaign and Unica Optimize are installed on different machines, create identical folder structures as per the absolute path on the Unica Campaign machine and the Unica Optimize. Make sure that the absolute path is accessible to both the Unica Campaign listener and the Unica Optimize listener from their respective machines.
  - If Unica Campaign and Unica Optimize are installed on the same machine, you do not have to create the folder structure because it already exists.
  Example:
  In this example, Unica Campaign and Unica Optimize are installed on separate machines, and Unica Campaign has the following loader configuration:
  LoaderCommand: /HCL/Campaign/partitions/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  LoaderCommandForAppend: //HCL/Campaign/partitions/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  In this example, you would create the directory /HCL/Campaign/partitions/partition1/ on the Unica Optimize machine and copy all necessary loader-specific script files to that directory on the Unica Optimize machine. For details, read about configuring the database load utility in the Unica Optimize User's Guide.

Results

Unica Campaign performs the following actions when it writes to the database: First, it creates a temporary data file as fixed width or delimited text. If specified by the LoaderControlFileTemplate property, a temporary control file is created dynamically based on the template file and the list of fields to be sent to the database. Next, it issues the command specified by the LoaderCommand configuration property. Finally, it cleans up the temporary data file and control file.