CHECKPOINT - Teradata Database

Places a flag in a journal table that can be used to coordinate transaction recovery.

where:

Syntax Element …	Specifies …
table_name	the journal table that is to be marked with the checkpoint entry.
checkpoint_name	a label that can be used to reference the checkpoint entry in database recovery activities. checkpoint_name should be unique. If checkpoint_name duplicates an existing entry in the journal, then you can qualify it with the system‑assigned event number. If you do not specify checkpoint_name, then the checkpoint entry must be referenced in recovery activities by its event number.

where:

Syntax element …	Specifies …
database_name	the name of the database for which a checkpoint flag is to be created. The PERIOD character after the name is required.
table_name	the name of the table for which a checkpoint flag is to be created.
checkpoint_label	an SQL identifier that labels the checkpoint.
label_variable	a host variable that contains a label for the checkpoint. The colon is required.
host_variable_name	the name of a host variable to contain the checkpoint flag. The preceding colon is optional.
host_indicator_variable_name	the name of an optional host indicator variable to receive nulls.

CHECKPOINT is a Teradata extension to the ANSI SQL:2011 standard.

To checkpoint a journal table, you must meet at least one of the following criteria.

Have CHECKPOINT privilege on the journal table.

Have CHECKPOINT privilege on the database containing the journal table.

Be an owner of the database containing the journal table.

Be an immediate or indirect owner of the journal table.

CHECKPOINT causes the system to place a READ lock on all data tables that write journal images to the table named in the CHECKPOINT request. This lock causes any new transactions to wait until the checkpoint operation is complete. It also causes the checkpoint operation to await the completion of outstanding update transactions.

This action guarantees that the checkpoint saved in the journal represents a clean point in the transaction environment. When the checkpoint operation completes, the system releases the locks.

The system assigns an event number to each checkpoint entry. This number can be returned as a result of CHECKPOINT request processing; it is also stored, along with other information about request execution, in a data dictionary table. You can review the table data through the DBC.EventsV system view.

If an explicit transaction or a multistatement request contains a CHECKPOINT request, then that CHECKPOINT request must precede any INSERT, UPDATE, or DELETE requests in the transaction or request.

The following rules apply to CHECKPOINT:

The stored procedure and embedded SQL form of CHECKPOINT is a data returning request.

Although CHECKPOINT is a data returning request, it cannot be associated with a selection cursor.

Whether specified as checkpoint_label or as label_host_variable, the checkpoint label must be a valid SQL identifier.

If you specify label_host_variable, the host variable must follow the rules for SQL strings for the client language and must be preceded by a colon. For details, see SQL Stored Procedures and Embedded SQL.

The main host variable identified by host_variable_name must be a type that conforms to INTEGER.

CHECKPOINT cannot be performed as a dynamic SQL statement.

CHECKPOINT causes a synchronization point to be generated and recorded in the journal table specified by table_name.

If you specify the NAMED clause, the checkpoint label is associated with the synchronization point. A 32-bit integer that uniquely identifies the synchronization point is returned into the main host variable defined in the host variable specification.

CHECKPOINT is not valid in embedded SQL when you specify the TRANSACT(2PC) option to Preprocessor2.

The following request can be used to place a checkpoint entry into a journal table:

     CHECKPOINT account_db.jnl_table, NAMED daily_pay_maint;

When this request is executed, all update activity is ceased on tables that write journal images to the journal table, (jnl_table) and an event number is returned.

A row containing the daily_pay_maint label is placed into the journal table. The label can then be used in rollforward or rollback operations.