Icon SET PROCEDURE CACHE

Sets the function/procedure cache size within a job.

Syntax
SET PROCEDURE CACHE TO <NumStatements>

Usage
Use this statement to set the function/procedure cache size within a job. The default function/procedure cache size in a job is 0, which results in no function/procedure caching at all. Please see the Buffering and Caching topic for more information on the function/procedure caching functionality in ElevateDB.

Information This statement will override any existing function/procedure cache size set for the session via client-specific connection parameters, so it is normally recommended that you only use this statement within the body of jobs, which use unique sessions per job execution. Please see the CREATE JOB statement for more information on how to create a job.

Examples
-- The following job backs up all tables in all databases
-- defined in the current system at 11:00 PM every evening.

CREATE JOB Backup
RUN AS "System"
FROM DATE '2006-01-01' TO DATE '2010-12-31'
DAILY
BETWEEN TIME '11:00 PM' AND TIME '11:30 PM'
CATEGORY 'Backup'
BEGIN
   DECLARE DBCursor CURSOR FOR DBStmt;
   DECLARE DBName VARCHAR DEFAULT '';

   -- 8 procedures is more than we need, but isn't wasteful
   SET PROCEDURE CACHE TO 8;

   PREPARE DBStmt FROM 'SELECT * FROM Databases';

   OPEN DBCursor;

   FETCH FIRST FROM DBCursor ('Name') INTO DBName;

   WHILE NOT EOF(DBCursor) DO
      IF (DBName <> 'Configuration') THEN
         EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' +
                           CAST(CURRENT_DATE AS VARCHAR(10)) +
                           '-' + DBName + '" TO STORE "Backups" INCLUDE CATALOG';
         -- This next call is the one that we're interested in caching
         CALL LogBackup(CURRENT_TIMESTAMP);
      END IF;
      FETCH NEXT FROM DBCursor ('Name') INTO DBName;
   END WHILE;

   CLOSE DBCursor;
END

Required Privileges
Any user can execute this statement.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
ExtensionThis SQL statement is an ElevateDB extension.
Image