Use of literals in certain queries with LIKE predicates can improve the speed of those queries on DB2 UDB by providing the database optimizer with better information with which to choose appropriate indexes for the query.
A predicate of the form "LIKE ?" is generated whenever a wildcard (* or ?) is included in a search string. A wildcard may be included in a search string in either of the following ways:
- The user includes the wildcard explicitly in the search string; for example "ABC*" or "*ABC?".
- A trailing wildcard (*) is automatically appended to a search string when all of the following apply:
- The search string does not contain any wildcards, such as just "ABC".
- The search string is not preceded with the = sign.
- The AutomaticTrailingWildcards parameter in the SWE section of the application CFG file (such as uagent.cfg) is set to TRUE or is not present.
NOTE: Adding the AutomaticTrailingWildcards parameter with a value of FALSE in the SWE section of the application CFG file eliminates automatic trailing wildcards and the LIKE predicates that are generated as a result. Typically, implementations should have the AutomaticTrailingWildcards parameter set to FALSE, thereby removing much of the need for the Use Literals for Like user property.
When using a bind variable, criteria containing wildcards are treated as LIKE ? in the SQL. The Use Literals for Like user property provides a further conversion of the SQL to, for example, LIKE "ABC%", thus specifying the exact location of wildcards with the % sign.
Use Literals for Like can provide greater efficiency than using bind variables for searches on some criteria, but it does not improve performance for other criteria. For example:
- Searches are typically more efficient for criteria with wildcards in trailing positions only, such as "ABC*" because an appropriate index is more easily chosen by the optimizer to do a matching scan.
- Searches of large tables on criteria with wildcards in leading positions, such as "*ABC", typically impact performance. Use Literals for Like does not improve performance for such searches because it does not significantly reduce the number of rows scanned.