ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 317
SQL Statements A - D
CHECKPOINT
• 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.