Case-insensitive search in DB2 for i
Published on: Tue Nov 25 2025
When working with DB2 for i, you might encounter situations where you need to perform case-insensitive searches. This can be particularly useful when dealing with user input or data that may not have consistent casing. Fortunately, DB2 for i provides a couple of methods to achieve case-insensitive searches.
Let’s assume that we want to search for employee Deepak in the employee table, and it is keyed in as shown below :
Option 1: Using the UPPER or LOWER functions
This is the most straightforward method. You can convert both the column and the search term to either upper or lower case using the UPPER() or LOWER() functions. Here’s an example:
SELECT *
FROM employee
WHERE LOWER(first_name) = LOWER('Deepak');The key to making this work efficiently is to ensure that the column being searched is indexed. If the column is indexed, DB2 can utilize the index even when using these functions, which helps maintain performance.
You would create the index like this:
create index first_name_lower on employee(lower(first_name));This is the most common approach and works well for many scenarios.
Option 2: Using COLLATING SEQUENCES
Assuming you have your collating sequence set up correctly, your query can be as simple as:
SELECT *
FROM employee
WHERE first_name = 'deepaK' So, even though our input is 'deepaK', it will match 'Deepak' in the database.
But, what exactly is a collating sequence?
A collating sequence (also called a sort sequence) defines how characters in a character set relate to each other when they are compared and ordered. A collating sequence can also be used to treat certain characters as equivalent, for instance, a and A.
| Type of Collating Sequence | Description |
|---|---|
| Shared-weight collating sequence | A collating sequence in which two or more bytes have the same value. For example, in a shared-weight collating sequence, the uppercase letter A and the lowercase letter a might both have the same weight, making them equivalent for comparison purposes. |
| Unique-weight collating sequence | A collating sequence in which every byte has a unique value. For example, in a unique-weight collating sequence, the uppercase letter A and the lowercase letter a would have different weights, making them distinct for comparison purposes. |
In DB2 for i, you need to set the following parameters to enable case-insensitive searches using collating sequences:
- QSRTSEQ - The sort sequence
- QLANGID - The language ID
In most cases, you would set these parameters in your JDBC connection. The default, in IBM ACS, is shown below:
You need to change the value as shown below:
Wait, just a change in JDBC parms is all it takes? Well, yes and no.
- Yes, that is all it takes.
- No, if you are concerned about performance.
Any existing index created without considering the collating sequence will not be usable. You can see this in Visual Explain where it did not utilize the existing index on first_name and opted for a full table scan instead.
However, if you now create a new index on first_name considering the collating sequence, DB2 will be able to utilize it for case-insensitive searches.
create index first_name_sortseq on employee(first_name);The way you create the index is the same as before, but now, based on the JDBC parm, DB2 understands that it needs to consider the collating sequence for comparisons.
BTW, you can specify these parms for STRSQL also.
A list of the indexes that we currently have on the employee table is shown below:
Apart from case-insensitive searches, collating sequences can also help with other scenarios, such as ignoring accents in characters (e.g., treating ‘é’ and ‘e’ as equivalent). So, for a name like ‘Björn’, a search for ‘Bjorn’ would yield a match.
SELECT *
FROM employee
WHERE first_name = 'bjorn';
In conclusion, both methods have their pros and cons. Using UPPER() or LOWER() is straightforward and easy to implement, while using collating sequences can provide better user experience with the right indexing strategy. Choose the method that best fits your application’s needs!