ROLLBACK_TO_SNAPSHOT stored procedure

The ROLLBACK_TO_SNAPSHOT stored procedure is used to roll back a Datalake Iceberg table to a specific snapshot ID. The snapshot to be rolled back to must be an ancestor of the current snapshot.

The snapshot can be specified using a snapshot ID or a named reference to a snapshot.

A list of snapshots created for a table can be queried using the TABLE_SNAPSHOTS table function.

The tags and branches defined for a table can be queried using the TABLE_SNAPSHOT_REFS table function.

Authorization

Execution of the ROLLBACK_TO_SNAPSHOT stored procedure is restricted to users with Db2 administrative privileges.

Syntax

snapshot

Description

schema_name

An input argument that specifies the Datalake Iceberg table schema name.

table_name

An input argument that specifies the name of the Iceberg table whose state is to be rolled back.

snapshot

An input argument that specifies the snapshot id the table is to be rolled back to. Options are:

snapshot_id
An input argument of type VARCHAR(20) that identifies the snapshot to which the table is rolled back to. This can be specified as a named parameter or as a constant.
ref
An input parameter of type VARCHAR(32672) which identifies a snapshot branch or tag to which the table is rolled back to. For information on snapshot branches and tags see, Iceberg Snapshot handling.

Output

A result set with two columns named STATUS_CODE and STATUS_MESSAGE. These columns contain values indicating the success or failure of the procedure. If the procedure succeeds, the STATUS_CODE is set to 0 and the STATUS_MESSAGE includes information related to the procedure results. If the procedure failed, the STATUS_CODE is set to -1 and the STATUS_MESSAGE includes an explanation indicating the reason for failure.

Examples

Rollback table SAMPLE.EMPLOYEE to a specific snapshot ID.

CALL ROLLBACK_TO_SNAPSHOT('SAMPLE', 'EMPLOYEE', 8158289932608565302)

  Result set 1

  --------------

  STATUS_CODE STATUS_MESSAGE                                                                              

  ----------- --------------------------------------------------------------------------------------------

            0 SAMPLE.EMPLOYEE rolled back to snapshot 8158289932608565302 taken at 2024-04-22 06:20:55.072

  1 record(s) selected.

  Return Status = 0

Rollback table SAMPLE.EMPLOYEE to a specific named reference.

db2 "CALL ROLLBACK_TO_SNAPSHOT('SAMPLE', 'EMPLOYEE', REF => 'release_one')"                                                                                                                                                                                

  Result set 1

  --------------

  STATUS_CODE STATUS_MESSAGE                                                                                                              

  ----------- ----------------------------------------------------------------------------------------------------------------------------

            0 SAMPLE.EMPLOYEE rolled back to snapshot 8158289932608565302 (named reference "release_one") taken at 2024-04-22 06:20:55.072

  1 record(s) selected.

  Return Status = 0