Value

TRUE or FALSE.

If the value of this user property is TRUE, then literals, instead of bind variables, are generated as criteria for LIKE predicates in the SQL for queries on the field.

If the value of this user property is FALSE or the user property is not defined on a field, then bind variables are used in criteria in the SQL for queries on the field.

Usage

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.

Follow these other guidelines for using Use Literals for Like:

• Try this user property with fields for which you have a strong indication from performance and from DB2 UDB utilities that inefficient indexes are being chosen by the optimizer. Deploy this solution to production if you see a significant improvement in performance with the user property.
• This property provides no benefit for case-insensitive queries.

CAUTION:  Widespread use of this user property can drive up CPU use because more SQL parsing and prepares are required, and can result in package cache overflows. Use this user property sparingly, on fields for which you have confirmed the benefit by testing.

You can inactivate this user property or modify its value. You can create new instances of this user property, but you should not create more than one instance for a field.

Parent Object Type

Field

Functional Area

Querying