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 [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.

The Use Literals for Like property 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 be computationally intensive because more SQL code parsing is required, resulting in package cache overflows. Use this user property sparingly, and only 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 do not create more than one instance for a single field.

Parent Object Type

Field

Functional Area

Querying