SQL Reference

SET CURRENT REFRESH AGE

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

No authorization is required to execute this statement.

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).

0

Indicates that only summary tables defined with REFRESH IMMEDIATE may be used to optimize the processing of a query.

99999999999999

Indicates that any summary tables defined with REFRESH DEFERRED or REFRESH IMMEDIATE may be used to optimize the processing of a query. This value represents 9 999 years, 99 months, 99 days, 99 hours, 99 minutes, and 99 seconds.

ANY

This is a shorthand for 99999999999999.

host-variable

A variable of type DECIMAL(20,6) or other 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 the host-variable must be 0 or 99 999 999 999 999.000000.

Notes

• The initial value of the CURRENT REFRESH AGE special register is zero.
• Setting the CURRENT REFRESH AGE special register to a value other than zero should be done with caution. By allowing a summary table that may not represent the values of the underlying base table to be used to optimize the processing of the query, the result of the query may NOT accurately represent the data in the underlying table. This may be reasonable when you know the underlying data has not changed or you are willing to accept the degree of error in the results based on your knowledge of the data.
• The CURRENT REFRESH AGE value of 99 999 999 999 999 cannot be used in timestamp arithmetic operations since the result would be outside the valid range of dates (SQLSTATE 22008).

Examples

Example 1:  The following statement sets the CURRENT REFRESH AGE special register.

    SET CURRENT REFRESH AGE ANY

Example 2: 

The following example retrieves the current value of the CURRENT REFRESH AGE special register into the host variable called CURMAXAGE.

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

The value would be 99999999999999.000000, set by the previous example.