The properties in Campaign | partitions | partition[n] | dataSources determine how
IBM® Campaign interacts with databases, including its own system tables, for the specified partition.
These properties specify the databases that IBM® Campaign can access and they control many aspects of how queries are formed.
Each data source that you add in IBM® Campaign is represented by a category under
partition >
partition [n] >
dataSources >
<data-source-name>.
|
The IBM® Campaign system tables data source for each partition must be named UA_SYSTEM_TABLES in IBM® Marketing Platform, and every IBM® Campaign partition must have a dataSources > UA_SYSTEM_TABLES category on the Configuration page.
|
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:
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
|
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.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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).
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
The BulkReaderBlockSize property defines the size of a data block, in number of records, that
Campaign reads from the database at a time.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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')
Campaign|partitions|partition[n]|dataSources|dataSourcename
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".
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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 DB2NotLoggedInitiallyUserTables property is not used for the IBM® Campaign system tables.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
|
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
The DeleteAsTruncate property specifies whether, when an output process is configured to
REPLACE TABLE,
Campaign uses
TRUNCATE TABLE or deletes from the table.
|
TRUE for Netezza®, Oracle, and SQLServer.
|
|
FALSE for other database types.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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
|
You want the Campaign application server to perform de-duplication to reduce resource consumption/burden on the database.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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>
|
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.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
UAC_*, which excludes temp tables and Extract tables, when the
ExtractTablePrefix property's value is the default value
Campaign|partitions|partition[n]|dataSources|dataSourcename
Use the ExtractTablePostExecutionSQL property to specify one or more complete SQL statements that run immediately after the creation and population of an Extract table.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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)
Campaign|partitions|partition[n]|dataSources|dataSourcename
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).
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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 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>).
|
|
|
|
|
|
|
|
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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 TRUE to specify that the decimal point symbol appropriate to the locale is used.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
|
If maxReuseThreads is set to a non-zero value, it should be greater than or equal to the value of MaxQueryThreads.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
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
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
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
|
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.
|
|
Disabled: IBM® Campaign uses ANSI ODBC calls.
|
|
UTF-8: IBM® Campaign uses Unicode ODBC calls and assumes that a SQLWCHAR is a single byte. This is compatible with DataDirect ODBC drivers.
|
|
UCS-2: IBM® Campaign uses Unicode ODBC calls and assumes that a SQLWCHAR is 2 bytes. This is compatible with Windows™ and unixODBC ODBC drivers.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Use the PostSnapshotTableCreateRunScript property to specify a script or executable that
Campaign runs after a Snapshot table has been created and populated.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
Use the PrefixOnSelectSQL property to specify a string that is automatically prepended to all
SELECT SQL expressions generated by
Campaign.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
The ShareConnection property is no longer used and should remain set to its default value,
FALSE.
Campaign|partitions|partition[n]|dataSources|dataSourcename
The SQLOnConnect property defines a complete SQL statement that
Campaign runs immediately after each database connection.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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 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.
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:
|
On Windows™: Add the following line to the Campaign Listener startup script ( <CAMPAIGN_HOME>\bin\cmpServer.bat):
|
|
On UNIX™: After DB2® is started, the system administrator must type the following command from the DB2® instance user:
|
StringEncoding=CP932
CharacterSet=UTF8
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
TRUNCATE TABLE table
DROP TABLE table
DELETE FROM table [WHERE ...]
UPDATE table SET ...
Campaign|partitions|partition[n]|dataSources|dataSourcename
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'
Campaign|partitions|partition[n]|dataSources|dataSourcename
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 ...
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.)
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
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
*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
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
|
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.
|
To allow Campaign to work with
Netezza® or SQL Server synonyms:
UseSQLToRetrieveSchema=TRUE
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
|
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.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Use the SegmentTablePostExecutionSQL property to specify a complete SQL statement that
Campaign runs after a Segment temp table has been created and populated.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
TempTablePrefix="<USER>"
Campaign|partitions|partition[n]|dataSources|dataSourcename
For example, with the TempTablePostExecutionSQL property, you could specify the following SQL statement to create an index:
Campaign|partitions|partition[n]|dataSources|dataSourcename
For example, with the PostTempTableCreateRunScript property, you could specify a script that includes the following SQL statement to create an index:
Campaign|partitions|partition[n]|dataSources|dataSourcename
Set the value to TRUE to delete all records from a table before dropping the table.
|
If IBM® Campaign is unable to delete the records for any reason, it will not drop the table.
|
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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)
Campaign|partitions|partition[n]|dataSources|dataSourcename
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
Campaign|partitions|partition[n]|dataSources|dataSourcename
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)
Campaign|partitions|partition[n]|dataSources|dataSourcename
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.
|
A value of FALSE (the default) causes IBM® 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 IBM® Campaign to profile a field by issuing a query similar to the following:
|
SELECT field, COUNT(*) FROM table GROUP BY field
Copyright IBM Corporation 2015. All Rights Reserved.
|