Home / server / Questions / 59899
Accepted
Tim Büthe
Asked: 2009-08-29 01:18:35 +0800 CST

How to change the collation of a table/column?

  • 772

I know, there is the database configuration parameter "Database collating sequence" for setting the collation in db2. But since this effects the whole database and can only be set when creating it, I'm looking for the possibility for setting this for a single table or column. Alternatively, setting the collation in the SQL statement, like you could do on Microsoft SQL Server:

SELECT * FROM table ORDER BY col1 COLLATE Latin1_General_CI_AS

Is this possible in DB2? How?

Secondly, how will changing the collation for either for the whole db, a table, or within a query effect the performance?

db2

2 Answers

  1. This is kind of information that should be in the manual.

    I don't know about DB2 but for others (MySql, PostgresSQL, SQL Server or Oracle) you can use ALTER TABLE to modify the collation (or ALTER DATABASE to modify default collation for new tables).

    Regarding your second question: YES - it has a bug impact on speed and it would be best to not set the collation in the select. You could use Unicode collation algorithm that is supposed to do best Unicode sorting: even so you'll find out that this algorithm is not working perfect for all languages but from statistical point of view you'll get best results.

    Implementing different collation for each user could be too costly even if this is not a bad thing for the user experience.

    • 0
  2. Best Answer

    There is a function called COLLATION_KEY_BIT which can be used within a Statement like this:

    SELECT FIRSTNME, LASTNAME
FROM EMPLOYEE
ORDER BY COLLATION_KEY_BIT(LASTNAME, 'UCA400R1_LDE')
    • 0