SET CURRENT REFRESH AGE statement

The SET CURRENT REFRESH AGE statement changes the value of the CURRENT REFRESH AGE special register. It 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

                             .-=-.                         
>>-SET--CURRENT REFRESH AGE--+---+--+-numeric-constant-+-------><
                                    +-ANY--------------+   
                                    '-host-variable----'   

Description

numeric-constant

A DECIMAL(20,6) value representing a timestamp duration. The value must be 0 or 99 999 999 999 999 (the microseconds portion of the value is ignored and can therefore be any value).

ANY

This is a shorthand for 99 999 999 999 999.

host-variable

A variable of type DECIMAL(20,6) or another type that is assignable to DECIMAL(20,6). 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 value of host-variable must be 0 or 99 999 999 999 999.

Notes

• The initial value of the CURRENT REFRESH AGE special register is zero.
• The value of CURRENT REFRESH AGE is replaced by the specified value. The value must be 0 or 99 999 999 999 999. The value 99 999 999 999 999 represents 9999 years, 99 months, 99 days, 99 hours, 99 minutes, and 99 seconds.

  If the value of CURRENT REFRESH AGE is 0, the materialized query tables affected by this special register will not be used to optimize the processing of a query. If the value of CURRENT REFRESH AGE is 99 999 999 999 999, the materialized query tables affected by this special register can be used to optimize the processing of a query, but only if the value of CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register includes them, and the CURRENT QUERY OPTIMIZATION special register is set to 2 or a value greater than or equal to 5. The materialized query tables affected by this special register are REFRESH DEFERRED MAINTAINED BY USER and REFRESH DEFERRED MAINTAINED BY SYSTEM.

  REFRESH IMMEDIATE MAINTAINED BY SYSTEM materialized query tables can always be used to optimize the processing of a query if the CURRENT QUERY OPTIMIZATION special register is set to 2 or a value greater than or equal to 5.

  REFRESH DEFERRED MAINTAINED BY FEDERATED_TOOL materialized query tables are used for optimization if the CURRENT QUERY OPTIMIZATION special register is set to 2 or a value greater than or equal to 5, and the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register is set to ALL or includes FEDERATED_TOOL.

• Setting the CURRENT REFRESH AGE special register to a value other than zero should be done with caution. A table type specified by the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register might not represent the values of the underlying base table. If such a table is used to optimize the processing of a query, the query result might not accurately represent the data in the underlying table. This might be reasonable if you know that the underlying data has not changed, or if you are willing to accept a degree of error in the results, based on your knowledge of the cached data.
• The CURRENT REFRESH AGE value of 99 999 999 999 999 cannot be used in timestamp arithmetic operations, because the result would be outside of the valid range for dates (SQLSTATE 22008).

Examples

    SET CURRENT REFRESH AGE ANY

   EXEC SQL VALUES (CURRENT REFRESH AGE) INTO  :CURMAXAGE;