Avoid the use of LIKE patterns beginning with % or _

The percent (%), and underline (_), used in the pattern of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string like the row column values to select. They can take advantage of indexes when used to denote characters in the middle or at the end of a character string.

For example, when using SQL, specify the following:

… WHERE LASTNAME LIKE 'J%SON%'

When using the OPNQRYF command, specify the following:

... QRYSLT('LASTNAME *EQ %WLDCRD(''J*SON*'')')

However, when used at the beginning of a character string, they can prevent Db2® for i from using any indexes that might be defined on the LASTNAME column to limit the number of rows scanned using index scan-key positioning. Index scan-key selection, however, is allowed. For example, in the following queries index scan-key selection can be used, but index scan-key positioning cannot.

In SQL:

… WHERE LASTNAME LIKE '%SON'

In OPNQRYF:

… QRYSLT('LASTNAME *EQ %WLDCRD(''*SON'')')

Avoid patterns with a % so that you can get the best performance with key processing on the predicate. If possible, try to get a partial string to search so that index scan-key positioning can be used.

For example, if you were looking for the name "Smithers", but you only type "S%," this query returns all names starting with "S." Adjust the query to return all names with "Smi%". By forcing the use of partial strings, you might get better performance in the long term.