Invalid SQL SQL Case

Question

I am running below DB2 SQL through SQL Server (as it should 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 two LIKES on mmitds and vhitno case insensitive?

Answer 1

You can use something like this:

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

Beware: this may affect the choice of index, but you can create an index like this:

 create index MYINDEX on MYTABLE (UPPER(mycol)) 

If you used SQL built into the RPG, you can configure the program to use case-insensitive sorts and comparisons

 SET OPTION SRTSEQ=*LANGIDSHR; 

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

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

To connect to ODBC, you must set the following connection properties:

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

Answer 2

These are frequently asked questions, so maybe you should read more, for example: this article is one of many, and there are various approaches. Examples of principles apply to i-series like Linux / Unix / Windows, even if the implementation is different.

If you do not have access to make changes to the table (for example, to add columns, indexes, etc.), then you may suffer performance loss when using UPPER() or LOWER() in predicate columns. This can result in the inability to use indexes for these columns and slow performance.

First you need to check if the corresponding columns in the Db2 tables really have mixed case values, and if they have only one case, modify your query to ensure a comparison with that case.

If the columns are mixed-case and the fixed-register (or UDF) column does not exist, and if your query will often be executed for vital business purposes, then the best advice is to make sure that the table has the appropriate design (to support the register). insensitive comparisons) by any of a number of methods.

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

Answer 3

Database setup

There is a database configuration setting that you can set when creating a database creation . 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.