Today, I am writing about how different binary collations between databases affects Campaign sorting and result in error.
Campaign uses binary sort collation but this is not enough. I encountered a case where two databases (Oracle and Netezza) are using binary sort collation but still it results in incorrect sort order. In the case, input is taken from 1 database and this was made output to another database.
ERROR Message encountered : [SORT ORDER] [THIS_CYCLE] Audience ID: Wrong sort order detected (Last ID > This ID)
[SORT ORDER] [THIS_CYCLE] Audience ID: Wrong sort order detected (Last ID > This ID)
Last ID: Name=FK_ROWID_ASSET, Type=8, Chars=100, Bytes=100, dbtype=17, prec=0, Value='02i0Y000001XOBiQAO'
Reason: This is because sort order of database is different from what Campaign expects. Although both databases have binary sort collation yet there is a difference between these two binary sort collations itself. One database is using ASCII format and the other one is using EBCDIC standard.
In the ASCII standard, all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters.
So, the way one database sorts is different from the way other database expects sorting to be. Hence it, resorts them onto campaign server. That's why we are having wrong sort order error.
If this is the case, there is no way we can avoid this error. This is as expected.
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 is used under license.