By default ,DB2 is case-sensitive
That means 'A' and 'a' are not the same
Can i change some options to make it insensitive?
Thanks a lot

Not to my knowledge. You have to do case insentive checks on as as needed basis.

Andy

We migrate some sps from MSSQL to DB2.
It is too late When we wake up to the problem
It seems that we have to check all the sps manually
Thank you all the same

Well, it depends on what you need. You can add an uppercased version of the column you need to search with
<new column> GENERATED ALWAYS AS (UPPER(<original column>))

When searching the table using where <original column> = UPPER('Search text') DB2 will use the generated column. All you have to do is to create an index on this column.

I've also seen a way to generate a totally case insensitive database but you need to use the C API to get that done. If you are interested in this I can try to find it, I mailed it my work some weeks ago.

Hope this helps.

It will be quiet interesting if you can provide us with information on how to do create a case-insensitive db using C APIs ...

Thanks in Advance

Cheers
Sathyaram


I've also seen a way to generate a totally case insensitive database but you need to use the C API to get that done. If you are interested in this I can try to find it, I mailed it my work some weeks ago.

Hi Sathyaram

I found it in the outbox of my newsreader also. It's copied from a post from Sydney Monteiro on one of the newsgroups on the IBM newsserver.

Here it is:

============================================


The collating sequence is set at the database level and it can only be set
when creating a database. That means that once set it can not be changed.

The CREATE DATABASE STATEMENT only offers rudimentary support for
setting the collating sequence
(Realistically SYSTEM, IDENTITY and COMPATIBILITY are the only reasonable choices for U.S. folks)

On the other hand, the administrative routine sqlecrea has the means to get the work done.

Go to the copyrighted IBM material at
http://publib.boulder.ibm.com/infoce...brecov-sqc.htm

Find the int DbCreate(char existingDbAlias[], char newDbAlias[])
function and modify

dbDescriptor.sqldbcss = SQL_CS_NONE;

to read

If compiled on the server and ran on the server, even the issues with code points for the EBCDIC versus ASC collating sequence are handled transparently by the C library's isalpha().

BAM! Now you have a function that creates a new database with same code set and territory as the existing database, and ALL compare operations (>,<,like, indexing, and order by) are done case insensitively. :-)

============================================


I don't have a C compiler on my system so I can't give it a try. It would be interesting to know if it really works.

Kind regards, Gert