HCL SW Blogs
Select Page


This is Swati Rajput. I am getting started with my first blog today in which I am writing about various sort orders that a database has and what sort orders are specifically needed by Campaign.

If sort order of database is different than what Campaign needs, what parameters, configuration or commands need to be run on database to match those so that data sorting can be avoided on Campaign Server. Instead if appropriate sort orders are set , sorting of data would be carried on database which would be much faster.

By default, Campaign Use Binary Sort order.
In order to do proper sorting at campaign, set default character set as UTF-8, default character type as Unicode and sort order as Binary.

Oracle Sort Orders:
Default sorting order in oracle is binary.
Execute this command to check various sorting techniques in Oracle:
select * from v$nls_valid_values where parameter=’SORT’;

​Sort order Sql Server​

More description:

DB2 sort order

collate_info – Collating Information
This parameter determines the database’s collating sequence. For a language-aware collation, the first 256 bytes contain the string representation of the collation name (for example, SYSTEM_819_US).

This parameter can only be displayed using the db2CfgGet API. It cannot be displayed through the command line processor or the Control Center. You can specify the collating sequence at database creation time.

More description:


Teradata sort order:
The Teradata Database offers five standard collation sequences in which data can be defined as CASESPECIFIC or NOT CASESPECIFIC. This affects how the five collation sequences collate and compare data.

The five collations, determined either by default or explicit use of the SET SESSION COLLATION statement, are:
CASESPECIFIC or NOT CASESPECIFIC can be chosen at table definition time, or specified as part of the SQL statement.

The default collation sequence is based upon the client type:
•  EBCDIC for channel-attached clients
•  ASCII for all other clients

Campaign Sort order:
If sort order of database is different from what Campaign expects, it gives error like below:
This is a warning message. It means that the database returned records in an order different from the order that Campaign expects. The Campaign server detected this and stopped retrieving records from the database. Instead, Campaign pulled the entire list of IDs down to the Campaign temp directory on the server. There, Campaign re-sorted the IDs and continued processing the rest of the flowchart with the correctly sorted list being used from the temp space on the server.
​Question:  Then how Can we change the sort order for the database sessions that are being initiated from Campaign if database’s sort order is not same as Campaign expects and changing it directly in the database is last option?
We can use
SQLOnConnect property (settings->configuration->campaign->partition->partition1->datasources->Your data source) to change sort order for the database sessions that are being initiated from Campaign.The SQLOnConnect property defines a complete SQL statement that Campaign runs immediately after each database connection. The SQL statement generated by this property is automatically passed to your database without
checking its syntax this property is undefined by default.
You can run below commands at campaign side to change sort order at database side without checking its syntax:Eg: For oracle and DB2 database use: Alter session set NLS_SORT=’BINARY’For Teradata Datasource in sqlOnconnect Property
Set session Collation ASCII or
Set Session Collation MultinationalFor sqlserver:​.SQL_Latin1_General_Cp850_BIN collation gives a correct result with sorting. We recommend you to choose
appropriate sqlserver collation sequence corresponding to ASCII supported by the campaign. One such example of this collation is SQL_Latin1_General_Cp850_BIN.

Comment wrap
Further Reading
Unica Centralized Offer Mannagement
Marketing & Commerce | August 6, 2020
Offer Creation Using Unica Centralized Offer Management (COM)
Having an offer management system in place enables you to manage offers, their priorities, the number of times a customer sees a specific offer, to which customers the Offer should be shown to. With offer management, you can easily personalize your offers and tailor an efficient user experience for the audience. %
Let’s Geek Out on Unica
Marketing & Commerce | July 6, 2020
Let’s Geek Out on Unica: Scaling Your Campaign Execution
Everyone is trying to do more with less – quickly. I have clients who ask me all the time “How do I makes things faster?” or “How do I streamline campaign execution while doing more campaigns?” Both valid questions. Folks want to execute at scale, do more personalization, and run more campaigns in more channels simultaneously. This is where the magic that is Unica can come to the rescue. 
Campaign Listener Clustering and Listener Failover
Marketing & Commerce | April 21, 2020
Understanding Campaign Listener Clustering and Listener Failover – Part 1
To understand how Campaign Listener clustering and its use in failover work, you should first understand how the Campaign listener works in general.
Filters result by