SET_TO_SNAPSHOT stored procedure

The SET_TO_SNAPSHOT stored procedure is used to set the current snapshot for a Datalake Iceberg table to a specific snapshot. The snapshot the table is to be set to can be any snapshot. The snapshot can be specified using a snapshot ID or a named reference to a snapshot.

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

Syntax

snapshot

Description

schema_name

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

table_name

The name of the Iceberg table whose state is to be set to.

snapshot

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

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

Examples

Set the current snapshot for table SAMPLE.EMPLOYEE to a specific snapshot ID.

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

  Result set 1

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

  STATUS_CODE STATUS_MESSAGE                                                                      

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

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

  1 record(s) selected.

  Return Status = 0

Set the current snapshot for table SAMPLE.EMPLOYEE to a specific named reference.

CALL SET_TO_SNAPSHOT('SAMPLE', 'EMPLOYEE', REF => 'release_one')                                         

  Result set 1

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

  STATUS_CODE STATUS_MESSAGE                                                                                                      

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

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

  1 record(s) selected.

  Return Status = 0