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 [InfraUIFramework] 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 [InfraUIFramework] section of the application CFG file eliminates automatic trailing wildcards and the LIKE predicates that are generated as a result. Typically, the AutomaticTrailingWildcards parameter is set to FALSE in most implementations, 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. Additionally, when using scripting and the AutomaticTrailingWildcards parameter is set to TRUE, LIKE statements are added to the SQL from queries generated by the script. |