Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    China
    Posts
    2

    Talking Unanswered: problem about case-sensitive

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Not to my knowledge. You have to do case insentive checks on as as needed basis.

    Andy

  3. #3
    Join Date
    Nov 2003
    Location
    China
    Posts
    2
    Originally posted by ARWinner
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by kylin
    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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by sathyaram_s
    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
    Code:
    >--+-----------------------------------+------------------------>
    1   |                .-SYSTEM---------. |
    1   '- COLLATE USING--+-COMPATIBILITY--+-'
    1                    +-IDENTITY-------+
    1                    +-IDENTITY_16BIT-+
    1                    '-NLSCHAR--------'
    (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

    Code:
    dbDescriptor.sqldbcss = SQL_CS_USER;
     {
      int i;
        for(i=0;i<256;i++)
        {
       if (isalpha(i))
        dbDescriptor.sqldbudc[i]=toupper(i);
       else
        dbDescriptor.sqldbudc[i]=i;
         }
     }
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •