Checking DB2 special register settings

The settings of the CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES special registers can affect whether shadow tables are considered for query optimization. You must also set the query optimization level to 2 or a value that is greater than or equal to 5.

To check the special register settings:

1. Check the settings of the CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES special registers for the current connection by issuing the following VALUES statements:

   values(CURRENT REFRESH AGE)
   values(CURRENT MAINTAINED TABLE TYPES) 

   Before running a query, check the settings. These settings are applied at a connection level, so the values that are returned might not be the same as the settings that are applied to concurrent applications.
2. Check the value of the CURRENT REFRESH AGE special register that was in effect when a query was run by issuing the following query:

   SELECT
     stmt.EXECUTABLE_ID,
     ( SELECT substr(VALUE,1,24)
       FROM table(COMPILATION_ENV(COMP_ENV_DESC))
       WHERE NAME = 'REFRESH_AGE'
     ) as REFRESH_AGE,
     ( SELECT substr(VALUE,1,24)
       FROM table(COMPILATION_ENV(COMP_ENV_DESC))
       WHERE NAME = 'MAINTAINED_TABLE_TYPE'
     ) as MAINTAINED_TYPES
   from
     table(MON_GET_PKG_CACHE_STMT(null,null,null,-1)) AS stmt
   where
     STMT_TEXT = statement;

   The following is an edited version of the output that you might receive from running the preceding query:

   EXECUTABLE_ID                               REFRESH_AGE            MAINTAINED_TYPES
   ------------------------------------------- ---------------------- ----------------
   x'0100..7B00..0000020020140711112718053188' +00000000001000.000000 REPLICATION
   
     1 record(s) selected.