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 FIRS开发者_JAVA技巧T 500 ROWS ONLY )
AS T
WHERE ROW_NUM <= @MAXRECORDS
OPTIMIZE FOR 500 ROW ;

OPEN CR ;
RETURN ;

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.

If you want case-insensitive in your procedure, try using this option in it:

SET OPTION SRTSEQ = *LANGIDSHR ;

You should also create an index to support it for performance. Create the index when you have *LANGIDSHR as a connection attribute, and the shared-weight index should then be available to later jobs. (There are various ways to get the appropriate setting into effect.)

*LANGIDSHR relates to the language-ID for your jobs. Characters in the character set that might be considered as "equals", such as 'A' and 'a' or 'ü' and 'u', should be given equal weights (shared) and so select together.

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

See: https://stackoverflow.com/a/47181640/5507619

Database setting

There is a database config setting you can set at database creation. It's based on unicode, though.

CREATE DATABASE yourDB USING COLLATE UCA500R1_S1

The default Unicode Collation Algorithm is implemented by the UCA500R1 keyword without any attributes. Since the default UCA cannot simultaneously encompass the collating sequence of every language supported by Unicode, optional attributes can be specified to customize the UCA ordering. The attributes are separated by the underscore (_) character. The UCA500R1 keyword and any attributes form a UCA collation name.

The Strength attribute determines whether accent or case is taken into account when collating or comparing text strings. In writing systems without case or accent, the Strength attribute controls similarly important features. The possible values are: primary (1), secondary (2), tertiary (3), quaternary (4), and identity (I). To ignore:

• accent and case, use the primary strength level
• case only, use the secondary strength level
• neither accent nor case, use the tertiary strength level

Almost all characters can be distinguished by the first three strength levels, therefore in most locales the default Strength attribute is set at the tertiary level. However if the Alternate attribute (described below) is set to shifted, then the quaternary strength level can be used to break ties among white space characters, punctuation marks, and symbols that would otherwise be ignored. The identity strength level is used to distinguish among similar characters, such as the MATHEMATICAL BOLD SMALL A character (U+1D41A) and the MATHEMATICAL ITALIC SMALL A character (U+1D44E).

Setting the Strength attribute to higher level will slow down text string comparisons and increase the length of the sort keys. Examples:

• UCA500R1_S1 will collate "role" = "Role" = "rôle"
• UCA500R1_S2 will collate "role" = "Role" < "rôle"
• UCA500R1_S3 will collate "role" < "Role" < "rôle"

This worked for me. As you can see, ..._S2 ignores case, too.

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

CREATE DATABASE yourDB USING COLLATE CLDR181_S1

Collation 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, there is supposed to be a way to change the setting.

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

I do have problems executing this, but for all I know it is supposed to work.

Generated table row

Other options are e.g. generating a upper case row:

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.