DB2 10.5 for Linux, UNIX, and Windows

SET CURRENT MDC ROLLOUT MODE statement

The SET CURRENT MDC ROLLOUT MODE statement assigns a value to the CURRENT MDC ROLLOUT MODE special register. The value specifies the type of rollout cleanup that is to be performed on qualifying DELETE statements for multidimensional clustering (MDC) tables.

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


>>-SET--CURRENT--MDC ROLLOUT MODE--+-NONE----------+-----------><
                                   +-IMMEDIATE-----+   
                                   +-DEFERRED------+   
                                   '-host-variable-'

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 DB2® 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

Specify deferred cleanup behavior for the next DELETE statement that qualifies for rollout processing.

   SET CURRENT MDC ROLLOUT MODE IMMEDIATE