Search code examples
sqlsortingdb2db2-luw

DB2 order by clause gives different results on different OS

I am trying out a simple SQL to sort varchar column using order by clause in DB2.

Query is something like :

select * from ThirdParty where ThirdPartyKy > 10001 order by ThirdPartyName

The sample data I've for ThirdPartyName columns is (Not necessarily in the same order) :

ThirdPartyName :

'AA'
'bb'
'CC'
'BB'
'aa'
'cc'

I am executing this query using SQuirrel against Local windows install of DB2 10.5 FP8 and against server install on AIX (same version). I'm getting different output of above SQL on both these.

Output on DB2 - Windows

'aa'
'AA'
'bb'
'BB'
'cc'
'CC'

Output on DB2 - AIX

'AA'
'BB'
'CC'
'aa'
'bb'
'cc'

Now, I know that using 'upper(thirdpartyname)' will give me results what I want i.e. String starting with Special Characters first, numeric and then case insensitive string sorting. I am unable to understand why would the behavior be different? Has anyone observed the same behavior for DB2? I've also started wondering what else I've to be careful about.

Solution

  • The sort order for strings in Db2, and in most of the other database systems, depends on the settings for the database system and database (and sometimes table and column).

    My guess is that your settings differ. Do the databases have the same codepage? Do they have the same language? The same collating sequence?

    Here is the link to string comparison rules. They determine the sort order: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008479.html

    Try the following to get databse details:

    get db cfg