CHECKPOINT

The CHECKPOINT statement causes an ALLBASE/SQL system checkpoint to be taken.

Scope

ISQL or Application Programs

SQL Syntax

  CHECKPOINT [:HostVariable
              :LocalVariable
              :ProcedureParameter]

Parameters

HostVariable: identifies an output host variable used to communicate the amount of log space available for use. The host variable is an integer.
LocalVariable: contains a value in a procedure.
ProcedureParameter: contains a value that is passed into or out of a procedure.

Description

Specifying a host variable with CHECKPOINT statement in an application allows you to determine how much free space is available in the log file.
The LocalVariable parameter is used in the stored procedure for obtaining free log space.
When you can use the host variable in a CHECKPOINT statement in an application program or procedure, the host variable can be omitted if you don't need to know the number of free blocks available.
When you enter a CHECKPOINT statement interactively in ISQL, you cannot specify a host variable. Returned information is displayed on the screen.
Checkpoint processing is as follows:
- Contents of the log buffers are written to the log files(s).
- Data buffers containing changed pages are written to DBEFiles.
- A checkpoint record containing a list of the transactions currently in progress is written in the log.
- When nonarchive logging is in effect, space containing log records written prior to the beginning of the oldest incomplete transaction is made available for reuse. When archive logging is in effect, however, this step is skipped and no log file space is recovered by checkpoints.
- For a brief interval while a checkpoint is being taken, SQL statements that modify the DBEnvironment continue to be accepted but their processing is temporarily suspended. This suspension occurs for the amount of time needed to write the log buffers and changed pages to permanent storage. Retrieval from the DBEnvironment is not suspended during a checkpoint.
Contents of the log buffer are also written to the log file(s) when a COMMIT WORK is executed.
When you submit a START DBE statement, ALLBASE/SQL processes all log records created since the last checkpoint record. Therefore taking a checkpoint just before stopping the DBE reduces the amount of time that is needed when a DBEnvironment is started up.
ALLBASE/SQL automatically takes a checkpoint when the log file is full, when the data buffer is full, and when the STOP DBE and COMMIT ARCHIVE statements are processed. When the START DBE statement is processed, ALLBASE/SQL writes a checkpoint record.
Submitting a CHECKPOINT statement allows you to determine how much free space is available in the log file.

Authorization

You must have DBA authority to use this statement.

Example

A stored procedure retrieves the number of free blocks of log space available. Create a stored procedure with an output parameter.

      EXEC SQL create procedure cp (freeblock integer OUTPUT) as
               begin
                checkpoint :freeblock;
               end;

Pass the host variable as an output parameter to procedure.

      EXEC SQL execute procedure cp (hstfblk output);
 
      writeln('free log space available', hstfblk);
      if hstfblk <= TOOLOW then 
      writeln('Add new log files ');

A log block is a 512-byte allocation of storage. When you submit the CHECKPOINT statement interactively, ISQL displays the amount of log space available for use.

   isql=> CHECKPOINT;
   Number of free log blocks is 240
   isql=>

ISQL assigns and displays the free log space.

A program retrieves the number of free blocks of log space available. In a Pascal application program, declare a host variable.

      EXEC SQL begin declare section;
        hstfblk : integer;
      EXEC SQL end declare section;

Submit a checkpoint with host variable to obtain free log space available.

      EXEC SQL checkpoint :hstfblk;
 
      writeln('free log space: ',hstfblk);
      if hstfblk <= TOOLOW then 
      writeln('Add new log files ');