Case INsensitivity for string comparisons

[Tom]

Hi,

Our development team is adding DB2 8.1 compatibility to our existing
application which currently supports SQLServer 2000. Our code is
written to take advantage of SQLServer's ability to allow query
string compares to be case insensitive. We have identified DB2 forces
all query string compares to be evaluated case sensitive. Since our
code is written to assume case insensitivity, it curently seems that
we must change our code base to force each compare to be case
insensitive in order to be compatible with DB2.

We are looking for a configuration option or similar mechanism for DB2
8.1 to allow for all query string compares to be case insensitive by
default. This is to allow us to avoid making any changes in our code
to compensate for the case sensitive nature of DB2 string compares.

Specifically: what we are looking for is a way that the code:

WHERE lastname = UPPER('Geyer')

can be coded as

WHERE lastname ='Geyer'

and any combination of case in the data that = 'geyer' would match.

1. Does DB2 8.1 have a mechanism for changing all query string
compares to be case insensitive?

2. If not, what is the recommended technique for performing case
insensitive string compares in DB2?

3. If not, does IBM have plans to enable case insensitive string
compares in DB2 in an upcoming release?


Thanx much,

Tom

[Blair Adamache]

Re: Case INsensitivity for string comparisons

v8.1 has workarounds with views and generated columns (including index
support) described here:

http://www-106.ibm.com/developerworks/db2/library/techarticle/0203adamache/0203adamache.html

Tom wrote:
[color=blue]
> Hi,
>
> Our development team is adding DB2 8.1 compatibility to our existing
> application which currently supports SQLServer 2000. Our code is
> written to take advantage of SQLServer's ability to allow query
> string compares to be case insensitive. We have identified DB2 forces
> all query string compares to be evaluated case sensitive. Since our
> code is written to assume case insensitivity, it curently seems that
> we must change our code base to force each compare to be case
> insensitive in order to be compatible with DB2.
>
> We are looking for a configuration option or similar mechanism for DB2
> 8.1 to allow for all query string compares to be case insensitive by
> default. This is to allow us to avoid making any changes in our code
> to compensate for the case sensitive nature of DB2 string compares.
>
> Specifically: what we are looking for is a way that the code:
>
> WHERE lastname = UPPER('Geyer')
>
> can be coded as
>
> WHERE lastname ='Geyer'
>
> and any combination of case in the data that = 'geyer' would match.
>
> 1. Does DB2 8.1 have a mechanism for changing all query string
> compares to be case insensitive?
>
> 2. If not, what is the recommended technique for performing case
> insensitive string compares in DB2?
>
> 3. If not, does IBM have plans to enable case insensitive string
> compares in DB2 in an upcoming release?
>
>
> Thanx much,
>
> Tom[/color]

[PM \(pm3iinc-nospam\)]

Re: Case INsensitivity for string comparisons

Other solutions may include ...

If your app uses it's own database, maybe you can create one using a user
defined collating sequence.
(or find a system collating sequence that is case insensitive?)

Maybe there is also something to do with COLLATING_SEQUE NCE option of the
federated features of db2.
(never been there... federating db2 to db2)
Function mapping?

I suggest you also read :

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

search : "LIKE predicate applied to a CHAR column"
Just to show you that like predicates may behave differently. (db2 vs sql
server)

As for your Q3, i doubt ibm will ever default to
orange = apple or 'a' = 'A'
UCASE and other text functions are there to support users who wish
to deviate from the default.

Maybe there are other solutions available.
Is the app using java, odbc, ... ?

PM

"Tom" <tmedrek@gr.com > a écrit dans le message de
news:bb35f97e.0 401270811.28eb0 fb0@posting.goo gle.com...[color=blue]
> Hi,
>
> Our development team is adding DB2 8.1 compatibility to our existing
> application which currently supports SQLServer 2000. Our code is
> written to take advantage of SQLServer's ability to allow query
> string compares to be case insensitive. We have identified DB2 forces
> all query string compares to be evaluated case sensitive. Since our
> code is written to assume case insensitivity, it curently seems that
> we must change our code base to force each compare to be case
> insensitive in order to be compatible with DB2.
>
> We are looking for a configuration option or similar mechanism for DB2
> 8.1 to allow for all query string compares to be case insensitive by
> default. This is to allow us to avoid making any changes in our code
> to compensate for the case sensitive nature of DB2 string compares.
>
> Specifically: what we are looking for is a way that the code:
>
> WHERE lastname = UPPER('Geyer')
>
> can be coded as
>
> WHERE lastname ='Geyer'
>
> and any combination of case in the data that = 'geyer' would match.
>
> 1. Does DB2 8.1 have a mechanism for changing all query string
> compares to be case insensitive?
>
> 2. If not, what is the recommended technique for performing case
> insensitive string compares in DB2?
>
> 3. If not, does IBM have plans to enable case insensitive string
> compares in DB2 in an upcoming release?
>
>
> Thanx much,
>
> Tom[/color]