Setting up IBM® Campaign to use database load utilities
You can improve performance by using a database load utility for all datasources.
IBM® 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 IBM® 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.
The following instructions assume that you are using a DB2® database. If you are using a different database, adjust the instructions accordingly.
*
1.
To set up IBM® 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 IBM® Campaign.
2.
Most database load utilities require the use of a control file. IBM® Campaign can generate control files dynamically based on control file templates that you create.
a.
connect to <DATABASE> user <USER> using <PASSWORD>;
load client from <DATAFILE> of del modified by coldel| insert into <TABLE>(
<FIELDNAME><,>
)
nonrecoverable;
b.
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.
IBM® 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.
3.
To invoke the load utility, IBM® 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.
a.
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
b.
chmod 755 db2load.sh
4.
The loader configuration properties identify the control file templates and indicate the location of the script or executable file.
a.
Choose Settings > Configuration, then select Campaign|partitions|partition1|dataSources|<datasourcename>.
b.
Set the properties that begin with the word Loader. For important information, see the topic Configuration properties for IBM® Campaign in the IBM® Campaign Administrator's Guide or the online help.
*
LoaderCommand: The path to the script or executable to invoke the database load utility. 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 them exactly as shown. They will be replaced by the specified elements when the command is run. Example: /Unica/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. Example: /Unica/Campaign/partition/partition1/db2load.sh <CONTROLFILE> <DATAFILE>
*
LoaderDelimiter and LoaderDelimiterForAppend: The delimiter used in the loader control file template.
*
LoaderControlFileTemplate: The path to the control file template, relative to the current partition. Example: loadscript.db2
*
LoaderControlFileTemplateForAppend: The path to the control file template for appending records, relative to the current partition. Example: loadappend.db2
*
All other Loader settings: Specify as required for your implementation, according to the information provided in the topic Configuration properties for IBM® Campaign.
The following image shows an example of Loader configuration settings. Screen capture of loader configuration settings for a partition
c.
Be sure to adjust the Loader configuration settings for each datasource.
IBM® 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.
Troubleshooting database load utilities for IBM Campaign