Prev: DB2 & LDAP problem
Next: SQL Result from A or B
From: Philipp Post on 30 Jul 2010 11:51
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

From: Serge Rielau on 30 Jul 2010 14:19
On 7/30/2010 9:21 PM, Philipp Post wrote:
> 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
>
Philipp, Actually you can create the database with a case insensitive
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

From: Philipp Post on 31 Jul 2010 09:34
Thanks Serge, that helps. Also I now found the right papers on this at
Developerworks.

brgds

Philipp Post
 | 
Pages: 1
Prev: DB2 & LDAP problem
Next: SQL Result from A or B