Campaign | partitions | partition[n] | dataSources

These properties specify the databases that HCL Campaign can access and they control many aspects of how queries are formed.

Each data source that you add in HCL Campaign is represented by a category under partition > partition [n] > dataSources > <data-source-name>.

AccessLibrary

Description

HCL Campaign chooses its data source access library according to the data source type. For example, libora4d.so is used for Oracle connectivity, while libdb24d.so is used for DB2® connectivity. In most cases, the default selections are appropriate. However, the AccessLibrary property can be changed if the default value proves to be incorrect in your HCL Campaign environment. For example, 64-bit HCL Campaign provides two ODBC access libraries: one appropriate for ODBC data sources compatible with the unixODBC implementation (libodb4d.so) and the other compatible with the DataDirect implementation (libodb4dDD.so, used by HCL Campaign to access, for example, Teradata).

AliasPrefix

Description

The AliasPrefix property specifies the way HCL Campaign forms the alias name that HCL Campaign creates automatically when using a dimension table and writing to a new table.

Note that each database has a maximum identifier length; check the documentation for the database you are using to be sure that the value you set does not exceed the maximum identifier length for your database.

Default value

A

Additional libraries for AIX®

Description

HCL Campaign includes two additional libraries for AIX ODBC driver managers that support the ODBC ANSI API rather than the ODBC Unicode API:

• libodb4dAO.so (32- and 64-bit): ANSI-only library for unixODBC-compatible implementations
• libodb4dDDAO.so (64-bit only): ANSI-only library for DataDirect-compatible implementations

If you determine that the default access library must be overridden, set this parameter as required (for example, to libodb4dDD.so, overriding the default selection of libodb4d.so).

Default value

No default value defined.

AllowBaseJoinsInSelect

Description

This property determines whether HCL Campaign attempts to do a SQL join of base tables (from the same data source) used in a Select process; otherwise, the equivalent join is done on the Campaign server.

Default value

TRUE

Valid Values

TRUE | FALSE

AllowSegmentUsingSQLCase

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies whether the Segment process consolidates multiple SQL statements into a single SQL statement, when specific configuration conditions are met.

Setting this property to TRUE results in significant performance improvements when all of the following conditions are met:

• Segments are mutually exclusive.
• All segments come from a single table.
• Criteria for each segment are based on the HCL macro language.

In this case, HCL Campaign generates a single SQL CASE statement to perform segmentation, followed by segment-by-field processing on the Campaign application server.

Default value

TRUE

Valid Values

TRUE | FALSE

AllowTempTables

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies whether HCL Campaign creates temporary tables in the database. Creating temporary tables can significantly improve the performance of campaigns.

When the value is TRUE, temporary tables are enabled. Each time a query is issued against the database (for example, by the Segment process), the resulting IDs are written to a temporary table in the database. When an additional query is issued, HCL Campaign can use that temporary table to retrieve rows from the database.

A number of HCL Campaign operations, such as useInDbOptimization, rely on the ability to create temp tables. If temporary tables are not enabled, IBM Campaign retains the selected IDs in the HCL Campaign server memory. The additional query retrieves IDs from the database and matches them to the IDs in server memory. This can negatively impact performance.

You must have appropriate privileges to write in the database to use temporary tables. Privileges are determined by the database login that you provide when you connect to the database.

Default value

TRUE

Note: Typically, you set AllowTempTables to TRUE. To override the value for a specific flowchart, open the flowchart in Edit mode, select Advanced Settings from the Admin menu , then select Disallow Use of Temp Tables for This Flowchart on the Server Optimization tab.

ASMSaveDBAuthentication

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ASMSaveDBAuthentication property specifies whether, when you log in to Campaign and map a table in a data source you die not previously log in to, HCL Campaign saves your user name and password in IBM EMM.

If you set this property to TRUE, Campaign does not prompt you for a user name and password when you log in to the data source. If you set this property to FALSE, Campaign prompts you for a user name and password each time you log in to the data source.

Default value

TRUE

Valid Values

TRUE | FALSE

ASMUserForDBCredentials

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ASMUserForDBCredentials property specifies the IBM EMM user name that is assigned to the HCL Campaign system user (required for accessing the Campaign system tables).

This property is undefined by default.

Default value

No default value defined.

BulkInsertBlockSize

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property defines the maximum size of a data block, in number of records, that Campaign passes to the database at a time.

Default value

100

BulkInsertRequiresColumnType

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The BulkInsertRequiresColumnType property is required to support Data Direct ODBC data sources only. Set this property to TRUE for Data Direct ODBC data sources when you use bulk (array) inserts. Set the property to FALSE to be compatible with most other ODBC drivers.

Default value

FALSE

BulkReaderBlockSize

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The BulkReaderBlockSize property defines the size of a data block, in number of records, that Campaign reads from the database at a time.

Default value

2500

ConditionalSQLCloseBracket

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ConditionalSQLCloseBracket property specifies the type of bracket that is used to indicate the end of a conditional segment in raw SQL custom macros. Conditionalized segments that are enclosed in the specified open and close bracket type are used only if temp tables exist. They are ignored if there are no temp tables.

Default value

} (closing curly brace)

ConditionalSQLOpenBracket

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ConditionalSQLOpenBracket property specifies the type of bracket used to indicate the start of a conditional segment in raw SQL custom macros. Conditionalized segments enclosed within the brackets specified by the ConditionalSQLOpenBracket and ConditionalSQLCloseBracket properties are used only if temp tables exist, and are ignored if there are no temp tables.

Default value

