SET_TO_TIMESTAMP stored procedure

The SET_TO_TIMESTAMP stored procedure is used to set the current snapshot of a Datalake Iceberg table to a specific snapshot taken before the specified timestamp. The snapshot the table is to be set to can be any snapshot.

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

Authorization

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

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 SET_TO_TIMESTAMP('SAMPLE', 'EMPLOYEE', '2024-04-22-06.20.53.220')

Result set 1

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

  STATUS_CODE STATUS_MESSAGE                                                                      

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

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

  1 record(s) selected.

  Return Status = 0