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
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
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 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 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 Multinational
.SQL_Latin1_General_Cp850_BIN collation gives correct result with sorting. We recommend you to choose
appropriate sqlserver collation sequence corresponding to ASCII supported by campaign.One such example of this collation is SQL_Latin1_General_Cp850_BIN.
I am a Technical Analyst with over 3+ years of experience in the IT and software industry, with a focus to help my clients do better and achieve better customer satisfaction. I am currently a Technical Analyst for Marketing Suite at HCL.
Campaign is a trademark of IBM Corporation, registered in many jurisdictions, and are used under license.