ROLLBACK_ TO_TIMESTAMP stored procedure

The ROLLBACK_TO_TIMESTAMP stored procedure is used to roll back a Datalake Iceberg table to a specific snapshot taken before the specified timestamp. The snapshot for the point in time to be rolled back to must be an ancestor of the current snapshot.

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

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

Syntax

The schema is SYSHADOOP.

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.

point_in_time

The timestamp that represents the point in time the table is to be rolled back to. The timestamp must be formatted as yyyy-MM-dd HH:mm:ss.SSS.

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 why it failed.

Example

CALL ROLLBACK_TO_TIMESTAMP ('SAMPLE', 'EMPLOYEE', '2024-04-22-06.20.53.220')

Result set 1

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

  STATUS_CODE STATUS_MESSAGE                                                                              

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

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

  1 record(s) selected.

  Return Status = 0