{ (opening curly brace)

ConnectionCacheSize

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ConnectionCacheSize property specifies the number of connections that Campaign maintains in a cache for each data source.

By default (N=0), Campaign establishes a new connection to a data source for each operation; if Campaign maintains a cache of connections and a connection is available for reuse, Campaign uses the cached connection rather than establishing a new connection.

If the setting is not 0, when a process is done with a connection, Campaign keeps up to the specified number of connections open for an amount of time that is specified by the InactiveConnectionTimeout property. After this time expires, the connections are removed from the cache and closed.

Default value

0 (zero)

DateFormat

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Campaign uses the value of the DateFormat property to determine how to parse data in date formats when using the Campaign macro language or when interpreting data from date columns.

Set the value of the DateFormat property to the format in which Campaign expects to receive dates from this data source. The value must match the format that your database uses to display dates on select. For most databases, this setting is the same as the setting for the DateOutputFormatString property.

Note: If you use the multi-locale feature, do not use date formats that contain 3-letter months (MMM), %b (abbreviated month name), or %B (full month name). Instead, use a delimited or fixed format with a numeric value for the month.

To determine the date format that your database uses, select a date from the database as described below.

Selecting a date by database

Table 1. Date formats

Database	To determine the correct setting
DB2	Connect to the database from a machine that is running the Campaign server. Use db2test in the Campaign\bin directory to connect and issue the following command: values current date If your operating system does not provide the db2test utility, use the cxntest utility to test connections to the target database. g
Netezza®	Connect to the database from a machine that is running the Campaign server. Use odbctest, in the Campaign\bin directory, to connect and issue the following command: CREATE TABLE date_test (f1 DATE); INSERT INTO date_test values (current_date); SELECT f1 FROM date_test; Another way to select date format is to run following command: SELECT current_date FROM ANY_TABLE limit 1; where ANY_TABLE is the name of any existing table
Oracle	Log in to the database from the machine that is running the Campaign server. Use SQL *Plus to connect and issue the following command: SELECT sysdate FROM dual The current date is returned in NLS_DATE_FORMAT for that client.
SQL Server	Connect to the database from a machine that is running the Campaign listener. Use odbctest, in the Campaign\bin directory, to connect and issue the following command: SELECT getdate()

Additional considerations

Note the following database-specific instructions.

Teradata

Teradata allows you to define the date format on a per-column basis. In addition to dateFormat and dateOutputFormatString, you must set SuffixOnCreateDateField. To be consistent with our system table settings, use:

• SuffixOnCreateDateField = FORMAT 'YYYY-MM-DD'
• DateFormat = DELIM_Y_M_D
• DateOutputFormatString = %Y-%m-%d

SQL Server

If the Use regional settings when outputting currency, numbers, dates, and times option is not checked in the ODBC data source configuration, then you cannot reset the date format. In general, it is easier to leave this setting cleared so that the date format configuration does not change for each language.

Default value

DELIM_Y_M_D

Valid Values

Any of the formats that are specified in the DATE macro

DateOutputFormatString

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DateOutputFormatString property specifies the format of the date datatype to be used when Campaign writes any date, such as a campaign start or end date, to a database. Set the value of the DateOutputFormatString property to the format that the data source expects for columns of the type date. For most databases, this setting is the same as the setting for the [data_source_name] > DateFormat property.

The DateOutputFormatString property can be set to any of the formats that are specified for format_strin the DATE_FORMAT macro. The DATE_FORMAT macro accepts two different kinds of formats. One is an identifier (for example, DELIM_M_D_Y, DDMMMYYYY, the same as accepted by the DATE macro), while the other is a format string. The value of the DateOutputFormatString property must be a format string - it must not be one of the DATE macro identifiers. Typically, use one of the delimited formats.

You can verify whether you selected the correct format by creating a table and inserting a date in the format you selected, as described in the following procedure.

To verify DateOutputFormatString

1. Connect to the database using the appropriate tool, as described in the table for "Selecting a date by database".

   Do not use the query tools that come with the database (such as SQL Server's Query Analyzer) to verify that dates are being sent to the database correctly. These query tools might convert the date format to something other than what Campaign actually sent to the database.

2. Create a table and insert a date in the format you selected. For example, if you selected %m/%d/%Y:

   CREATE TABLE date_test (F1 DATE) 
   INSERT INTO date_test VALUES ('03/31/2004')

   If the database allows the INSERT command to complete successfully, then you selected the correct format.

Default value

%Y/%m/%d

DateTimeFormat

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The value of the [data_source_name] > DateTimeFormat property specifies the format in which Campaign expects to receive datetime/timestamp data from a database. It must match the format your database uses to display datetime/timestamp data on select. For most databases, this setting is the same as the setting for DateTimeOutputFormatString.

Typically, you set the DateTimeFormat by prepending your DateFormat value with DT_ after determining the DateFormat value as described in the table for " Selecting a date by database".

Note: If you use the multi-locale feature, do not use date formats that contain 3-letter months (MMM), %b (abbreviated month name), or %B (full month name). Instead, use a delimited or fixed format with a numeric value for the month.

Default value

DT_DELIM_Y_M_D

Valid Values

Only delimited formats are supported, as follows:

• DT_DELIM_M_D
• DT_DELIM_M_D_Y
• DT_DELIM_Y_M
• DT_DELIM_Y_M_D
• DT_DELIM_M_Y
• DT_DELIM_D_M
• DT_DELIM_D_M_Y

DateTimeOutputFormatString

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DateTimeOutputFormatString property specifies the format of the datetime datatype to be used when Campaign writes any datetime, such as a campaign start or end date and time, to a database. Set the value of the DateTimeOutputFormatString property to the format that the data source expects for columns of the type datetime. For most databases, this setting is the same as the setting for the [data_source_name] > DateTimeFormat property.

See DateOutputFormatString for a method for verifying that the format you select is correct.

Default value

%Y/%m/%d %H:%M:%S

DB2NotLoggedInitially

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property determines whether HCL Campaign uses the not logged initially SQL syntax when populating temporary tables in DB2.

A value of TRUE disables logging for inserts into temp tables, which improves performance and decreases database resource consumption. When set to TRUE, if a temp table transaction fails for any reason, the table will become corrupted and must be dropped. All data previously contained in the table will be lost.

If your version of DB2 does not support the not logged initially syntax, set this property to FALSE.

If you are using a DB2 11 user database on z/OS®, set this property to FALSE. If you are using DB2 10.5 with the BLU feature ON for a user database, set both DB2NotLoggedInitially and DB2NotLoggedInitiallyUserTables to FALSE.

Default value

TRUE

Valid Values

TRUE | FALSE

DB2NotLoggedInitiallyUserTables

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DB2NotLoggedInitiallyUserTables property determines whether HCL Campaign uses the not logged initially SQL syntax for inserts into DB2 user tables.

A value of TRUE disables logging for inserts into the user tables, which improves performance and decreases database resource consumption. When set to TRUE, if a user table transaction fails for any reason, the table will become corrupted and must be dropped. All data previously contained in the table will be lost.

If you are using DB2 10.5 with the BLU feature ON for a user database, set both DB2NotLoggedInitially and DB2NotLoggedInitiallyUserTables to FALSE.

Note: The DB2NotLoggedInitiallyUserTables property is not used for the HCL Campaign system tables.

Default value

FALSE

Valid Values

TRUE | FALSE

DefaultScale

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DefaultScale property is used when Campaign creates a database field to store numeric values from a flat file or a derived field, when using the Snapshot or Export process.

This property is not used for numeric values originating in a database table, unless the database field omits information about precision and scale. (Precision indicates the total number of digits allowed for the field. Scale indicates the number of digits allowed to the right of the decimal point. For example, 6.789 has a precision of 4 and a scale of 3. Values obtained from a database table include information about precision and scale, which Campaign uses when creating the field.)

Example: Flat files do not indicate precision and scale so you can use DefaultScale to specify how many places to the right of the decimal point to define for the field that is created. For example:

• DefaultScale=0 creates a field with no places to the right of the decimal point (only whole numbers can be stored).
• DefaultScale=5 creates a field with a maximum of 5 values to the right of the decimal point.

If the value set for DefaultScale exceeds the field's precision, DefaultScale=0 is used for those fields. For example, if the precision is 5, and DefaultScale=6, a value of zero is used.

Default value

0 (zero)

DefaultTextType

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DefaultTextType property is intended for ODBC data sources. This property tells Campaign how to create text fields in the destination data source if the source text fields are from a different data source type. For example, the source text fields might be from a flat file or from a different type of DBMS. If the source text fields are from the same type of DBMS, this property is ignored and the text fields are created in the destination data source using the data types from the source text fields.

Default value

VARCHAR

Valid Values

VARCHAR | NVARCHAR

DeleteAsRecreate

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DeleteAsRecreate property specifies whether, when an output process is configured to REPLACE TABLE and if TRUNCATE is not supported, Campaign drops and recreates the table or only deletes from the table.

When the value is TRUE, Campaign drops the table and recreates it.

When the value is FALSE, Campaign executes a DELETE FROM from the table.

Default value

FALSE

Valid Values

TRUE | FALSE

DeleteAsTruncate

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DeleteAsTruncate property specifies whether, when an output process is configured to REPLACE TABLE, Campaign uses TRUNCATE TABLE or deletes from the table.

When the value is TRUE, Campaign runs a TRUNCATE TABLE from the table.

When the value is FALSE, Campaign runs a DELETE FROM from the table.

The default value depends on the database type.

Default value

• TRUE for Netezza, Oracle, and SQLServer.
• FALSE for other database types.

Valid Values

TRUE | FALSE

DisallowTempTableDirectCreate

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property is used by Oracle, Netezza, and SQL Server data sources and is ignored for all other data sources.

This property specifies the way Campaign adds data to a temp table.

When set to FALSE, Campaign performs direct create-and-populate SQL syntax using one command. For example: CREATE TABLE <table_name> AS ... (for Oracle and Netezza) and SELECT <field_names> INTO <table_name> ... (for SQL Server).

When set to TRUE, Campaign creates the temp table and then populates it directly from table to table using separate commands.

Default value

FALSE

Valid Values

TRUE | FALSE

DSN

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Set this property to the data source name (DSN) as assigned in your ODBC configuration for this Campaign data source. This value is undefined by default.

Using the Campaign data source configuration properties, you can specify multiple logical data sources that refer to the same physical data source. For example, you can create two sets of data source properties for the same data source, one with AllowTempTables = TRUE and the other with AllowTempTables = FALSE. Each of these data sources would have a different name in Campaign, but if they refer to the same physical data source and they will have the same DSN value.

Default value

No default value defined.

DSNUsingOSAuthentication

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The DSNUsingOSAuthentication property applies only when an Campaign data source is SQL Server. Set the value to TRUE when the DSN is configured to use Windows™ Authentication mode.

Default value

FALSE

Valid Values

TRUE | FALSE

EnableBaseDimSelfJoin

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The EnableBaseDimSelfJoin property specifies whether the Campaign database behavior will perform self-joins when the Base and Dimension tables are mapped to the same physical table and the Dimension is not related to the Base table on the Base table's ID field(s).

By default, this property is set to FALSE, and when the Base and Dimension tables are the same database table and the relationship fields are the same (for example, AcctID to AcctID), Campaign assumes that you do not want to perform a join.

Default value

FALSE

EnableSelectDistinct

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The EnableSelectDistinct property specifies whether the internal lists of IDs for Campaign are de-duplicated by the Campaign server or by the database.

When the value is TRUE, the database performs de-duplication, and SQL queries generated against the database then have the form (when appropriate):

SELECT DISTINCT key FROM table

When the value is FALSE, the Campaign server performs de-duplication, and SQL queries generated against the database have the form:

SELECT key FROM table

Leave the default value of FALSE if:

• Your database is constructed so that unique identifiers (primary keys of base tables) are already guaranteed to be de-duped.
• You want the Campaign application server to perform de-duplication to reduce resource consumption/burden on the database.

Regardless of what value you specify for this property, Campaign automatically ensures that keys are de-duplicated as required. This property merely controls where the de-duplication effort occurs (on the database or on the Campaign server).

Default value

TRUE

Valid Values

TRUE | FALSE

EnableSelectOrderBy

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The EnableSelectOrderBy property specifies whether the internal lists of IDs for Campaignare sorted by the Campaign server or by the database.

When the value is TRUE, the database performs the sorting, and SQL queries generated against the database have the form:

SELECT <key> FROM <table> ORDER BY <key>

When the value is FALSE, the Campaign server performs the sorting, and SQL queries generated against the database have the form:

SELECT <key> FROM <table>

Note: Only set this property to FALSE if the audience levels used are text strings on a non-English database. All other scenarios can use the default of TRUE.

Default value

TRUE

Valid Values

True | False

ExcludeFromTableDisplay

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ExcludeFromTableDisplay parameter allows you to limit the database tables that are displayed during table mapping in HCL Campaign. It does not reduce the number of table names retrieved from the database. Table names that match the specified patterns are not displayed. Values for this parameter are case-sensitive.

Example: If you set the value to sys.*, tables with names that begin with all lower case sys. are not displayed.

Example: UAC_* (the default value for SQL Server data sources) excludes temp tables and Extract tables, when the ExtractTablePrefix property's value is the default value.

Example: To exclude the HCL Marketing Platform system tables, as they are not relevant when working with user data:

DF_*,USM_*,OLS_*,QRTZ*,USCH_*,UAR_*

Using Oracle as an example, the complete value would be:

UAC_*,PUBLIC.*,SYS.*,SYSTEM.*,DF_*,USM_*,OLS_*,QRTZ*, USCH_*,UAR_*

Default value

UAC_*,PUBLIC.*,SYS.*,SYSTEM.* (for an Oracle data source)

UAC_* (for a SQL Server data source)

UAC_*,SYSCAT.*,SYSIBM.*,SYSSTAT.* (for a DB2 data source)

ExtractTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the ExtractTablePostExecutionSQL property to specify one or more complete SQL statements that run immediately after the creation and population of an Extract table.

Tokens available to ExtractTablePostExecutionSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Extract table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Extract table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Extract table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Extract table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Extract table creation.
<KEYCOLUMNS>	This token is replaced with the Extract table column name(s).
<TABLENAME>	This token is replaced with the Extract table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

A valid SQL statement

ExtractTablePrefix

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ExtractTablePrefix property specifies a string that is automatically prepended to all Extract table names in Campaign. This property is useful when two or more data sources point to the same database. For details, see the TempTablePrefix description.

Default value

UAC_EX

ForceNumeric

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ForceNumeric property specifies whether Campaign retrieves numeric values as the data type double. When the value is set to TRUE, Campaign retrieves all numeric values as the data type double.

Default value

FALSE

Valid Values

TRUE | FALSE

InactiveConnectionTimeout

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The InactiveConnectionTimeout property specifies the number of seconds an inactive Campaign database connection is left open before it is closed. Setting the value to 0 disables the timeout, leaving the connection open.

Default value

120

InsertLogSize

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The InsertLogSize property specifies when a new entry is entered in the log file while the Campaign Snapshot process is running. Every time the number of records written by the Snapshot process reaches a multiple of the number specified in the InsertLogSize property, a log entry is written. The log entries can help you determine how far a running Snapshot process has progressed. Setting this value too low may create large log files.

Default value

100000 (one hundred thousand records)

Valid Values

Positive integers

JndiName

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The JndiName property is used only when configuring the Campaign system tables (not for other data sources, such as customer tables). Set its value to the Java™ Naming and Directory Interface (JNDI) data source that is defined in the application server (WebSphere® or WebLogic).

Default value

campaignPartition1DS

LoaderCommand

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the command issued to invoke your database load utility in HCL Campaign. If you set this property, HCL Campaign enters the database loader utility mode for all output files from the Snapshot process that are used with the Replace All Records settings. This property also invokes the database loader utility mode when HCL Campaign uploads ID lists into temp tables.

The valid value for this property is any full path name either to the database load utility executable or to a script that launches the database load utility. Using a script allows you to perform additional setup before invoking the load utility.

Note: If you use HCL Contact Optimization and you are configuring loader settings for the UA_SYSTEM_TABLES datasource, there are important considerations to take into account. For example, you must use absolute paths for LoaderCommand and LoaderCommandForAppend. Read about setting up Campaign to use database load utilities in the HCL Campaign Administrator's Guide.

Most database load utilities require several arguments to be launched successfully. These arguments can include specifying the data file and control file to load from and the database and table to load into. HCL Campaign supports the following tokens, which are replaced by the specified elements when the command is run. Consult your database load utility documentation for the correct syntax to use when invoking your database load utility.

This property is undefined by default.

Tokens available to LoaderCommand are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart being run.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart being run.
<CONTROLFILE>	This token is replaced with the full path and file name to the temporary control file that HCL Campaign generates according to the template that is specified in the LoaderControlFileTemplate property.
<DATABASE>	This token is replaced with the name of the data source that HCL Campaign is loading data into. This is the same data source name used in the category name for this data source.
<DATAFILE>	This token is replaced with the full path and file name to the temporary data file created by HCL Campaign during the loading process. This file is in the HCL Campaign Temp directory, UNICA_ACTMPDIR.
<DBUSER>	This token is replaced with the database user name for the database.
<DSN>	This token is replaced with the value of the DSN property. If the DSN property is not set, the <DSN> token is replaced by the data source name used in the category name for this data source (the same value used to replace the <DATABASE> token).
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart being run.
<NUMFIELDS>	This token is replaced with the number of fields in the table.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<TABLE>	This token is obsolete. Use <TABLENAME> instead.
<TABLENAME>	This token is replaced with the database table name that HCL Campaign is loading data into. This is the target table from your Snapshot process or the name of the Temp Table being created by HCL Campaign.
<USER>	This token is replaced with the database user from the current flowchart connection to the data source.

Default value

No default value defined.

Valid Values

Any full path name either to the database load utility executable or to a script that launches the database load utility.

LoaderCommandForAppend

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the command issued to invoke your database load utility for appending records to a database table in HCL Campaign. If you set this property, HCL Campaign enters database loader utility mode for all output files from the Snapshot process that are used with the Append Records settings.

This property is specified as a full path name either to the database load utility executable or to a script that launches the database load utility. Using a script allows you to perform additional setup before invoking the load utility.

Most database load utilities require several arguments to be successfully launched. These can include specifying the data file and control file to load from and the database and table to load into. The tokens are replaced by the specified elements when the command is run.

Consult your database load utility documentation for the correct syntax to use when invoking your database load utility.

This property is undefined by default.

Tokens available to LoaderCommandForAppend are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart being run.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart being run.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart being run.
<CONTROLFILE>	This token is replaced with the full path and file name to the temporary control file that Campaign generates according to the template that is specified in the LoaderControlFileTemplate property.
<DATABASE>	This token is replaced with the name of the data source that HCL Campaign is loading data into. This is the same data source name used in the category name for this data source.
<DATAFILE>	This token is replaced with the full path and file name to the temporary data file created by HCL Campaign during the loading process. This file is in the Campaign Temp directory, UNICA_ACTMPDIR.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<DSN>	This token is replaced with the value of the DSN property. If the DSN property is not set, the <DSN> token is replaced by the data source name used in the category name for this data source (the same value used to replace the <DATABASE> token).
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<NUMFIELDS>	This token is replaced with the number of fields in the table.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<TABLE>	This token is obsolete. Use <TABLENAME> instead.
<TABLENAME>	This token is replaced with the database table name that HCL Campaign is loading data into. This is the target table from your Snapshot process or the name of the Temp Table being created by HCL Campaign.
<USER>	This token is replaced with the database user from the current flowchart connection to the data source.

Default value

No default value defined.

LoaderControlFileTemplate

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the full path and file name to the control file template that is configured for HCL Campaign. The path to the template is relative to the current partition. For example: loadscript.db2.

When this property is set, HCL Campaign dynamically builds a temporary control file based on the specified template. The path and name of this temporary control file is available to the <CONTROLFILE> token that is available to the LoaderCommand property.

Before you use HCL Campaign in the database loader utility mode, you must configure the control file template that is specified by this parameter. The control file template supports the following tokens, which are dynamically replaced when the temporary control file is created by HCL Campaign.

For the correct syntax required for your control file, see your database loader utility documentation.

This property is undefined by default.

Tokens available to LoaderControlFileTemplate are the same as those described for the LoaderCommand property, plus the following special tokens, which are repeated once for each field in the outbound table.

Token	Description
<DBCOLUMNNUMBER>	This token is replaced with the column ordinal in the database.
<FIELDLENGTH>	This token is replaced with the length of the field being loaded into the database.
<FIELDNAME>	This token is replaced with the name of the field being loaded into the database.
<FIELDNUMBER>	This token is replaced with the number of the field being loaded into the database.
<FIELDTYPE>	This token is replaced with the literal CHAR( ). The length of this field is specified between the parentheses (). If your database does not understand the field type CHAR, you can manually specify the appropriate text for the field type and use the <FIELDLENGTH> token. For example, for SQLSVR and SQL2000 you would use SQLCHAR(<FIELDLENGTH>).
<NATIVETYPE>	This token is replaced with the actual database type that this field is loaded into.
<xyz>	This token places the specified character(s) on all fields being loaded into the database, except the last. A typical use is <,> which repeats a comma for all fields except the last.
<~xyz>	This token places the specified characters only on the last repeated line.
<!xyz>	This token places the specified character(s), including the angle brackets < >, on all lines.

Default value

No default value defined.

LoaderControlFileTemplateForAppend

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the full path and file name to the control file template that is configured in HCL Campaign. The path to the template is relative to the current partition. For example: loadappend.db2

When this property is set, HCL Campaign dynamically builds a temporary control file based on the specified template. The path and name of this temporary control file is available to the <CONTROLFILE> token that is available to the LoaderCommandForAppend property.

Before you use HCL Campaign in the database loader utility mode, you must configure the control file template that is specified by this property. See your database loader utility documentation for the correct syntax required for your control file.

The available tokens are the same as the tokens for the LoaderControlFileTemplate property.

This property is undefined by default.

Default value

No default value defined.

LoaderDelimiter

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies whether the temporary data file is a fixed-width or delimited flat file, and, if it is delimited, the characters that HCL Campaign uses as delimiters.

If the value is undefined, HCL Campaign creates the temporary data file as a fixed width flat file.

If you specify a value, it is used when the loader is invoked to populate a table that is known to be empty. HCL Campaign creates the temporary data file as a delimited flat file, using the value of this property as the delimiter.

This property is undefined by default.

Default value

No default value defined.

Valid Values

Characters, which can be enclosed in double quotation marks, if wanted.

LoaderDelimiterAtEnd

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Some external load utilities require that the data file be delimited and that each line end with the delimiter. To accommodate this requirement, set the LoaderDelimiterAtEnd value to TRUE, so that when the loader is invoked to populate a table that is known to be empty, HCL Campaign uses delimiters at the end of each line. For example, DB2 on Unix expects each record to be terminated by a line feed character only; Campaign Campaign on Windows uses carriage return and line feed characters. Putting a delimiter at the end of every record ensures that the last column in the data file will load properly.

FALSE

Default value

FALSE

Valid Values

TRUE | FALSE

LoaderDelimiterAtEndForAppend

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Some external load utilities require that the data file be delimited and that each line end with the delimiter. To accommodate this requirement, set the LoaderDelimiterAtEndForAppend value to TRUE, so that when the loader is invoked to populate a table that is not known to be empty, HCL Campaign uses delimiters at the end of each line. For example, DB2 on Unix expects each record to be terminated by a line feed character only; HCL Campaign on Windows uses carriage return and line feed characters. Putting a delimiter at the end of every record ensures that the last column in the data file will load properly.

Default value

FALSE

Valid Values

TRUE | FALSE

LoaderDelimiterForAppend

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies whether the temporary HCL Campaign data file is a fixed-width or delimited flat file, and, if it is delimited, the character or set of characters used as delimiters.

If the value is undefined, HCL Campaign creates the temporary data file as a fixed width flat file.

If you specify a value, it is used when the loader is invoked to populate a table that is not known to be empty. HCL Campaign creates the temporary data file as a delimited flat file, using the value of this property as the delimiter.

This property is undefined by default.

Default value

No default value defined.

Valid Values

Characters, which you may enclose in double quotation marks, if wanted.

LoaderNULLValueInDelimitedData

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property supports null values in delimited data for database loaders, specifically Netezza. Enter the string that represents a null value for the column.

Default value

null

LoaderUseLocaleDP

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies, when HCL Campaign writes numeric values to files to be loaded by a database load utility, whether the locale-specific symbol is used for the decimal point.

Set this value to FALSE to specify that the period (.) is used as the decimal point.

Set this value to TRUE to specify that the decimal point symbol appropriate to the locale is used.

Default value

FALSE

Valid Values

TRUE | FALSE

MaxItemsInList

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Allows you to specify the maximum number of items that HCL Campaign is allowed to include in a single list in SQL (for example, the list of values following an IN operator in a WHERE clause).

Default value

1000 (Oracle only), 0 (unlimited) for all other databases

Valid Values

integers

MaxQueryThreads

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the upper limit on the number of simultaneous queries allowed to run against each database source from a single HCL Campaign flowchart. Higher values generally improve performance.

HCL Campaign runs database queries using independent threads. Because HCL Campaign processes run in parallel, it is common to have multiple queries running simultaneously against a single data source. If the number of queries to be run in parallel exceeds the MaxQueryThreads, the HCL Campaign server limits the number of simultaneous queries to the specified value.

The maximum value is unlimited.

Note: If maxReuseThreads is set to a non-zero value, it should be greater than or equal to the value of MaxQueryThreads.

Default value

Varies depending on the database

MaxRowFetchRecords

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

For performance reasons, it is best to keep this number low.

When the selected number of IDs is less than the value specified by the MaxRowFetchRecords property, HCL Campaign passes the IDs to the database one at a time, in separate SQL queries. This process can be very time-consuming. If the number of selected IDs is greater than the value specified by this property, HCL Campaign uses temporary tables (if allowed on the database source), or it pulls down all the values from the table, not including any unnecessary values.

Default value

100

MaxTempTableJoinPctSelectAll

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

When a query is issued, HCL Campaign creates a temporary table on the database containing the exact list of IDs, as a result of the query. When an additional query that selects all records is issued against the database, the MaxTempTableJoinPctSelectAll property specifies whether a join is performed with the temporary table.

If the relative size of the temporary table (specified as a percentage) is greater than the value of the MaxTempTableJoinPctSelectAll property, no join is performed. All records are selected first, then unwanted records are discarded.

If the relative size of the temporary table (specified as a percentage) is less than or equal to the value of MaxTempTableJoinPctSelectAll property, the join is performed with the temporary table first, and then the resulting IDs are retrieved to the server.

This property is applicable only if the value of the AllowTempTables property is set to TRUE. This property is ignored if the useInDbOptimization property is set to YES.

Default value

90

Valid Values

Integers between 0-100. A value of 0 means that temporary table joins are never used; a value of 100 means that table joins are always used, regardless of the size of the temporary table.

Example

Assume that MaxTempTableJoinPctSelectAll is set to 90. First, you might want to select customers (CustID) with account balances (Accnt_balance) greater than $1,000 from the database table (Customer).

The corresponding SQL expression generated by the Select process may look like this:

SELECT CustID FROM Customer
	WHERE Accnt_balance > 1000

The Select process may retrieve 100,000 IDs from the total table size of 1,000,000, which is 10%. If temporary tables are allowed, HCL Campaign writes the selected IDs (TempID) into a temporary table (Temp_table) in the database.

Then, you might want to snapshot the selected IDs (CustID) together with the actual balance (Accnt_balance). Since the relative size of the temporary table (Temp_table) is less than 90 percent (MaxTempTableJoinPctSelectAll), the join is done with the temporary table first. The SQL expression generated by the Snapshot process may look like this:

SELECT CustID, Accnt_balance FROM Customer, Temp_table WHERE CustID = TempID

If the Select process retrieves more than 90 percent, the subsequent Snapshot process retrieves all the records, and matches them with the first set of IDs, discarding the unnecessary ones.

The SQL expression generated by the Snapshot process may look like this:

SELECT CustID, Accnt_balance FROM Customer

MaxTempTableJoinPctWithCondition

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

When a query is issued, HCL Campaign creates a temporary table on the database containing the exact list of IDs, as a result of the query. When an additional query, selecting records with limitation conditions is issued against the database, the MaxTempTableJoinPctWithCondition property specifies whether a join should be performed with the temporary table.

If the relative size of the temporary table (specified as a percentage) is greater than the value of MaxTempTableJoinPctWithCondition, no join is performed. This avoids the overhead in the database where it may not be needed. In this case, the query is issued against the database, the resulting list of IDs retrieved, and then unwanted records are discarded as they are matched to the list in server memory.

If the relative size of the temporary table (in percentage) is less than or equal to the value of MaxTempTableJoinPctWithCondition, the join is done with the temporary table first, and then the resulting IDs are retrieved to the server.

This property is applicable only if the value of the AllowTempTables property is set to TRUE.

Default value

20

Valid Values

Integers between 0-100. A value of 0 means that temporary table joins are never used; a value of 100 means that table joins are always used, regardless of the size of the temporary table.

MinReqForLoaderCommand

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to set the threshold for using the bulk loader. HCL Campaign invokes the script assigned to the LoaderCommand propery when the number of unique IDs in the input cell exceeds the value defined here. The value of this property does not represent the number of records that will be written.

If this property is not configured, HCL Campaign assumes that the value is the default value (zero). If this property is configured but a negative value or non-integer value is set as the value, a value of zero is assumed.

Default value

0 (zero)

Valid Values

Integers

MinReqForLoaderCommandForAppend

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to set the threshold for using the bulk loader. HCL Campaign invokes the script assigned to the LoaderCommandForAppend parameter when the number of unique IDs in the input cell exceeds the value defined here. The value of this property does not represent the number of records that will be written.

If this property is not configured, HCL Campaign assumes that the value is the default value (zero). If this property is configured but a negative value or non-integer value is set as the value, a value of zero is assumed.

Default value

0 (zero)

Valid Values

Positive integers

NumberOfRetries

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The NumberOfRetries property specifies the number of times HCL Campaign automatically retries a database operation on failure. HCL Campaign automatically resubmits queries to the database this number of times before reporting a database error or failure.

Default value

0 (zero)

ODBCTableTypes

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property is empty by default, which is appropriate for all currently supported data sources.

Default value

Not defined

Valid Values

(empty)

ODBCUnicode

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ODBCUnicode property specifies the type of encoding used in HCL Campaign ODBC calls. It is used only with ODBC data sources and is ignored when used with Oracle or DB2 native connectivity.

Important: If this property is set to UTF-8 or UCS-2, the data source's StringEncoding value must be set to either UTF-8 or WIDEUTF-8, otherwise the ODBCUnicode property's setting is ignored.

Default value

disabled

Valid Values

Possible values for this property are:

• Disabled: HCL Campaign uses ANSI ODBC calls.
• UTF-8: HCL Campaign uses Unicode ODBC calls and assumes that a SQLWCHAR is a single byte. This is compatible with DataDirect ODBC drivers.
• UCS-2: HCL Campaign uses Unicode ODBC calls and assumes that a SQLWCHAR is 2 bytes. This is compatible with Windows and unixODBC ODBC drivers.

ODBCv2

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the ODBCv2 property to specify which ODBC API specification HCL Campaign should use for the data source.

The default value of FALSE allows HCL Campaign to use the v3 API specification, while a setting of TRUE causes HCL Campaign to use the v2 API specification. Set the ODBCv2 property to TRUE for data sources that do not support the ODBC v3 API specification.

When the ODBCv2 property is set to TRUE, HCL Campaign does not support the ODBC Unicode API, and values other than disabled for the ODBCUnicode property are not recognized.

Default value

FALSE

Valid Values

TRUE | FALSE

OwnerForTableDisplay

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to limit the table mapping display in HCL Campaign to tables in a specified schema. For example, to specify tables in the schema "dbo", set OwnerForTableDisplay=dbo.

Default value

No default value defined.

PadTextWithSpaces

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

When set to TRUE, the PadTextWithSpaces property causes HCL Campaign to pad text values with spaces until the string is the same width as the database field.

Default value

FALSE

Valid Values

TRUE | FALSE

PostExtractTableCreateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to specify a script or executable for HCL Campaign to run after an Extract table has been created and populated.

Tokens available to PostExtractTableCreateRunScript are described below.

Token	Description
<DBUSER>	This token is replaced with the database user name for the database where the Extract table was created.
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Extract table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Extract table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Extract table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Extract table creation.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<KEYCOLUMNS>	This token is replaced with the Extract table column name(s).

Default value

Not defined

Valid Values

File name of a shell script or executable

PostSegmentTableCreateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Specifies a script or executable that HCL Campaign runs after a Segment temp table has been created and populated.

Tokens available to PostSegmentTableCreateRunScript are described below.

Token	Description
<DBUSER>	This token is replaced with the database user name for the database where the Segment temp table was created.
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Segment temp table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Segment temp table creation.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<KEYCOLUMNS>	This token is replaced with the Segment temp table column name(s).

Default value

Not defined

Valid Values

File name of a script or executable

PostSnapshotTableCreateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the PostSnapshotTableCreateRunScript property to specify a script or executable that Campaign runs after a Snapshot table has been created and populated.

Tokens available to PostSnapshotTableCreateRunScript are described below.

Token	Description
<DBUSER>	This token is replaced with the database user name for the database where the Snapshot table was created.
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Snapshot table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Snapshot table creation.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<KEYCOLUMNS>	This token is replaced with the Snapshot table column name(s).

Default value

Not defined

Valid Values

File name of a shell script or executable

PostTempTableCreateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the PostTempTableCreateRunScript property to specify a script or executable for Campaign to run after a temp table has been created and populated in a user data source or in the system tables database.

Tokens available to PostTempTableCreateRunScript are described below.

Token	Description
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<KEYCOLUMNS>	This token is replaced with the temp table column name(s).

Default value

No default value defined.

PostUserTableCreateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Specifies a script or executable that Campaign runs after a User table has been created and populated.

Tokens available to PostUserTableCreateRunScript are described below.

Token	Description
<DBUSER>	This token is replaced with the database user name for the database where the User table was created.
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the User table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the User table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the User table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the User table creation.
<PASSWORD>	This token is replaced with the database password from the current flowchart connection to the data source.
<KEYCOLUMNS>	This token is replaced with the User table column name(s).

Default value

Not defined

Valid Values

File name of a script or executable

PrefixOnSelectSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the PrefixOnSelectSQL property to specify a string that is automatically prepended to all SELECT SQL expressions generated by Campaign.

This property applies only to SQL generated by Campaign, and does not apply to SQL in raw SQL expressions used in the Select process.

This property is automatically added to the SELECT SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression.

This property is undefined by default.

Tokens available to PrefixOnSelectSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

QueryThreadSleep

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The QueryThreadSleep property affects the CPU utilization of the Campaign server process (UNICA_ACSVR). When the value is TRUE, the thread that the Campaign server process uses to check for query completion sleeps between checks. When the value is FALSE, the Campaign server process checks continuously for query completion.

Default value

TRUE

ReaderLogSize

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The ReaderLogSize parameter defines when Campaign makes a new entry in the log file when reading data from the database. Every time the number of records read from the database reaches a multiple of the number defined by this parameter, a log entry is written in the log file.

This parameter can help you determine how far a process has progressed in its run. Setting this value too low may create large log files.

Default value

1000000 (one million records)

Valid Values

Integers

SegmentTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SegmentTablePostExecutionSQL property to specify a complete SQL statement that Campaign runs after a Segment temp table has been created and populated.

Tokens available to SegmentTablePostExecutionSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Segment temp table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Segment temp table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Segment temp table creation.
<KEYCOLUMNS>	This token is replaced with the Segment temp table column name(s).
<TABLENAME>	This token is replaced with the Segment temp table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

A valid SQL statement

SegmentTempTablePrefix

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Sets the prefix for Segment tables created by the CreateSeg process in this data source. This property is useful when two or more data sources point to the same database. For details, see the TempTablePrefix description.

Default value

UACS

SnapshotTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SnapshotTablePostExecutionSQL property to specify one or more complete SQL statements to run immediately after a Snapshot table has been created and populated. This property is invoked only when a Snapshot process box writes out to an extract table.

Tokens available to SnapshotTablePostExecutionSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Snapshot table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Snapshot table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Snapshot table creation.
<KEYCOLUMNS>	This token is replaced with the Snapshot table column name(s).
<TABLENAME>	This token is replaced with the Snapshot table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

A valid SQL statement

SQLOnConnect

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SQLOnConnect property defines a complete SQL statement that Campaign runs immediately after each database connection.

The SQL statement generated by this property is automatically passed to your database without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SQLOnConnect are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

StringEncoding

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The StringEncoding property specifies the character encoding of the database. When Campaign retrieves data from the database, the data is transcoded from the encoding specified to the internal encoding of Campaign (UTF-8). When Campaign sends a query to the database, character data is transcoded from the internal encoding of Campaign (UTF-8) to the encoding specified in the StringEncoding property.

The value of this property must match the encoding used on the database client.

Do not leave this value blank although it is undefined by default.

If you use ASCII data, set this value to UTF-8.

If your database client encoding is UTF-8, the preferred setting for this value is WIDEUTF-8. The WIDE-UTF-8 setting works only if your database client is set to UTF-8.

If you use the partitions > partition[n] > dataSources > data_source_name > ODBCUnicode property, set the StringEncoding property to either UTF-8 or WIDEUTF-8. Otherwise, the ODBCUnicode property value is ignored.

For a list of supported encodings, see Character encodings in Campaign in the Campaign Administrator's Guide.

Important: See the following sections for important exceptions and additional considerations.

Default value

No default value defined.

Database-specific considerations

This section describes how to set the correct values for DB2, SQL Server, or Teradata databases.

DB2

Identify the DB2 database code page and code set. For localized environments, the DB2 database must have the following configuration:

• Database code set = UTF-8
• Database code page = 1208

Set the StringEncoding property values in Campaign to the DB2 database code set value.

Set the DB2CODEPAGE DB2 environment variable to the DB2 database code page value:

• On Windows: Add the following line to the Campaign Listener startup script (<CAMPAIGN_HOME>\bin\cmpServer.bat):

  db2set DB2CODEPAGE=1208

• On UNIX™: After DB2 is started, the system administrator must type the following command from the DB2 instance user:

  $ db2set DB2CODEPAGE=1208

  Then start the Campaign listener by running this command:

  ./rc.unica_ac start

This setting affects all DB2 data sources and can affect other running programs.

SQL Server

For SQL Server, use a code page instead of an iconv encoding. To determine the correct the value for the StringEncoding property with a SQL Server database, look up the code page that corresponds to the regional settings of the server's operating system.

For example, to use code page 932 (Japanese Shift-JIS):

StringEncoding=CP932

Teradata

For Teradata, you must override some default behavior. Teradata supports per-column character encoding, while Campaign supports only per-data source encoding. UTF-8 cannot be used with Campaign due to a bug in the Teradata ODBC driver. Teradata sets a default character encoding for each login. You can override this using a parameter in the ODBC data source configuration on Windows or in the odbc.ini on UNIX platforms as follows:

CharacterSet=UTF8

The default encoding for a Teradata table is LATIN. Teradata has very few built-in encodings, but it supports user-defined encodings.

The default value of the StringEncoding property is ASCII.

Important: For many situations involving a UTF-8 database, you should use WIDEUTF-8 pseudo-encoding, described in the WIDEUTF-8 section.

WIDEUTF-8

Campaign is normally responsible for transcoding between its internal encoding, UTF-8, and the encoding of the database. When the database is encoded in UTF-8, the value UTF-8 can be specified for StringEncoding (except for SQLServer), and no transcoding will be needed. Traditionally, these have been the only viable models for Campaign to access non-English data within a database.

In the 7.0 version of Campaign, a new database encoding called WIDEUTF-8 was introduced as a value for the StringEncoding property. By using this encoding, Campaign still uses UTF-8 to communicate with the database client, but allows the client to perform the task of transcoding between UTF-8 and the encoding of the actual database. This enhanced version of UTF-8 is needed to alter the widths of table column mappings so that they will be wide enough for transcoded text.

Note: The WIDEUTF-8 pseudo-encoding may be used only in the database configuration. It should not be used for any other purpose.

Note: Oracle does not support transcoding through the client.

SuffixOnAllOtherSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SuffixOnAllOtherSQL property specifies a string that is automatically appended to every SQL expression, generated by Campaign, which are not covered by the SuffixOnInsertSQL, SuffixOnSelectSQL, SuffixOnTempTableCreation, SuffixOnUserTableCreation, or SuffixOnUserBaseTableCreation properties.

This property applies only to SQL generated by Campaign, and does not apply to SQL in raw SQL expressions used in the Select process.

SuffixOnAllOtherSQL is used for the following expression types, when generated by Campaign:

TRUNCATE TABLE table
	DROP TABLE table
	DELETE FROM table [WHERE ...]
	UPDATE table SET ...

This property is automatically added to the SQL expression without checking its syntax. If you use this parameter, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SuffixOnAllOtherSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

SuffixOnCreateDateField

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SuffixOnCreateDateField property specifies a string that Campaign automatically appends to any DATE fields in the CREATE TABLE SQL statement.

For example, you might set this property as follows:

SuffixOnCreateDateField = FORMAT 'YYYY-MM-DD'

If this property is undefined (the default), the CREATE TABLE command is unchanged.

Note: See the table in the description of the DateFormat property.

Default value

No default value defined.

SuffixOnExtractTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SuffixOnExtractTableCreation property to specify a string that is automatically appended to the SQL expression generated by Campaign when an Extract table is created.

Tokens available to SuffixOnExtractTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Extract table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Extract table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Extract table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Extract table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Extract table creation.
<KEYCOLUMNS>	This token is replaced with the Extract table column name(s).
<TABLENAME>	This token is replaced with the Extract table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

Valid SQL

SuffixOnInsertSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SuffixOnInsertSQL property specifies a string that is automatically appended to all INSERT SQL expressions generated by Campaign. This property applies only to SQL generated by Campaign, and does not apply to SQL in raw SQL expressions used in the Select process.

SuffixOnInsertSQL is used for the following expression type, when generated by Campaign:

INSERT INTO table ...

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SuffixOnInsertSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

SuffixOnSegmentTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Specifies a string that is automatically appended to the SQL expression generated by Campaign when a Segment temp table is created.

Tokens available to SuffixOnSegmentTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Segment temp table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Segment temp table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Segment temp table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Segment temp table creation.
<KEYCOLUMNS>	This token is replaced with the Segment temp table column name(s).
<TABLENAME>	This token is replaced with the Segment temp table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

Valid SQL

SuffixOnSelectSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SuffixOnSelectSQL property specifies a string that is automatically appended to all SELECT SQL expressions generated by Campaign. This property applies only to SQL generated by Campaign, and does not apply to SQL in "raw SQL" expressions used in the Select process.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SuffixOnSelectSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

SuffixOnSnapshotTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SuffixOnSnapshotTableCreation property to specify a string that is automatically appended to the SQL expression generated by Campaign when a Snapshot table is created.

Tokens available to SuffixOnSnapshotTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the Snapshot table was created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the Snapshot table was created.
<DBUSER>	This token is replaced with the database user name for the database where the Snapshot table was created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the Snapshot table creation.
<KEYCOLUMNS>	This token is replaced with the Snapshot table column name(s).
<TABLENAME>	This token is replaced with the Snapshot table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

Not defined

Valid Values

Valid SQL

SuffixOnTempTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SuffixOnTempTableCreation property to specify a string that is automatically appended to the SQL expression generated by Campaign when a temp table is created. This property applies only to SQL generated by Campaign, and does not apply to SQL in "raw SQL" expressions used in the Select process. To use this property, the AllowTempTables property must be set to TRUE.

You may want to use tokens to substitute the table name and the column name(s) (<TABLENAME> and <KEYCOLUMNS>) in this SQL statement, since these are generated dynamically during the execution of the campaign.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Note: For Oracle databases, the configuration parameter is appended to the temp table creation SQL expression after the table name.

Tokens available to SuffixOnTempTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<KEYCOLUMNS>	This token is replaced with the temp table column name(s).
<TABLENAME>	This token is replaced with the temp table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

SuffixOnUserBaseTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SuffixOnUserBaseTableCreation property to specify a string that is automatically appended to the SQL expression that Campaign generates when a user creates a Base table (for example, in an Extract process). This property applies only to SQL generated by Campaign, and does not apply to SQL in "raw SQL" expressions used in the Select process.

You may want to use tokens to substitute the table name and the column name(s) (<TABLENAME> and <KEYCOLUMNS>) in this SQL statement, since these are generated dynamically during the execution of the campaign.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SuffixOnUserBaseTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<KEYCOLUMNS>	This token is replaced with the temp table column name(s).
<TABLENAME>	This token is replaced with the temp table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

SuffixOnUserTableCreation

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the SuffixOnUserTableCreation property to specify a string that is automatically appended to the SQL expression that Campaign generates when a user creates a General table (for example, in a Snapshot process). This property applies only to SQL generated by Campaign, and does not apply to SQL in "raw SQL" expressions used in the Select process.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to SuffixOnUserTableCreation are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<TABLENAME>	This token is replaced with the temp table name.

Default value

No default value defined.

SystemTableSchema

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Specifies the schema used for Campaign system tables.

The default value is blank. This parameter is only relevant for the UA_SYSTEM_TABLES data source.

Leave this value blank unless the UA_SYSTEM_TABLES data source contains multiple schemas (for example, an Oracle database used by multiple groups). (In this context, "schema" indicates the initial portion of a "qualified" table name of the form X.Y (for example, dbo.UA_Folder). In this form, X is the schema and Y is the unqualified table name. This terminology for this syntax differs among the different database systems supported by Campaign.)

If multiple schemas exist in the system tables database, then set this value to the name of the schema in which the Campaign system tables were created.

Default value

No default value defined.

TableListSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use the TableListSQL property to specify the SQL query to use to include synonyms in the list of tables available to map.

The default value is blank. This property is required if your data source is SQL Server and you want to be able to map synonyms in the returned table schema. This property is optional if you want to use a specific SQL query with other data sources in place of, or in addition to, the table schema information retrieved using the standard methods (such as an ODBC call or native connection).

Note: To ensure that Campaign works with SQL Server synonyms, you must set the UseSQLToRetrieveSchema property to TRUE in addition to setting this property as described here.

If you set this property with a valid SQL query, HCL Campaign issues the SQL query to retrieve the list of tables for mapping. If the query returns one column, it is treated as a column of names; if the query returns two columns, the first column is assumed to be a column of owner names, and the second column is considered to be a column of table names.

If the SQL query does not begin with an asterisk (*), HCL Campaign merges this list with the list of tables that are normally retrieved (such as through ODBC calls or native connections).

If the SQL query begins with an asterisk (*), the list returned by the SQL replaces the normal list, rather than being merged with it.

Default value

None

Valid Values

A valid SQL query

Example

If the data source is SQL Server, under normal circumstances the ODBC API call that HCL Campaign uses returns a list of tables and views, but no synonyms. To include the list of synonyms as well, set TableListSQL similar to the following example:

select B.name AS oName, A.name AS tName 
from sys.synonyms A LEFT OUTER JOIN sys.schemas B
on A.schema_id = B.schema_id ORDER BY 1, 2

To retrieve the list of tables, views, and synonyms, avoiding the ODBC API completely, set TableListSQL similar to the following example:

*select B.name AS oName, A.name AS tName from
 (select name, schema_id from sys.synonyms UNION 
select name, schema_id from sys.tables UNION select name,
schema_id from sys.views) A LEFT OUTER JOIN sys.schemas B on
A.schema_id = B.schema_id ORDER BY 1, 2 

If the data source is Oracle, you can use a query similar to the following to retrieve the list of tables, views, and synonyms in place of the data retrieved using the native connection method that looks at the ALL_OBJECTS view:

*select OWNER, TABLE_NAME from (select OWNER, TABLE_NAME 
from ALL_TABLES UNION select OWNER, SYNONYM_NAME AS TABLE_NAME 
FROM ALL_SYNONYMS UNION select OWNER,
VIEW_NAME AS TABLE_NAME from ALL_VIEWS) A ORDER BY 1, 2 

TempTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to specify a complete SQL statement that HCL Campaign runs immediately after the creation of a temporary table in a user data source or in the system tables database. For example, to improve performance, you can create an index on a temporary table immediately after its creation (see examples below). To enable the creation of temporary tables in a data source, the AllowTempTables property must be set to TRUE.

You can use tokens to substitute the table name (<TABLENAME>) and column names (<KEYCOLUMNS>) in the SQL statement, because the values are generated dynamically when the campaign runs.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. You can enclose the string in quotation marks, but this is not required.

This property treats semicolons as delimiters to run multiple SQL statements. If your SQL statement contains semicolons and you want it to run as one statement, use a backslash as an escape character before the semicolons.

Note: If you are using stored procedures with this property, be sure that you use the correct syntax for your database.

Tokens available to TempTablePostExecutionSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<KEYCOLUMNS>	This token is replaced with the temp table column name(s).
<TABLENAME>	This token is replaced with the temp table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

Examples

The following value creates an index on the temp table just after its creation, to improve the data retrieval process: CREATE INDEX IND_<TABLENAME> ON <TABLENAME> (<KEYCOLUMNS>)

The following example for Oracle calls a stored procedure and uses backslashes to escape the semicolon: begin dbms_stats.collect_table_stats()\; end\;

TempTablePrefix

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies a string that is automatically prepended to the names of all temporary tables created by Campaign. Use this property to help identify and manage temp tables. You also can use this property to cause temp tables to be created in a particular location.

For example, if the user token corresponds to a schema, you can set

TempTablePrefix="<USER>"

and all temp tables will be created in the schema of whatever user is connected to the data source.

If two or more data sources point to the same database, errors and incorrect search results can occur during flowchart runs due to usage of the same temporary tables by different process boxes and flowcharts. This situation can also occur with Extract process tables and Strategic Segment tables. To avoid this situation, use TempTablePrefix (or ExtractTablePrefix for Extract tables) to define different schemas for each data source. This approach ensures that the initial part of the name is different, so the table names will always be different.

For example, give each data source a unique TempTablePrefix such as UAC_DS1 and UAC_DS2 to distinguish between temp tables for each data source. The same concept applies if you are sharing data source schemas. For example, the following prefixes allow the temp tables to be unique for both data sources that write temp tables to the same database:

DS1 TempTablePreFix: schemaA.UAC_DS1

DS2 TempTablePreFix: schemaA.UAC_DS2

The following table describes the tokens that are available to TempTablePrefix.

Note: You must make sure that the final temp table name after resolving tokens does not exceed any database-specific name length restrictions.

Note: In tokens used for TempTablePrefix, any characters that are not valid for database table names will be stripped. After tokens are resolved, the resulting temp table prefixes must start with an alphabetic character, and must contain only alphanumeric characters or underscore characters. Illegal characters will be removed silently. If any resulting temp table prefix does not begin with an alphabetic character, Campaign prepends the letter "U" to the prefix.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

UAC

TempTablePreTruncateExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Note: This property is supported only for Teradata data sources. For all other supported databases, this property should not be set.

Use this property to specify a SQL query to run before a temp table is truncated. The query that you specify can be used to negate the effect of a SQL statement specified in the TempTablePostExecutionSQL property.

For example, with the TempTablePostExecutionSQL property, you could specify the following SQL statement to create an index:

CREATE INDEX <TABLENAME>Idx_1 (<KEYCOLUMNS>) ON <TABLENAME>

Then, specify the following query in the TempTablePreTruncateExecutionSQL property to drop the index:

DROP INDEX <TABLENAME>Idx_1 ON <TABLENAME>

Default value

Not defined

Valid Values

A valid SQL query

TempTablePreTruncateRunScript

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Note: This property is supported only for Teradata data sources. For all other supported databases, this property should not be set.

Use this property to specify a script or executable to run before a temp table is truncated. The script that you specify can be used to negate the effect of a SQL statement specified in the PostTempTableCreateRunScript property.

For example, with the PostTempTableCreateRunScript property, you could specify a script that includes the following SQL statement to create an index:

CREATE INDEX <TABLENAME>Idx_1 (<KEYCOLUMNS>) ON <TABLENAME>

Then, specify another script with the following statement in the TempTablePreTruncateRunScript property to drop the index:

DROP INDEX <TABLENAME>Idx_1 ON <TABLENAME>

Default value

Not defined

Valid Values

File name of a shell script or executable

TeradataDeleteBeforeDrop

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property applies only to Teradata data sources. It specifies whether records are deleted before a table is dropped.

Set the value to TRUE to delete all records from a table before dropping the table.

Note: If HCL Campaign is unable to delete the records for any reason, it will not drop the table.

Set the value to FALSE to drop a table without first deleting all records.

Default value

TRUE

TruncateSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property is available for use with DB2 data sources, and allows you to specify alternate SQL for table truncation. This property applies only when DeleteAsTruncate is TRUE. When DeleteAsTruncate is TRUE, any custom SQL in this property is used to truncate a table. When this property is not set, HCL Campaign uses the TRUNCATE TABLE <TABLENAME> syntax.

This property is undefined by default.

Tokens available to TruncateSQL are described below.

Token	Description
<TABLENAME>	This token is replaced with the database table name that HCL Campaign is truncating.

Default value

No default value defined.

Type

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property specifies the database type of this data source.

Default value

The default value depends on the database template used to create the data source configuration.

Valid Values

Valid values for system tables are:

• SQLServer
• DB2
• DB2ODBC
• ORACLE
• ORACLE8
• ORACLE9

Valid values for customer tables also include:

• TERADATA
• NETEZZA

UOSQLOnConnect

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The SQLOnConnect property defines a complete SQL statement that Campaign runs immediately after each database connection. The UOSQLOnConnect property is similar to this, but specifically applicable to Contact Optimization.

The SQL statement generated by this property is automatically passed to your database without checking its syntax. If you use this property, make sure that it is a legal expression. The string may be enclosed in quotation marks, but this is not required.

This property is undefined by default.

Tokens available to UOSQLOnConnect are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which temp tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which temp tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which temp tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the temp tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the temp table creation.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

UseExceptForMerge

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

When HCL Campaign performs exclusions in the Merge process or in the Segment process, by default it uses NOT EXISTS syntax, as:

SELECT IncludeTable.ID FROM IncludeTable WHERE NOT EXISTS 
(SELECT * FROM ExcludeTable WHERE IncludeTable.ID = ExcludeTable.ID)

If UseExceptForMerge is TRUE and you cannot use NOT IN (because UseNotInForMerge is disabled or because the audience level consists of multiple fields and the data source is not Oracle), then the syntax is altered as follows:

Oracle

SELECT IncludeTable.ID FROM IncludeTable 
MINUS (SELECT ExcludeTable.ID FROM ExcludeTable)

Others

SELECT IncludeTable.ID FROM IncludeTable 
EXCEPT (SELECT ExcludeTable.ID FROM ExcludeTable)

Default value

FALSE

Valid Values

TRUE | FALSE

UseMergeForTrack

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property implements SQL MERGE syntax to improve the performance of the Track process in flowcharts. This property can be set to TRUE for DB2, Oracle, SQL Server 2008, and Teradata 12. It can also be used with other databases that support the SQL MERGE statement.

Default value

TRUE (DB2 and Oracle) | FALSE (all others)

Valid Values

TRUE | FALSE

UseNonANSIJoin

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

The UseNonANSIJoin property specifies whether this data source uses non-ANSI join syntax. If the data source type is set to Oracle7 or Oracle8, and the value of UseNonANSIJoin is set to TRUE, the data source uses non-ANSI join syntax appropriate for Oracle.

Default value

FALSE

Valid Values

TRUE | FALSE

UseNotInForMerge

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

When HCL Campaign performs exclusions in the Merge process or in the Segment process, by default it uses NOT EXISTS syntax, as:

SELECT IncludeTable.ID FROM IncludeTable WHERE NOT EXISTS (SELECT * 
FROM ExcludeTable WHERE IncludeTable.ID = ExcludeTable.ID)

If UseNotInForMerge is enabled and either (1) the audience level is composed of a single ID field, or (2) the data source is Oracle, then the syntax is altered as follows:

SELECT IncludeTable.ID FROM IncludeTable WHERE IncludeTable.ID NOT IN 
(SELECT ExcludeTable.ID FROM ExcludeTable)

Default value

FALSE

Valid Values

TRUE | FALSE

UseNotInToDeleteCH

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property affects the HCL Campaign system table data source (UA_SYSTEM_TABLES). It affects the SQL query syntax for how the MailList and CallList processes remove records from the HCL Campaign system tables.

The default value of FALSE typically improves database performance. The default behavior uses EXISTS / NOT EXISTS when removing Contact History records (either after a failed run or in response to the user's action in the GUI). The removal process involves deleting from UA_OfferHistAttrib and updating UA_OfferHistory.

You can change this value to TRUE if you prefer to use the SQL syntax of IN / NOT IN. Prior versions of HCL Campaign used IN / NOT IN.

Default value

FALSE

Valid Values

TRUE | FALSE

UserBaseTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property is invoked when a process box is configured to write to a New Mapped Table > Base Record Table > Create New Table in Selected Database. This property is invoked only when the table is created (during the creation and mapping process). This property is not invoked during process box runtime.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. You can enclose the string in quotation marks, but this is not required.

This property treats semicolons as delimiters to run multiple SQL statements. If your SQL statement contains semicolons and you want it to run as one statement, use a backslash as an escape character before the semicolons.

Note: If you are using stored procedures with this property, be sure to use the correct syntax for your database. The following example for Oracle calls a stored procedure and uses backslashes to escape the semicolon: begin dbms_stats.collect_table_stats()\; end\;

You can use tokens to substitute the <TABLENAME> in this SQL statement because the name is generated dynamically when the campaign runs. For available tokens, see UserTablePostExecutionSQL.

UserTablePostExecutionSQL

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Use this property to specify a complete SQL statement that IBM Campaign runs immediately after the creation of a user table in a user data source or in the system tables database. This property is invoked when a process box writes to one of the following tables:

• New Mapped Table > General Table > Create New Table in Selected Datasource: The property is invoked during the creation/mapping process; not during Snapshot runtime.
• New Mapped Table > Dimension Table > Create New Table in Selected Database: The property is invoked during the creation/mapping process; not during Snapshot runtime.
• Database Table: The property is invoked during process box runtime.

This property is automatically added to the SQL expression without checking its syntax. If you use this property, make sure that it is a legal expression. You can enclose the string in quotation marks, but this is not required.

This property treats semicolons as delimiters to run multiple SQL statements. If your SQL statement contains semicolons and you want it to run as one statement, use a backslash as an escape character before the semicolons.

Note: If you are using stored procedures with this property, be sure to use the correct syntax for your database. The following example for Oracle calls a stored procedure and uses backslashes to escape the semicolon: begin dbms_stats.collect_table_stats()\; end\;

You can use tokens to substitute the <TABLENAME> in this SQL statement, because the name is generated dynamically when the campaign runs.

Tokens available to UserTablePostExecutionSQL are described below.

Token	Description
<AMUSER>	This token is replaced with the IBM EMM user name associated with the flowchart for which the user tables were created.
<CAMPAIGNCODE>	This token is replaced with the code for the campaign associated with the flowchart for which the user tables were created.
<CAMPAIGNNAME>	This token is replaced with the name of the campaign associated with the flowchart for which the user tables were created.
<DBUSER>	This token is replaced with the database user name for the database where the user tables were created.
<FLOWCHARTNAME>	This token is replaced with the name of the flowchart associated with the user table creation.
<TABLENAME>	This token is replaced with the user table name.
<USER>	This token is replaced with the Campaign user name of the user running the flowchart.

Default value

No default value defined.

UseSQLToProfile

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

This property allows you to configure HCL Campaign to submit the SQL query GROUP BY to the database to compute profiles (using SELECT field, count(*) FROM table GROUP BY field), rather than fetching records.

• A value of FALSE (the default) causes HCL Campaign to profile a field by retrieving the field value for all records in the table and to track the count of each distinct value.
• A value of TRUE causes HCL Campaign to profile a field by issuing a query similar to the following:

SELECT field, COUNT(*) FROM table GROUP BY field
		

which pushes the burden to the database.

Default value

FALSE

Valid Values

TRUE | FALSE

UseSQLToRetrieveSchema

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

Uses a SQL query, rather than an ODBC or native API call, to retrieve the schema to use as the table schema for this data source.

The default value for this property is FALSE, indicating that Campaign should use its standard method (ODBC or native connection, for example) to retrieve the schema. Setting this property to TRUE causes Campaign to prepare a SQL query similar to select * from <table> to retrieve the table schema.

This can provide advantages that are specific to each data source. For example, some data sources (Netezza, SQL Server) do not properly report SQL synonyms (alternative names for database objects, defined using the create synonym syntax) through the default ODBC or native connections. By setting this property to TRUE, SQL synonyms are retrieved for data mapping within Campaign.

The following list describes the behavior of this setting for a number of data sources:

• For Netezza, you must set this property to TRUE to allow support for synonyms. Setting this property to TRUE tells Campaign to prepare a SQL query to retrieve the table schema. No other settings or values are needed to support synonyms in Netezza data sources.
• For SQL Server, to allow support for synonyms you must set this property to TRUE and enter valid SQL in the TableListSQL property for this data source. See the description for the TableListSQL property for more details.
• For Oracle data sources, setting this property to TRUE tells Campaign to prepare the SQL query to retrieve the table schema. The result set identifies NUMBER fields (no precision/scale specified, which may cause issues in Campaign) as NUMBER(38), which avoids those possible issues.
• For other data sources, you can optionally set this property to TRUE to use the default SQL select query described above, or to specify valid SQL in the TableListSQL property to use instead of, or in addition to, the ODBC API or native connection that is used by default. See the description for the TableListSQL property for more details.

Default value

FALSE

Valid Values

TRUE | FALSE

Example

To allow Campaign to work with Netezza or SQL Server synonyms:

UseSQLToRetrieveSchema=TRUE

UseTempTablePool

Configuration category

Campaign|partitions|partition[n]|dataSources|dataSourcename

Description

WhenUseTempTablePool is set to FALSE, temp tables are dropped and re-created every time a flowchart is run. When the property is set to TRUE, temp tables are not dropped from the database. Temp tables are truncated and reused from the pool of tables maintained by Campaign. The temp table pool is most effective in environments where you rerun flowcharts many times, such as during a design and test phase.

Default value

FALSE

Valid Values

TRUE | FALSE