Each data source that you add in Campaign is represented by a category under partition[n] > dataSources > [DATA_SOURCE_NAME].
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 Campaign environment. For example, 64-bit Campaign provides two ODBC access libraries: one appropriate for ODBC data sources compatible with the unixODBC implementation (libodb4d.so — used by Campaign to access, for example, Informix), and the other compatible with the DataDirect implementation (libodb4dDD.so — used by Campaign to access, for example, Teradata).
If you determine that the default access library should be overridden (for example, if you purchase a DataDirect driver manager and driver for Informix), set this parameter as required (in the example, to libodb4dDD.so, overriding the default selection of libodb4d.so).
This property is empty by default.
The ASMSaveDBAuthentication property specifies whether, when you log in to Campaign and map a table in a data source you have not previously logged in to, Campaign saves your user name and password in IBM Unica Marketing.
If you set this property to TRUE, Campaign does not prompt you for a user name and password when you subsequently 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 Unica Marketing user name assigned to the Campaign system user (required for accessing the Campaign system tables).
This property is undefined by default.
The AliasPrefix property specifies the way Campaign forms the alias name that Campaign creates automatically when using a dimension table and writing to a new table.
The AllowSegmentUsingSQLCase property specifies whether the Campaign Segment process consolidates multiple SQL statements into a single SQL statement, when specific configuration conditions are met.
Setting this property to TRUE results in significant performance improvements when all of the following conditions are met:
In this case, Campaign generates a single SQL CASE statement to perform segmentation, followed by segment-by-field processing on the Campaign application server.
The AllowTempTables property specifies whether 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.
When the UseTempTablePool property is set to TRUE, temp tables will not be dropped from the database. Temp tables will be truncated and reused from the pool of tables maintained by Campaign. When set to FALSE, temp tables are dropped and re-created every time a flowchart is run.
Use the TempTablePreTruncateRunScript property to specify a script or executable that will be run before a temp table is truncated. The script that you specify can be used to negate the effect of a SQL statement specified in the PostTempTableCreateRunScript property.
For example, with the PostTempTableCreateRunScript property, you could specify a script that includes the following SQL statement to create an index:
CREATE INDEX <TABLENAME>Idx_1 (<KEYCOLUMNS>) ON <TABLENAME>
Then, specify another script with the following statement in the TempTablePreTruncateRunScript property to drop the index:
DROP INDEX <TABLENAME>Idx_1 ON <TABLENAME>
File name of a shell script or executable
Use the TempTablePreTruncateExecutionSQL property to specify a SQL query that will be run before a temp table is truncated. The query that you specify can be used to negate the effect of a SQL statement specified in the TempTablePostExecutionSQL property.
For example, with the TempTablePostExecutionSQL property, you could specify the following SQL statement to create an index:
CREATE INDEX <TABLENAME>Idx_1 (<KEYCOLUMNS>) ON <TABLENAME>
Then, specify the following query in the TempTablePreTruncateExecutionSQL property to drop the index:
DROP INDEX <TABLENAME>Idx_1 ON <TABLENAME>
The BulkInsertBlockSize 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 using 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 used to indicate the end 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.
} (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, once a process is done with a connection, Campaign keeps up to the specified number of connections open for an amount of time specified by the InactiveConnectionTimeout property. After this time expires, the connections are removed from the cache and closed.
0 (zero)
Use the DSN property to identify the physical data source for the Campaign data source you are defining. This value is undefined by default.
You can specify multiple logical data sources that refer to the same physical data source. For example, you can create two Campaign data sources, one with AllowTempTables = TRUE and the other with AllowTempTables = FALSE. Each of these data sources would have a different data source name in Campaign, but if they refer to the same physical data source they will have the same DSN value.
The DSNUsingOSAuthentication property applies only when an Campaign data source is SQL Server. Set the the value to TRUE when the DSN is configured to use Windows Authentication mode.
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 your database uses to display dates on select. For most databases, this setting is the same as the setting for the DateOutputFormatString property.
Note the following database-specific instructions.
Teradata allows you to define the date format on a per-column basis. In addition to dateFormat and dateOutputFormatString, you need to set SuffixOnCreateDateField. To be consistent with our system table settings, use:
|
SuffixOnCreateDateField = FORMAT 'YYYY-MM-DD'
|
|
|
DateFormat = DELIM_Y_M_D
|
|
|
DateOutputFormatString = %Y-%m-%d
|
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 unchecked so that the date format configuration does not change for each language.
Any of the formats 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 specified for format_strin the DATE_FORMAT macro. Note that 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.
|
2.
|
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 should 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".
Only delimited formats are supported, as follows:
The DateTimeOutputFormatString property specifies the format of the datetime datatype to be used when Campaign writes any datetime, such as a campaign start or end date and time, to a database.Set the value of the DateTimeOutputFormatString property to the format that the data source expects for columns of the type datetime. For most databases, this setting is the same as the setting for the [data_source_name] > DateTimeFormat property.
See DateOutputFormatString for a method for verifying that the format you select is correct.
The DB2NotLoggedInitially property determines whether Campaign uses the not logged initially SQL syntax when populating temporary tables in DB2. When set to TRUE, this property disables logging for inserts into temp tables, which improves performance and decreases database resource consumption.
If your version of DB2 does not support the not logged initially syntax, set this property to FALSE.
The DB2NotLoggedInitiallyUserTables property determines whether 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 Campaign system tables.
|
The DefaultScale property determines floating point precision when a Campaign Snapshot process exports numeric fields to a table. When the value is 0 (zero) Campaign preserves the floating point precision in the field being exported.
You can limit the floating point precision by changing this property. For example, if you set the value at 3, floating point precision would be three places (0.NNN).
0 (zero)
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 executes a TRUNCATE TABLE from the table.
When the value is FALSE, Campaign executes a DELETE FROM from the table.
The default value depends on the database type.
|
|
TRUE for Netezza, Oracle, Sybase and SQLServer.
|
|
|
FALSE for other database types.
|
The DisallowTempTableDirectCreate 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.
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 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):
When the value is FALSE, the Campaign server performs de-duplication, and SQL queries generated against the database have the form:
You should leave the default value of FALSE if:
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:
When the value is FALSE, the Campaign server performs the sorting, and SQL queries generated against the database have the form:
|
|
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.
|
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.
Table names matching the specified patterns are not displayed.
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
The ExtractTablePrefix property specifies a string that is automatically prepended to all Extract table names in Campaign.
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. Note that 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).
The LoaderCommand property specifies the command issued to invoke your database load utility in Campaign. If you set this parameter, 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 parameter also invokes the database loader utility mode when Campaign uploads ID lists into temp tables.
This parameter is undefined by default.
Tokens available to LoaderCommand are described in the following table:
|
This token is replaced with the code for the campaign associated with the flowchart. |
|
|
This token is replaced with the name of the campaign associated with the flowchart being run. |
|
|
This token is replaced with the full path and file name to the temporary control file that Campaign generates according to the template that is specified in the LoaderControlFileTemplate parameter. |
|
|
This token is replaced with the database user name for the database. |
|
|
This token is replaced with the value of the DSN property. If the DSN property is not set, the <DSN> token is replaced by the data source name used in the category name for this data source (the same value used to replace the <DATABASE> token). |
|
|
This token is replaced with the name of the flowchart being run. |
|
|
This token is replaced with the number of fields in the table. |
|
|
This token is obsolete, but is supported for backward compatibility. See <TABLENAME>, which replaced <TABLE> as of version 4.6.3. |
|
The LoaderCommandForAppend parameter specifies the command issued to invoke your database load utility for appending records to a database table in Campaign. If you set this parameter, Campaign enters database loader utility mode for all output files from the Snapshot process that are used with the “append records” settings.
This parameter is undefined by default.
Tokens available to LoaderCommandForAppend are described in the following table:
|
This token is replaced with the code for the campaign associated with the flowchart being run. |
|
|
This token is replaced with the name of the campaign associated with the flowchart being run. |
|
|
This token is replaced with the full path and file name to the temporary control file that Campaign generates according to the template that is specified in the LoaderControlFileTemplate parameter. |
|
|
This token is replaced with the value of the DSN property. If the DSN property is not set, the <DSN> token is replaced by the data source name used in the category name for this data source (the same value used to replace the <DATABASE> token). |
|
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the number of fields in the table. |
|
|
This token is obsolete, but is supported for backward compatibility. See <TABLENAME>, which replaced <TABLE> as of version 4.6.3. |
|
The LoaderControlFileTemplate property specifies the full path and file name to the control file template that has been previously configured in Campaign. When this parameter is set, Campaign dynamically builds a temporary control file based on the template that you specify here. The path and name of this temporary control file is available to the <CONTROLFILE> token that is available to the LoaderCommand parameter.
This parameter is undefined by default.
Tokens available to LoaderControlFileTemplate are the same as those described for the LoaderCommand property, plus the following special tokens, which are repeated once for each field in the outbound table.
The LoaderControlFileTemplateForAppend property specifies the full path and file name to the control file template that has been previously configured in Campaign. When this parameter is set, Campaign dynamically builds a temporary control file based on the template that is specified here. The path and name of this temporary control file is available to the <CONTROLFILE> token that is available to the LoaderCommandForAppend property.
See your database loader utility documentation for the correct syntax required for your control file. Tokens available to your control file template are the same as those for the LoaderControlFileTemplate property.
This parameter is undefined by default.
The LoaderDelimiter property specifies whether the temporary data file is a fixed-width or delimited flat file, and, if it is delimited, the characters Campaign uses as delimiters.
If the value is undefined, Campaign creates the temporary data file as a fixed width flat file.
This property is undefined by default.
Characters, which can be enclosed in double quotes, if desired.
Some external load utilities require that the data file be delimited and that each line end with the delimiter (for example, Informix's dbaccess using External Tables). 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, Campaign uses delimiters at the end of each line.
Some external load utilities require that the data file be delimited and that each line end with the delimiter (for example, Informix's dbaccess using External Tables). 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, Campaign uses delimiters at the end of each line.
The LoaderDelimiterForAppend property specifies whether the temporary 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, Campaign creates the temporary data file as a fixed width flat file.
This property is undefined by default.
Characters, which you may enclose in double quotes, if desired.
The LoaderUseLocaleDP property specifies, when 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.
1000 (Oracle only), 0 (unlimited) for all other databases
The MaxQueryThreads property specifies the upper limit on the number of simultaneous queries allowed to run against each database source from a single Campaign flowchart.
Varies depending on the database
When the selected number of IDs is less than the value specified by the MaxRowFetchRecords property, Campaign passes the IDs to the database, one at a time in a separate SQL query. This process may be very time-consuming. If the number of selected IDs is greater than the value specified by this parameter, Campaign uses temporary tables (if allowed against the database source), or it pulls down all the values from the table, not including any unnecessary values.
For performance reasons, it is recommended to keep this number low.
When a query is issued, 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 MaxTempTableJoinPctWithCondition 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 then or equal to the value of MaxTempTableJoinPctWithCondition 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.
When a query is issued, 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 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. Campaign invokes the script assigned to the LoaderCommand 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.
Use this property to set the threshold for using the bulk loader. 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.
The NumberOfRetries property specifies the number of times Campaign automatically retries a database operation on failure. Campaign automatically resubmits queries to the database this number of times before reporting a database error or failure.
The ODBCTableTypes property is required to support Sybase IQ/ASE data sources only. Campaign requires this property to be set in order to display the list of tables in the table mapping window. Add the following values to this property to enable mapping for Sybase IQ/ASE data sources:
'TABLE','VIEW','SYNONYM','ALIAS'
This property is empty by default, which is appropriate for data sources other than Sybase IQ/ASE.
'TABLE','VIEW','SYNONYM','ALIAS'
The ODBCUnicode property specifies the type of encoding used in 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.
|
Possible values for this property are:
|
|
Disabled - Campaign uses ANSI ODBC calls.
|
|
|
UTF-8 - Campaign uses Unicode ODBC calls and assumes a SQLWCHAR is a single byte. This is compatible with DataDirect ODBC drivers.
|
|
|
UCS-2 - Campaign uses Unicode ODBC calls and assumes a SQLWCHAR is 2 bytes. This is compatible with Windows and unixODBC ODBC drivers.
|
Use the ODBCv2 property to specify which ODBC API specification Campaign should use for the data source.
The default value of FALSE allows Campaign to use the v3 API specification, while a setting of TRUE causes 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, Campaign does not support the ODBC Unicode API, and values other than disabled for the ODBCUnicode property are not recognized.
The OwnerForTableDisplay property allows you to limit the table mapping display in Campaign to tables owned by a specified user, or to one or more sets of tables owned by the specified user(s).
Tokens available to PostSegmentTableCreateRunScript are described in the following table:
|
This token is replaced with the Segment temp table column name(s). |
File name of a script or executable
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 in the following table:
|
This token is replaced with the name of the flowchart associated with the Snapshot table creation. |
|
|
This token is replaced with the Snapshot table column name(s). |
File name of a shell script or executable
Use the PostExtractTableCreateRunScript property to specify a script or executable for Campaign to run after an Extract table has been created and populated.
Tokens available to PostExtractTableCreateRunScript are described in the following table:
|
This token is replaced with the name of the flowchart associated with the Extract table creation. |
|
|
This token is replaced with the Extract table column name(s). |
File name of a shell script or executable
When set to TRUE, the PadTextWithSpaces property causes Campaign to pad text values with spaces until the string is the same width as the database field.
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 in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
Tokens available to PostUserTableCreateRunScript are described in the following table:
|
This token is replaced with the name of the flowchart associated with the User table creation. |
|
File name of a script or executable
Use the PrefixOnSelectSQL property to specify a string that is automatically prepended to all SELECT SQL expressions generated by Campaign.
This property is automatically added to the SELECT SQL expression without checking its syntax. If you use this property, make sure it is a legal expression.
This property is undefined by default.
Tokens available to PrefixOnSelectSQL are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
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.
The SQLOnConnect property defines a complete SQL statement that Campaign executes immediately after each database connection.
This property is undefined by default.
Tokens available to SQLOnConnect are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
The SQLOnConnect property defines a complete SQL statement that Campaign executes immediately after each database connection. The UOSQLOnConnect property is similar to this, but specifically applicable to Optimize.
This property is undefined by default.
Tokens available to UOSQLOnConnect are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
The ShareConnection property controls the number of connections Campaign uses for database operations. When the value is FALSE each database operation is performed on a new connection. When the value is TRUE a single connection is used to execute all database operations.
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). Likewise, when Campaign sends a query to the database, character data is transcoded from the internal encoding of Campaign to the encoding specified in the StringEncoding property.
The value of this property must match the encoding used on the database client.
|
|
If you use the partitions > partition[n] > dataSources > [data_source_name] > ODBCUnicode property, this property is set to either UTF-8 or WIDEUTF-8, otherwise the ODBCUnicode property’s setting is ignored.
|
See Appendix C, Internationalization and Encodings for a list of supported encodings.
See the following sections for important exceptions and additional considerations.
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.
SuffixOnAllOtherSQL is used for the following expression types, when generated by Campaign:
This property is undefined by default.
Tokens available to SuffixOnAllOtherSQL are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
The SuffixOnCreateDateField property specifies a string that Campaign automatically appends to any DATE fields in the CREATE TABLE SQL statement.
For example, you might set this property as follows:
If this property is undefined (the default), the CREATE TABLE command is unchanged.
|
|
See the table in the description of the DateFormat property.
|
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:
This property is undefined by default.
Tokens available to SuffixOnInsertSQL are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
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 is does not apply to SQL in “raw SQL” expressions used in the Select process.
This property is undefined by default.
Tokens available to SuffixOnSelectSQL are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
Use the SuffixOnTempTableCreation property to specify a string that is automatically appended to the SQL expression generated by Campaign when a temp table is created. This property applies only to SQL generated by Campaign, and does not apply to SQL in “raw SQL” expressions used in the Select process. To use this property, the AllowTempTables property must be set to TRUE.
You may want to use tokens to substitute the table name and the column name(s) (<TABLENAME> and <KEYCOLUMNS>) in this SQL statement, since these are generated dynamically during the execution of the campaign.
This property is undefined by default.
Tokens available to SuffixOnTempTableCreation are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
Tokens available to SuffixOnSegmentTableCreation are described in the following table:
|
This token is replaced with the Segment temp table column name(s). |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
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 in the following table:
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 in the following table:
Use the SuffixOnUserBaseTableCreation property to specify a string that is automatically appended to the SQL expression that Campaign generates when a user creates a Base table (for example, in an Extract process). This property applies only to SQL generated by Campaign, and does not apply to SQL in “raw SQL” expressions used in the Select process.
You may want to use tokens to substitute the table name and the column name(s) (<TABLENAME> and <KEYCOLUMNS>) in this SQL statement, since these are generated dynamically during the execution of the campaign.
This property is undefined by default.
Tokens available to SuffixOnUserBaseTableCreation are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
Use the SuffixOnUserTableCreation property to specify a string that is automatically appended to the SQL expression that Campaign generates when a user creates a General table (for example, in a Snapshot process). This property applies only to SQL generated by Campaign, and does not apply to SQL in “raw SQL” expressions used in the Select process.
This property is undefined by default.
Tokens available to SuffixOnUserTableCreation are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
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.)
Use the TempTablePostExecutionSQL property to specify a complete SQL statement that Campaign executes immediately after the creation of a temporary table in a user data source or in the system tables database. The AllowTempTables property must be set to TRUE to enable the creation of temp tables in a data source.
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 TempTablePostExecutionSQL are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
Use the TableListSQL property to specify the SQL query to use to include synonyms in the list of tables available to map.
|
|
To ensure that Campaign works with SQL Server synonyms, you must set the UseSQLToRetrieveSchema property to TRUE in addition to setting this property as described here.
|
If the SQL query begins with an asterisk (*), the list returned by the SQL replaces the normal list, rather than being merged with it.
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's schema.
This can provide advantages that are specific to each data source. For example, some data sources (Netezza, SQL Server) do not properly report SQL synonyms (alternative names for database objects, defined using the create synonym syntax) through the default ODBC or native connections. By setting this property to TRUE, SQL synonyms are retrieved for data mapping within Campaign.
The following list describes the behavior of this setting for a number of data sources:
|
|
For SQL Server, to allow support for synonyms you must set this property to TRUE and enter valid SQL in the TableListSQL property for this data source.
|
|
|
For Oracle data sources, setting this property to TRUE tells Campaign to prepare the SQL query to retrieve the table's schema. The result set identifies NUMBER fields (no precision/scale specified, which may cause issues in Campaign) as NUMBER(38), which avoids those possible issues.
|
|
|
For other data sources, you can optionally set this property to TRUE and specify valid SQL in the TableListSQL property to use instead of or in addition to the ODBC API or native connection that is used by default.
|
Use the UserTablePostExecutionSQL property to specify a complete SQL statement that Campaign executes immediately after the creation of a user table in a user data source or in the system tables database.
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 in the following table:
|
This token is replaced with the name of the flowchart associated with the user table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
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 in the following table:
|
This token is replaced with the Segment temp table column name(s). |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
Use the SnapshotTablePostExecutionSQL property to specify one or more complete SQL statements that are executed immediately after a Snapshot table has been created and populated.
Tokens available to SnapshotTablePostExecutionSQL are described in the following table:
Use the ExtractTablePostExecutionSQL property to specify one or more complete SQL statements that are executed immediately after the creation and population of an Extract table.
Tokens available to ExtractTablePostExecutionSQL are described in the following table:
The TempTablePrefix parameter specifies a string that is automatically prepended to the names of all temporary tables created by Campaign. You should use this parameter to help you identify and manage your temp tables. You also can use this property to cause temp tables to be created in a particular location.
For example, if the user token corresponds to a schema, you can set
and all temp tables will be created in the schema of whatever user is connected to the data source.
Tokens available to TempTablePrefix are described in the following table:
|
This token is replaced with the name of the flowchart associated with the temp table creation. |
|
|
This token is replaced with the Campaign user name of the user running the flowchart. |
The TeradataDeleteBeforeDrop parameter applies only to Teradata data sources. It specifies whether records are deleted prior to a table being dropped.
Set this value to TRUE to delete all records from a table prior to dropping the table.
Set this value to FALSE to drop a table without first deleting all records.
The TruncateSQL 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 set to TRUE. When DeleteAsTruncate is set to TRUE, any custom SQL in this property is used to truncate a table. When this property is not set, Campaign uses the TRUNCATE TABLE <TABLENAME> syntax.
This parameter is undefined by default.
Tokens available to TruncateSQL are described in the following table:
|
This token is replaced with the database table name that Campaign is truncating. |
The partitions > partition[n] > dataSources > [data_source_name] > type property specifies the database type of this data source.
Valid values for system tables are:
Valid values for customer tables also include:
The default value depends on the database template used to create the data source configuration.
If UseExceptForMerge is enabled (value set to YES) and we cannot use "NOT IN" (because UseNotInForMerge is disabled, or because the audience level is comprised of multiple fields and the data source is not Oracle), then the syntax will be altered as:
Implements SQL MERGE syntax to improve the performance of the Track process. The UseMergeForTrack 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.
TRUE (DB2 and Oracle) | FALSE (all others)
The UseNonANSIJoin property specifies whether this data source uses non-ANSI join syntax (for example, for Oracle versions earlier than 8, and Informix8).
|
|
If the data source type is set to Informix8, the value of UseNonANSIJoin is ignored, and non-ANSI join syntax appropriate for Informix8 is always used.
|
|
|
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.
|
|
|
If the data source type is set to Sybase, and the value of UseNonANSIJoin is set to TRUE, the data source uses non-ANSI join syntax appropriate for Sybase.
|
If UseNotInForMerge is enabled (value set to YES), and either (1) the audience level is comprised of a single ID field, or (2) the data source is Oracle, then the syntax will be altered as:
The UseSQLToProfile property allows you to configure 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 Campaign to profile a field by retrieving the field's value for all records in the table and to track the count of each distinct value.
|
|
|
A value of TRUE causes Campaign to profile a field by issuing a query similar to the following:
|
which pushes the burden to the database.