SET CURRENT MDC ROLLOUT MODE statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

NONE

Specifies that MDC rollout optimization during delete operations is not to be used. The DELETE statement is processed in the same way as a DELETE statement that does not qualify for rollout.

IMMEDIATE

Specifies that MDC rollout optimization is to be used if the DELETE statement qualifies. If the table has RID indexes, the indexes are updated immediately during delete processing. The deleted blocks are available for reuse after the transaction commits.

DEFERRED

Specifies that MDC rollout optimization is to be used if the DELETE statement qualifies. If the table has RID indexes, index updates are deferred until after the transactions commits. With this option, delete processing is faster and uses less log space, but the deleted blocks are not available for reuse until after the index updates are complete.

host-variable

A variable of type VARCHAR. The length of host-variable must be less than or equal to 17 bytes (SQLSTATE 42815). The value of the host variable must be a left-aligned string that is one of 'NONE', 'IMMEDIATE', or 'DEFERRED' (case insensitive). If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

Notes

• Subsequent DELETE statements that are eligible for rollout processing respect the setting of the CURRENT MDC ROLLOUT MODE special register. Currently executing sections are not affected by a change to this special register.
• The effects of executing the SET CURRENT MDC ROLLOUT MODE statement are not rolled back if the unit of work in which the statement is executed is rolled back.
• After you run a SET CURRENT MDC ROLLOUT MODE statement, the behavior of MDC table rollout changes. The behavior of MDC table rollouts returns to the configuration set by the registry variable DB2_MDC_ROLLOUT when one of the following situations occur:
  • The connection/session to the database is terminated.
  • A CONNECT RESET is entered.
  • A SET CURRENT MDC ROLLOUT MODE NONE is entered.
• The DEFERRED mode is not supported on a data partitioned MDC table with partitioned RID indexes. Only the NONE and IMMEDIATE modes are supported. The cleanup rollout type will be IMMEDIATE if the DB2_MDC_ROLLOUT registry variable is set to DEFER, or if the CURRENT MDC ROLLOUT MODE special register is set to DEFERRED to override the DB2_MDC_ROLLOUT setting.

  If only nonpartitioned RID indexes exist on the MDC table, deferred index cleanup rollout is supported.

Example

   SET CURRENT MDC ROLLOUT MODE IMMEDIATE