DB2 SQL Case Insensitive

Question

I'm executing the below DB2 SQL via SQL Server (so needs to be in DB2 SQL):

exec ('
  select 
    TRIM (vhitno) AS "Item",
    TRIM (mmitds) AS "Description",
    TRIM (SUBSTRING (vhitno,12,4)) AS "Size",
    vhalqt AS "Available"
  from m3fdbtest.oagrln
    left outer join m3fdbtest.mdeohe 
      on vhcono = uwcono 
        and vhcuno = uwcuno 
        and vhagno = uwagno 
        and vhitno = uwobv1
    left outer join m3fdbtest.mitmas 
      ON vhcono = mmcono 
        AND vhitno = mmitno
    where uwcono = 1
      and uwstdt >= ?
      and uwlvdt <= ?
      and uwcuno = ''JBHE0001''
      and uwagst = ''20''
      and (vhitno LIKE ''%'' || ? || ''%''
        or mmitds LIKE ''%'' || ? || ''%'')',
  @From, @To, @Search, @Search) at M3_TEST_ODBC

However, DB2 is case sensitive - how do I make the two LIKES on mmitds and vhitno case insensitive?

Answer 1

This is a FAQ so maybe you should read more, for example: this article is one of many, and various approaches exist. The sample principles apply for i-series as Linux/Unix/Windows even if the implementations vary.

If you lack access to make table-changes (e.g. to add columns, indexes etc) then you might suffer the performance penalties of using UPPER() or LOWER() on the predicate columns. This may result in indexes on those columns being unable to be used and worse performance.

You should first verify if the relevant columns in the Db2 tables really have mixed-case values, and if they only have a single case then alter your query to ensure you compare against that case.

If the columns have mixed-case values and no fixed-case column (or UDF) exists, and if your query will be frequently run for a vital business purpose, then best advice is to ensure the table has an appropriate design (to support case insensitive comparisons) via any of a number of methods.

If Regular expression functions are available in your version of Db2, you might also consider using REGEXP_LIKE and a suitable regular expression.

Answer 2

You could use something like this:

where UPPER(mycol) like '%' || UPPER(?) || '%'

Beware: This could affect index selection, but you can create an index like this:

create index MYINDEX on MYTABLE (UPPER(mycol))

If you were using SQL embedded in RPG, you could set the program to use case insensitive sorts and comparisons with

SET OPTION SRTSEQ=*LANGIDSHR;

To do this with JDBC, you need to set the following driver properties:

"sort" = "language"
"sort language" = Your language code, I use "ENU"
"sort weight" = "shared"

For an ODBC connection you need to have the following connection properties set:

SORTTYPE = 2
LANGUAGE = your language code, I use ENU
SORTWEIGHT = 0

Answer 3

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.