DB2 10.5 for Linux, UNIX, and Windows

SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement

The SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement changes the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register.

This statement is not under transaction control.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax


                            .-TABLE-.          
>>-SET--CURRENT MAINTAINED--+-------+--TYPES-------------------->

   .-FOR OPTIMIZATION-.  .-=-.   
>--+------------------+--+---+---------------------------------->

>--+-ALL----------------------------------------------------------------+-><
   +-NONE---------------------------------------------------------------+   
   +-host-variable------------------------------------------------------+   
   | .-,--------------------------------------------------------------. |   
   | V                                                                | |   
   '---+-FEDERATED_TOOL---------------------------------------------+-+-'   
       +-SYSTEM-----------------------------------------------------+       
       +-USER-------------------------------------------------------+       
       +-REPLICATION------------------------------------------------+       
       |                     .-TABLE-.         .-FOR OPTIMIZATION-. |       
       '-CURRENT MAINTAINED--+-------+--TYPES--+------------------+-'

Description

ALL: Specifies that all possible types of maintained tables controlled by this special register, now and in the future, are to be considered when optimizing the processing of dynamic SQL queries.
NONE: Specifies that none of the object types that are controlled by this special register are to be considered when optimizing the processing of dynamic SQL queries.
FEDERATED_TOOL: Specifies that refresh-deferred materialized query tables that are maintained by a federated tool can be considered to optimize the processing of dynamic SQL queries, provided the value of the CURRENT QUERY OPTIMIZATION special register is 2 or greater than 5.
SYSTEM: Specifies that system-maintained refresh-deferred materialized query tables can be considered to optimize the processing of dynamic SQL queries. (Immediate materialized query tables are always available.)
USER: Specifies that user-maintained refresh-deferred materialized query tables can be considered to optimize the processing of dynamic SQL queries.
REPLICATION: Specifies that shadow tables can be considered to help optimize the processing of dynamic SQL queries.
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION: The value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register before this statement executes.
host-variable: A variable of type CHAR or VARCHAR. The length of the contents of the host variable must not exceed 254 bytes (SQLSTATE 42815). It cannot be set to null. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).
The characters of host-variable must be left-aligned. The contents of host-variable must be a string that is a comma-separated list of keywords matching what can be specified as keywords for the special register. These keywords must be specified in the exact case intended, because there is no conversion to uppercase characters. The value must be padded on the right with blanks if its length is less than that of the host variable.

Notes

The initial value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register is determined by the dft_mttb_types database configuration parameter, which has a default value of SYSTEM.
You must set the CURRENT REFRESH AGE special register to a value other than 0 for the specified table types to be considered during optimization of the processing of dynamic SQL queries.
If the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register includes anything other than REPLICATION or NONE, the value of the CURRENT REFRESH AGE special register must be either 0 or 99999999999999 (ANY).

Examples

Example 1: Set the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register.
```
   SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = SYSTEM, USER
```
Example 2: Retrieve the current value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register into a host variable called CURMAINTYPES.
```
   EXEC SQL VALUES (CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION)
     INTO :CURMAINTYPES
```
Example 3: Set the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register to have no value.
```
   SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = NONE
```