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
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.
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
If sort order of database is different from what Campaign expects, it gives error like below:
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.
Good set of information! It was really helpful.thanks
Thank you Pooja
Brief and to the point ! Kudos for this write up … Good post ?
Thank you Sourav
Its really very helpful ..Amazing work
Thank you Ishan
It’s very helpful.
Thank you Shivangi
Useful info on sort order.
Great Piece of Information.
Would like to more in details.
Will be waiting for the upcoming blogs.
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.
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 ?