EVALUATE UNCOMMITTED field (EVALUNC subsystem parameter)
The EVALUNC subsystem parameter controls whether predicate evaluation is to be allowed on uncommitted data of other transactions. This parameter applies only to stage 1 predicate processing that uses table access for queries with isolation level RS or CS. Table access includes table space scan, index-to-data access, and RID-list processing.
The EVALUNC subsystem parameter applies to predicates in queries, searched UPDATE statements, and searched DELETE statements.
- To predicates in SQL statements that access the Db2 catalog or directory
- To data definition statements, such as DROP TABLESPACE, for which internal processes query catalog or directory tables
Acceptable values: | NO, YES |
---|---|
Default: | NO |
Update: | option 29 on panel DSNTIPB |
DSNZPxxx: | DSN6SPRM EVALUNC |
- NO
- Predicate evaluation occurs only on committed data (or on the
application's own uncommitted changes). NO ensures that all qualifying
data is always included in the answer set.
This is the default value.
- YES
- Predicate evaluation can occur on uncommitted data of other transactions.
With YES, data might be excluded from the answer set. Data that does
not satisfy the predicate during evaluation but then, because of undo
processing (ROLLBACK or statement failure), reverts to a state that
does satisfy the predicate is missing from the answer set. A value
of YES enables Db2 to
take fewer locks during query processing. The number of avoided locks
depends on:
- The query's access path
- The number of evaluated rows that do not satisfy the predicate
- The number of those rows that are on overflow pages
Recommendation: Specify YES to improve concurrency if your applications can tolerate returned data that might falsely exclude any data that would be included as the result of undo processing (ROLLBACK or statement failure).
Although the option influences whether predicate evaluation can occur on uncommitted data, it does not influence whether uncommitted data is returned to an application. Queries with isolation level RS or CS return only committed data. They never return the uncommitted data of other transactions, even if predicate evaluation occurs. If data satisfies the predicate during evaluation, the data is locked as needed, and the predicate is re-evaluated as needed before the data is returned to the application.