Setting up HCL Campaign to use database load utilities

You can improve performance by using a database load utility for all datasources.

About this task

Note: These instructions assume that you are using a DB2® 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.

HCL® 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 HCL 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 HCL 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 HCL Campaign.

Create two load control file templates.

Most database load utilities require the use of a control file. HCL 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.
  
  HCL 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, HCL 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 HCL 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>.
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: /IBM/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: /IBM/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 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 IBM® Contact Optimization.
  Note: IBM Contact Optimization does not update any user database datasources, so the following information does NOT apply to user database datasources.
  IBM Contact Optimization uses the UA_SYSTEM_TABLES datasource loader settings to update Contact Optimization tables during session runs. Because these settings are common to IBM Campaign and IBM Contact Optimization, you must configure the loader as follows:
  - IBM Contact Optimization Loader settings: Avoid using relative paths for loader scripts in the UA_SYSTEM_TABLES datasource loader configuration. Instead, use absolute paths.
  - If Campaign and Contact Optimization are installed on different machines, create identical folder structures as per the absolute path on the Campaign machine and the Contact Optimization. Make sure that the absolute path is accessible to both the Campaign listener and the Contact Optimization listener from their respective machines.
  - If Campaign and Contact Optimization are installed on the same machine, you do not have to create the folder structure because it already exists.
  Example:
  In this example, Campaign and Contact Optimization are installed on separate machines, and Campaign has the following loader configuration:
  LoaderCommand: /IBM/Campaign/partitions/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  LoaderCommandForAppend: //IBM/Campaign/partitions/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
  In this example, you would create the directory /IBM/Campaign/partitions/partition1/ on the Contact Optimization machine and copy all of the necessary loader-specific script files to that directory on the Contact Optimization machine. For details, read about configuring the database load utility in the Contact Optimization User's Guide.

Results

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