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

Read syntax diagramSkip visual syntax diagram
>>-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

Example

Specify deferred cleanup behavior for the next DELETE statement that qualifies for rollout processing.
   SET CURRENT MDC ROLLOUT MODE IMMEDIATE