SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

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

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-------------+   
                                +-SYSTEM-----------+   
                                +-+-SESSION_USER-+-+   
                                | '-USER---------' |   
                                '-host-variable----'

Description

The value indicates which materialized query tables that are enabled for optimization are considered when optimizing the processing of dynamic SQL queries.

ALL: Indicates that all materialized query tables will be considered.
NONE: Indicates that no materialized query tables will be considered.
SYSTEM: Indicates that only system-maintained materialized query tables that are refresh deferred will be considered.
SESSION_USER or USER: Indicates that only user-maintained materialized query tables that are refresh deferred will be considered.
host-variable: A variable of type CHAR or VARCHAR. The length of the contents of host-variable must not exceed 255 bytes. 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.
The characters of host-variable must be left justified. The content of the host variable must be a string that would match what can be specified as keywords for the special register in the exact case intended as there is no conversion to uppercase characters.

Notes

The CURRENT REFRESH AGE special register needs to be set to a value other than zero in order for the specified types of objects to be considered for optimizing the processing of dynamic SQL queries.

The CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register affects dynamic statement cache matching.

Examples

Example 1: The following statement sets the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register:

   SET CURRENT MAINTAINED TABLE TYPES ALL;

Example 2: The following example retrieves the current value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register into the host variable called CURMAINTYPES.

   EXEC SQL VALUES (CURRENT MAINTAINED TABLE TYPES) INTO :CURMAINTYPES;

The value would be ALL if set by the previous example.

Example 3: The following example resets the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register so that no materialized query tables can be considered to optimize the processing of dynamic SQL queries.

  SET CURRENT MAINTAINED TABLE TYPES NONE;