Good day,

Environment: DB2 Express-C 9.7 on Windows XP.

From what I understood by a web search, DB2 compares the tables data
always in a case sensitive way and can not be changed in this
behaviour by default. (MS SQL Server could)

Now my questions:

1) How do I best avoid invalid data in a primary key column. Say
country_code should always be upper cased. Should a CHECK constraint
be used? Are there other / better ways?

2) If I need to do a JOIN on some other (not constained) columns, what
is the best way to enforce a comparison in a case insensitive way. Say
'Netherlands' and 'netherlands' and 'NETHERLANDS' should all JOIN in
the same way. Should UCASE / LCASE put into the JOIN condition? What
about the performance then? Other options here?

Thanks and brgds

Philipp Post

On 7/30/2010 9:21 PM, Philipp Post wrote:
collation.
Of course that has performance implications compared to binary or system
collation.

Anyway, a CHECK constraints is the way to go for individual columns.
They are very efficient in DB2.

When you join with another non-uppercased column you would need to
uppercase that column in deed. Thsi can have negative impact on the
optimizer plan (since Db2 can't use an index anymore.
You can mitigate that by adding a generated column that pre-computes the
uppercased value.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Thanks Serge, that helps. Also I now found the right papers on this at
Developerworks.

brgds

Philipp Post