0

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?

2 Answers 2

0

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.

1
  • Thanks for your answer, but since I could not find anything in the manual, and can't believe this isn't possible, I asked here. Secondly, I don't want to define a collation for every user but choose one and stick with it. If there is a performance impact, why is it? Doesn't the database layout data or index structure in the given collation?
    – Tim Büthe
    Aug 30, 2009 at 18:04
0

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')

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .