IBM® 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 IBM® Campaign environment. For example, 64-bit IBM® 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 IBM® Campaign to access, for example, Teradata).

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

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

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

This property determines whether IBM® 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.

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

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

This property specifies whether IBM® 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, IBM® Campaign can use that temporary table to retrieve rows from the database.

A number of IBM® 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 IBM® 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.

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, IBM® 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.

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

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

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.

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

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.

} (closing curly brace)

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.

{ (opening curly brace)

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.

0 (zero)

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.

		CREATE TABLE date_test (f1 DATE);
		INSERT INTO date_test values (current_date);
		SELECT f1 FROM date_test;

		SELECT current_date FROM	ANY_TABLE limit 1;

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:

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.

Any of the formats that are specified in the DATE macro

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.

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

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

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.

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

When set to TRUE, this property disables logging for inserts in to 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.

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

When set to TRUE, this property 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.

The DefaultScale property is used when Campaign creates a database field to store numeric values from a flat file or 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:

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.

0 (zero)

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.

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.

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.

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.

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.

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.

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.

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:

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

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>

The ExcludeFromTableDisplay parameter allows you to limit the database tables that are displayed during table mapping in Campaign. It does not reduce the number of table names retrieved from the database.

For example, if you set the value of this parameter to sys.*, tables with names that begin with sys. are not displayed. Note that the values for this parameter are case-sensitive.

UAC_*, which excludes temp tables and Extract tables, when the ExtractTablePrefix property's value is the default value

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.

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.

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.

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.

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.

100000 (one hundred thousand records)

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

This property specifies the command issued to invoke your database load utility in IBM® Campaign. If you set this property, IBM® 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 IBM® Campaign uploads ID lists into temp tables.

IBM® Contact Optimization uses the same configuration settings as Campaign for implementing a database load utility. If you configure IBM® Campaign to work with a database load utility, you configure IBM® Contact Optimization to use the same commands. Similarly, if you configure Contact Optimization to work with a database load utility, you are configuring IBM® Campaign to work with a database load utility. Each assumes a different root directory in your IBM® EMM installation directory. The root directory is /Campaign for IBM® Campaign or /ContactOptimization for IBM® Contact Optimization, so you can specify different commands in the loader command and template files.

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. IBM® 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.

Tokens available to LoaderCommand are described below.

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

Tokens available to LoaderCommandForAppend are described below.

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

When this property is set, IBM® 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 IBM® 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 IBM® Campaign.

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.

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

When this property is set, IBM® 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 IBM® 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 specifies whether the temporary data file is a fixed-width or delimited flat file, and, if it is delimited, the characters that IBM® Campaign uses as delimiters.

If the value is undefined, IBM® 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. IBM® Campaign creates the temporary data file as a delimited flat file, using the value of this property as the delimiter.

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, IBM® 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.

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, IBM® 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; IBM® 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.

This property specifies whether the temporary IBM® 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, IBM® 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. IBM® Campaign creates the temporary data file as a delimited flat file, using the value of this property as the delimiter.

This property specifies, when IBM® 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.

Allows you to specify the maximum number of items that IBM® 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).

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

IBM® Campaign runs database queries using independent threads. Because IBM® 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 IBM® Campaign server limits the number of simultaneous queries to the specified value.

When the selected number of IDs is less than the value specified by the MaxRowFetchRecords property, IBM® 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, IBM® 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.

When a query is issued, IBM® 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.

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.

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

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, IBM® 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

SELECT CustID, Accnt_balance FROM Customer

When a query is issued, IBM® 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.

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.

Use this property to set the threshold for using the bulk loader. IBM® 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, IBM® 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.

Use this property to set the threshold for using the bulk loader. IBM® 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, IBM® 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.

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

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

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

The default value of FALSE allows IBM® Campaign to use the v3 API specification, while a setting of TRUE causes IBM® 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, IBM® Campaign does not support the ODBC Unicode API, and values other than disabled for the ODBCUnicode property are not recognized.

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

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

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

Tokens available to PostExtractTableCreateRunScript are described below.

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

Tokens available to PostSegmentTableCreateRunScript are described below.

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.

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.

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

Tokens available to PostUserTableCreateRunScript are described below.

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.

Tokens available to PrefixOnSelectSQL are described below.

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.

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.

1000000 (one million records)

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.

The ShareConnection property is no longer used and should remain set to its default value, FALSE.

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

Tokens available to SQLOnConnect are described below.

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.

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.

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

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

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:

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

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

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.

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.

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

Tokens available to SuffixOnAllOtherSQL are described below.

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

SuffixOnCreateDateField = FORMAT 'YYYY-MM-DD'

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

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

Tokens available to SuffixOnInsertSQL are described below.

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.

Tokens available to SuffixOnSelectSQL are described below.

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.

Tokens available to SuffixOnTempTableCreation are described below.

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.

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.

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.

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.

Tokens available to SuffixOnUserBaseTableCreation are described below.

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.

Tokens available to SuffixOnUserTableCreation are described below.

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.

Use this property to specify a complete SQL statement that IBM® 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.

Tokens available to TempTablePostExecutionSQL are described below.

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

If you set this property with a valid SQL query, IBM® 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 (*), IBM® 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.

If the data source is SQL Server, under normal circumstances the ODBC API call that IBM® 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 

*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 

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.

Tokens available to UOSQLOnConnect are described below.

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.

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

UseSQLToRetrieveSchema=TRUE

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.

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.

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:

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.

Tokens available to UserTablePostExecutionSQL are described below.

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.

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.

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.

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.

TempTablePrefix="<USER>"

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.

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

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:

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

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:

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

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

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

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, IBM® Campaign uses the TRUNCATE TABLE <TABLENAME> syntax.

Tokens available to TruncateSQL are described below.

When IBM® 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:

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

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

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.

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.

When IBM® 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)

This property allows you to configure IBM® 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.

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