Places a flag in a journal table that can be used to coordinate transaction recovery.
Required Privileges
- 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.
ANSI Compliance
CHECKPOINT is a Teradata extension to the ANSI SQL:2011 standard.
General Usage Notes
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.
Usage Notes for Stored Procedures and Embedded SQL
- 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.