Triggers to flush the optimization profile cache

The following SQL procedure and triggers must be created before automatic flushing of the profile cache can occur.

   CREATE PROCEDURE SYSTOOLS.OPT_FLUSH_CACHE( IN SCHEMA VARCHAR(128),
                                              IN NAME VARCHAR(128) )
   LANGUAGE SQL
   MODIFIES SQL DATA
   BEGIN ATOMIC
    -- FLUSH stmt (33) + quoted schema (130) + dot (1) + quoted name (130) = 294
    DECLARE FSTMT VARCHAR(294) DEFAULT 'FLUSH OPTIMIZATION PROFILE CACHE '; --

    IF NAME IS NOT NULL THEN
      IF SCHEMA IS NOT NULL THEN
        SET FSTMT = FSTMT || '"' || SCHEMA || '".'; --
      END IF; --

      SET FSTMT = FSTMT || '"' || NAME || '"'; --

      EXECUTE IMMEDIATE FSTMT; --
    END IF; --
   END;

   CREATE TRIGGER SYSTOOLS.OPT_PROFILE_UTRIG AFTER UPDATE ON SYSTOOLS.OPT_PROFILE
   REFERENCING OLD AS O
   FOR EACH ROW
     CALL SYSTOOLS.OPT_FLUSH_CACHE( O.SCHEMA, O.NAME );

   CREATE TRIGGER SYSTOOLS.OPT_PROFILE_DTRIG AFTER DELETE ON SYSTOOLS.OPT_PROFILE
   REFERENCING OLD AS O
   FOR EACH ROW
     CALL SYSTOOLS.OPT_FLUSH_CACHE( O.SCHEMA, O.NAME );