 --#SET TERMINATOR @
 SET CURRENT SCHEMA LOGGER_1A @
 
+-- Constanct internalCache
+ALTER MODULE LOGGER ADD
+  VARIABLE INTERNAL_CACHE ANCHOR LOGDATA.CONFIGURATION.KEY CONSTANT 'internalCache' @
+
 -- Constant logInternals
 ALTER MODULE LOGGER ADD
   VARIABLE LOG_INTERNALS ANCHOR LOGDATA.CONFIGURATION.KEY CONSTANT 'logInternals' @

    END IF;
   END;
   -- Creates an prepares a dynamic query.
   SET STMT = 'SELECT LAST_REFRESH AS LAST_REFRESH, '
     || SECS || ' AS FREQUENCY, '
     || 'CASE ' || SECS || ' '
     || 'WHEN -1 THEN ''Not defined'' '
     || 'ELSE CHAR(LAST_REFRESH + ' || SECS || ' SECONDS) '
     || 'END AS NEXT_REFRESH '
     || 'FROM SYSIBM.SYSDUMMY1';
   PREPARE RS FROM STMT;
   OPEN REF;

  SECURED
 F_GET_VAL: BEGIN
  DECLARE RET ANCHOR LOGDATA.CONFIGURATION.VALUE;
  DECLARE SECS SMALLINT DEFAULT 60;
  DECLARE EPOCH TIMESTAMP;

  -- Checks if internal cache should be used.
  IF (CACHE = TRUE) THEN

   -- Sets the quantity of seconds before refresh. 60 seconds by default.
   REFRESH: BEGIN
    -- Handle for the second time the function is called and the param has not
    -- been defined.
    DECLARE CONTINUE HANDLER FOR SQLSTATE '2202E'
      SET SECS = 60;
    SET SECS = INT(CONFIGURATION[REFRESH_CONS]);
    IF (SECS IS NULL) THEN
     SET SECS = 60;
    END IF;
   END REFRESH;

   -- Sets a reference date 1970-01-01.
   SET EPOCH = DATE(719163);
   IF (COALESCE(LAST_REFRESH, EPOCH) + SECS SECONDS < CURRENT TIMESTAMP OR LOADED = FALSE) THEN
    BEGIN
     DECLARE VALUE ANCHOR LOGDATA.CONFIGURATION.VALUE;
     DECLARE KEY ANCHOR LOGDATA.CONFIGURATION.KEY;
     DECLARE AT_END BOOLEAN; -- End of the cursor.
     DECLARE SIZE SMALLINT;
     DECLARE POS SMALLINT;
     DECLARE CONF CURSOR FOR
       SELECT KEY, VALUE
       FROM LOGDATA.CONFIGURATION;
     DECLARE CONTINUE HANDLER FOR NOT FOUND
       SET AT_END = TRUE;

     -- Clears the current configuration.
     SET SIZE = CARDINALITY(CONFIGURATION_KEYS);
     IF (SIZE >= 1) THEN
      SET POS = 1;
      WHILE (POS < SIZE) DO
       SET KEY = CONFIGURATION_KEYS[POS];
       SET CONFIGURATION[KEY] = NULL;
       SET CONFIGURATION_KEYS[POS] = NULL;
       SET POS = POS + 1;
      END WHILE;
     END IF;
     -- Reads current configuration.
     SET POS = 1;
     SET AT_END = FALSE;
     OPEN CONF;
     FETCH CONF INTO KEY, VALUE;
     WHILE (AT_END = FALSE) DO
      SET CONFIGURATION_KEYS[POS] = KEY;
      SET CONFIGURATION[KEY] = VALUE;
      SET POS = POS + 1;
      FETCH CONF INTO KEY, VALUE;
     END WHILE;
     -- Sets the last configuration read as now.
     SET LAST_REFRESH = CURRENT TIMESTAMP;
    END;
    -- Checks the variable for internal cache
    BEGIN
     -- NULL if the key is not in the configuration.
     DECLARE EXIT HANDLER FOR SQLSTATE '2202E'
       SET CACHE = TRUE;

     SET CACHE = CASE CONFIGURATION[INTERNAL_CACHE] 
       WHEN 'true' THEN TRUE 
       ELSE FALSE
       END;
    END;
    SET LOADED = TRUE;
   END IF;

   BEGIN
    -- NULL if the key is not in the configuration.
    DECLARE EXIT HANDLER FOR SQLSTATE '2202E'
      SET RET = NULL;

    SET RET = CONFIGURATION[GIVEN_KEY];
   END;
  ELSE -- Does not use the internal cache but a query.
   -- Checks the cache status.
   SELECT C.VALUE INTO RET
     FROM LOGDATA.CONFIGURATION C
     WHERE C.KEY = INTERNAL_CACHE;
   SET CACHE = CASE RET
     WHEN 'true' THEN TRUE
     ELSE FALSE
     END;
   SELECT C.VALUE INTO RET
     FROM LOGDATA.CONFIGURATION C
     WHERE C.KEY = GIVEN_KEY;
  END IF;

  RETURN RET;
 END F_GET_VAL @

