DB2 case sensitivity

Question

It is very difficult for me to make my query in DB2 (AS / 400) case insensitive.

For instance:

SELECT * FROM NameTable WHERE LastName = 'smith' 

It will not return any results, but the following returns 1000 results:

 SELECT * FROM NameTable WHERE LastName = 'smith' 

I read that you add SortSequence / SortType to your connection string, but no luck ... does 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 ; 

Answer 1

Why not do it:

 WHERE lower(LastName) = 'smith' 

If you are concerned about performance (i.e. a query that does not use an index), keep in mind that DB2 has functional indexes that you can read about here . That way you can create an index on upper(LastName) .

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

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

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

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

Then, after running SP, you can select from this log table to find out what happened.

Answer 2

If you want case insensitivity in your procedure, try using this parameter in it:

 SET OPTION SRTSEQ = *LANGIDSHR ; 

You must also create an index to support its performance. Create an index if the connection attribute is *LANGIDSHR and the total weight index should then be available for subsequent jobs. (There are various ways to enter the appropriate setting.)

*LANGIDSHR refers to the language identifier for your assignments. Symbols in a character set that may be considered "equal", such as "A" and "a" or "ü" and "u", must have equal weights (shared) and therefore must be selected together.

Answer 3

I did something similar when I need a case insensitive search. I used UPPER(mtfield) = 'SEARCHSTRING' . I know this works.

Answer 4

See: fooobar.com/questions/1324964 / ...

Database setup

There is a database configuration setting that you can set in creating the database . However, it is based on unicode.

 CREATE DATABASE yourDB USING COLLATE UCA500R1_S1 

The default Unicode sorting algorithm is implemented by the keyword UCA500R1 without any attributes. Because the UCA by default cannot simultaneously include a sort sequence in each Unicode-supported language, additional attributes can be specified to configure the UCA order. Attributes are separated by an underscore (_). The UCA500R1 keyword and any attributes form the UCA sort name.

The Strength attribute determines whether accent or occasion is considered when matching or comparing text strings. When writing systems without incident or emphasis, the Strength attribute controls similarly important functions. Possible values: primary (1), secondary (2), tertiary (3), quaternary (4) and identical (I). To ignore:

• emphasis and occasion, use the level of primary strength.
• use secondary strength level
• neither accent nor case, use tertiary strength level

Almost all symbols can be selected using the first three levels of strength, so in most locations the Strength attribute is set to the tertiary level by default. However, if the Alternate attribute (described below) is set to a shift, then the Quaternary Strength level can be used to break the links between space characters, punctuation marks, and characters that would otherwise be ignored. A strong identity level is used to distinguish among similar characters, such as MATHEMATICAL BOLD SMALL A (U + 1D41A) and MATHEMATICAL ITALIC SMALL A (U + 1D44E).

Setting the Strength attribute to a higher level slows down the comparison of lines of text and increases the length of the sort keys. Examples:

• UCA500R1_S1 will match "role" = "Role" = "rôle"
• UCA500R1_S2 will map "role" = "Role" <"ROLE"
• UCA500R1_S3 will map the "role" <"Role" <"ROLE"

It worked for me. As you can see ... S2 also ignores case.

Using the newer standard version , it should look like this:

 CREATE DATABASE yourDB USING COLLATE CLDR181_S1 

Sort keywords :
UCA400R1 = Unicode Standard 4.0 = CLDR version 1.2
UCA500R1 = Unicode Standard 5.0 = CLDR version 1.5.1
CLDR181 = Unicode Standard 5.2 = CLDR version 1.8.1

If your database is already created, it is assumed that it will change the setting .

 CALL SYSPROC.ADMIN_CMD( 'UPDATE DB CFG USING DB_COLLNAME UCA500R1_S1 ' ); 

I am having problems with this, but I know that it should work.

Table row generated

Other options include, for example, generating an uppercase string :

 CREATE TABLE t ( id INTEGER NOT NULL PRIMARY KEY, str VARCHAR(500), ucase_str VARCHAR(500) GENERATED ALWAYS AS ( UPPER(str) ) )@ INSERT INTO t(id, str) VALUES ( 1, 'Some String' )@ SELECT * FROM t@ ID STR UCASE_STR ----------- ------------------------------------ ------------------------------------ 1 Some String SOME STRING 1 record(s) selected.