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:
https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1572/html/sprocs/X46017.htm

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:
https://www.codeproject.com/Articles/71612/Controlling-Collation-Order-On-DB

https://www.ibm.com/docs/en/db2/10.1.0?topic=sequences-simulation-ebcdic-binary-collation

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:
•  ASCII
•  EBCDIC
•  CHARSET_COLL
•  JIS_COLL
•  MULTINATIONAL
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
https://docs.teradata.com/#page/SQL_Reference/B035_1141_111A/ch03.105.069.html
https://docs.teradata.com/#page/SQL_Reference/B035-1144-160K/vpx1472241543698.html

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
article-img
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. %
article-img
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. 
article-img
Marketing & Commerce | April 21, 2020
Understanding Campaign Listener Clustering and Listener Failover – Part 1
Single Listener Environments To understand how Campaign Listener clustering and its use in failover work, you should first understand how the Campaign listener works in general. The following diagram depicts the basic setup of a single listener environment. In a single Campaign listener setup, the listener receives requests from the Campaign J2EE deployment in the web application server for specific Campaign feature actions made in the Unica UI. For example, after a user logs into the Unica UI and clicks on a Campaign-related feature (such as the Campaign > Campaigns menu item), a request is sent to the Campaign listener. It will fork off a separate independent process called “unica_acsvr” that runs on the Campaign analytic server (the machine where the listener process is running). This unica_acsvr process becomes the user’s Campaign Login session. Each user has their own unica_acsvr process for their login session. If the user then clicks on a Flowchart tab within a Campaign, the listener receives another request from the Campaign J2EE deployment to fork another separate unica_acsvr process that is exclusive to that flowchart. This unica_acsvr process loads into its memory the flowchart.ses file contents and any cell run results from prior executions of that flowchart if any (so green checkmarks and output cell counts can be displayed on each process box). Each flowchart accessed by users has its own unica_acsvr process started. Even when automated flowchart tasks (such as those started by the Unica scheduler or the Campaign utility unica_svradm) are started to execute flowchart logic, a single unica_acsvr process for that flowchart is spawned by the listener. Anytime a unica_acsvr process is spawned by the listener, the listener also adds a reference for that unica_acsvr into the Campaign_home/conf/unica_aclsnr.udb file. This file is the means of the listener understanding which unica_acsvr processes are running...
Close
Filters result by
Sort:
|