Icon SET STATEMENT CACHE

Sets the statement cache size within a job.

Syntax
SET STATEMENT CACHE TO <NumStatements>

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

Information This statement will override any existing statement 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 statements is more than we need, but isn't wasteful
   SET STATEMENT 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 statement is the one that we're interested in caching
         EXECUTE IMMEDIATE 'INSERT INTO BackupLog(ExecTime) Values (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