Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '09 at 18:04
add comment
up vote 0 down vote accepted

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')
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.