| October 23, 2018

Campaign Sort Orders

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

Swati Rajput

Swati Rajput is an IBM Certified Big Data Architect working as a Technical Analyst and a Client Advocate for the Marketing Suite at HCL.

Topics in this article:

Campaign Version 11.0

13 Comments

Pooja on January 10, 2019 at 10:05 am

Good set of information! It was really helpful.thanks
Reply
- Swati on January 10, 2019 at 7:51 pm
  
  Thank you Pooja
Sourav Dutta on January 10, 2019 at 11:08 am

Brief and to the point ! Kudos for this write up … Good post ?
Reply
- Swati on January 10, 2019 at 7:51 pm
  
  Thank you Sourav
Ishan on January 10, 2019 at 12:40 pm

Its really very helpful ..Amazing work
Reply
- Ishan on January 10, 2019 at 7:50 pm
  
  Thank you Ishan
Shivangi on January 10, 2019 at 7:45 pm

It’s very helpful.
Reply
- Swati on January 10, 2019 at 7:50 pm
  
  Thank you Shivangi
Ajay on January 10, 2019 at 8:00 pm

Useful info on sort order.
Reply
- SWATI RAJPUT on January 10, 2019 at 8:01 pm
  
  Thanks Ajay
Soumyadeep Biswas on January 10, 2019 at 8:14 pm

Great Piece of Information.
Would like to more in details.
Will be waiting for the upcoming blogs.
Reply
Abheek on January 10, 2019 at 9:26 pm

This is impressive Swati. I really like the way you used the examples to explain the sort orders. Keep up the good work and keep blogging.
Reply
Nichil on December 1, 2020 at 8:44 am

This is very valuable information. Thank you for sharing. I would like to add that Oracle will not sort unless you set the Datasource Property EnableSelectOrderBy to true. Can you confirm this ?
Reply

Campaign Sort Orders

Topics in this article:

Never Miss an Update

13 Comments

Submit a Comment Cancel reply