Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DB2 Case Sensitivity

I'm having great difficultly making my DB2 (AS/400) queries case insensitive.

For example:

SELECT *
FROM NameTable
WHERE LastName = 'smith'

Will return no results, but the following returns 1000's of results:

SELECT *
FROM NameTable
WHERE LastName = 'Smith'

I've read of putting SortSequence/SortType into your connection string but have had no luck... anyone have exepierence with this?

Edit:

Here's the stored procedure:

BEGIN
DECLARE CR CURSOR FOR
SELECT  T . ID ,
    T . LASTNAME ,
    T . FIRSTNAME ,
    T . MIDDLENAME ,
    T . STREETNAME || ' ' || T . ADDRESS2 || ' ' || T . CITY || ' ' || T . STATE || ' ' || T . ZIPCODE AS ADDRESS ,
    T . GENDER ,
    T . DOB ,
    T . SSN ,
    T . OTHERINFO ,
    T . APPLICATION
FROM
    ( SELECT R . * , ROW_NUMBER ( ) OVER ( ) AS ROW_NUM
    FROM CPSAB32.VW_MYVIEW
    WHERE R . LASTNAME = IFNULL ( @LASTNAME , LASTNAME )
    AND R . FIRSTNAME = IFNULL ( @FIRSTNAME , FIRSTNAME )
    AND R . MIDDLENAME = IFNULL ( @MIDDLENAME , MIDDLENAME )
    AND R . DOB = IFNULL ( @DOB , DOB )
    AND R . STREETNAME = IFNULL ( @STREETNAME , STREETNAME )
    AND R . CITY = IFNULL ( @CITY , CITY )
    AND R . STATE = IFNULL ( @STATE , STATE )
    AND R . ZIPCODE = IFNULL ( @ZIPCODE , ZIPCODE )
    AND R . SSN = IFNULL ( @SSN , SSN )
    FETCH FIRST 500 ROWS ONLY )
AS T
WHERE ROW_NUM <= @MAXRECORDS
OPTIMIZE FOR 500 ROW ;

OPEN CR ;
RETURN ;
like image 490
mint Avatar asked Oct 11 '10 16:10

mint


People also ask

Are DB2 column names case-sensitive?

DB2 is not case sensitive, and all names default to uppercase. DB2 objects include tables, views, columns, and indexes.

What is a case-sensitive?

In computing, if a written word such as a password is case-sensitive, it must be written in a particular form, for example using all capital letters or all small letters, in order for the computer to recognize it.

What commands are considered case-sensitive?

For the most part MS-DOS, the Windows command line, and most of the commands are not case-sensitive. Unlike case-sensitive command line operating systems, like Linux, commands such as the MS-DOS dir command can be typed as DIR, Dir, or dir. In a case-sensitive operating systems, only the lowercase command would work.

Is SQL case-sensitive or insensitive?

SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.


1 Answers

Why not do this:

WHERE lower(LastName) = 'smith'

If you're worried about performance (i.e. the query not using an index), keep in mind that DB2 has function indexes, which you can read about here. So essentially, you can create an index on upper(LastName).

EDIT To do the debugging technique I discussed in the comments, you could do something like this:

create table log (msg varchar(100, dt date);

Then in your SP, you can insert messages to this table for debugging purposes:

insert into log (msg, dt) select 'inside the SP', current_date from sysibm.sysdummy1;

Then after the SP runs, you can select from this log table to see what happened.

like image 163
dcp Avatar answered Sep 21 '22 05:09

dcp