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.


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: