SET CURRENT REFRESH AGE statement

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

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

Description

numeric-constant

A DECIMAL(20,6) value that represents a time stamp duration.The value must be 0 - 99999999999999 or a valid time stamp within that range. The valid format for the range is yyyymmddhhmmss.nnnnnn, where:

• yyyy is the number of years and can have a value of 0 - 9999.
• mm is the number of months and can have a value of 0 - 11.
• dd is the number of days and can have a value of 0 - 30.
• hh is the number of hours and can have a value of 0 - 23.
• mm is the number of minutes and can have a value of 0 - 59.
• ss is the number of seconds and can have a value of 0 - 59.
• nnnnnn is the number of fractional seconds. The fractional seconds portion of the value is ignored and therefore can be any value.

You do not have to include the leading zeros for the entire value or the trailing fractional seconds. However, individual elements that have another element to the left must include the zeros. For example, to represent 1 hour, 7 minutes, and 5 seconds, use 10705.

If materialized query tables that are affected by the CURRENT REFRESH AGE special register are maintained by USER, SYSTEM, or FEDERATED_TOOL, the only valid numeric values are 0 and 99999999999999. For further details, see the "Notes" section.

ANY

A short form 99999999999999. See the description of the numeric-constant parameter.

host-variable

A variable of type DECIMAL(20,6) or another type that is assignable to DECIMAL(20,6). You cannot set the host-variable parameter to null. If the host variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815). The host-variable parameter must conform to the same constraints as the numeric-constant parameter.

Notes

• The initial value of the CURRENT REFRESH AGE special register is determined by the dft_refresh_age database configuration parameter, which has a default value of 0.
• Be careful when setting the CURRENT REFRESH AGE special register to a value other than 0. A table type that you specify for 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 special register value of 99999999999999 cannot be used in time stamp arithmetic operations, because the result would be outside the valid range for dates (SQLSTATE 22008).
• The CURRENT REFRESH AGE special register affects the materialized query tables that you define as REFRESH DEFERRED MAINTAINED BY USER, REFRESH DEFERRED MAINTAINED BY REPLICATION, and REFRESH DEFERRED MAINTAINED BY SYSTEM. The CURRENT REFRESH AGE special register affects these materialized query tables as follows:
  • If the value of the CURRENT REFRESH AGE special register is 0, the materialized query tables are not used to optimize the processing of a query.
  • If the value of the CURRENT REFRESH AGE special register is 99999999999999, the materialized query tables can be used to help optimize the processing of a query if both the following criteria are met:
    • The value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register includes these tables.
    • The value of the CURRENT QUERY OPTIMIZATION special register is 2 or a value that is greater than or equal to 5.
  • If the value of the CURRENT REFRESH AGE special register is a value other than 0 or 99999999999999, only shadow tables are affected by this special register setting can be used to optimize the processing of a query, but only if both the following criteria are met:
    • The value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register is REPLICATION.
    • The value of the CURRENT QUERY OPTIMIZATION special register is 2 or a value that is greater than or equal to 5.
• The CURRENT REFRESH AGE special register has no effect on whether REFRESH IMMEDIATE MAINTAINED BY SYSTEM materialized query tables or REFRESH DEFERRED MAINTAINED BY FEDERATED_TOOL materialized query tables are used to optimize the processing of a query.

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

  REFRESH DEFERRED MAINTAINED BY FEDERATED_TOOL materialized query tables are used for optimization if both the following criteria are met:

  • The value of the CURRENT QUERY OPTIMIZATION special register is 2 or a value that is greater than or equal to 5.
  • The value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register is ALL or includes FEDERATED_TOOL.

Examples

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

      SET CURRENT REFRESH AGE ANY

• Example 2: The following command retrieves the value of the CURRENT REFRESH AGE special register into a host variable called CURMAXAGE.

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