TABLE_SNAPSHOTS table function

The TABLE_SNAPSHOTS table function is used to retrieve information about the snapshots of a Datalake Iceberg table. The snapshot information can be queried and used for snapshot management, such as rolling back to a particular snapshot. The TABLE_SNAPSHOTS table function can be called by any user.

Authorization

Any user can execute this function.

Syntax

Read syntax diagramSkip visual syntax diagram TABLE_SNAPSHOTS ( schema_name , table_name )

The schema is SYSHADOOP.

Description

schema_name
An input argument of type VARCHAR(128) containing the name of the schema as stored in the Db2 catalog. This is a required parameter which identifies the schema name.
table_name
An input argument of type VARCHAR(128) containing the name of the table as stored in the Db2 catalog. This is a required parameter which identifies the table name.

Output

The function returns a table with one row per snapshot containing information about the snapshot. Each row contains the following columns:

Table 1.
Column Name Data type Description
SCHEMA_NAME VARCHAR (128) The schema name of the Datalake Iceberg table.
TABLE_NAME VARCHAR (128) The table name of the Datalake Iceberg table.
SEQNUM BIGINT The sequence number of the snapshot indicating the order that the snapshots were created.
CREATE_TS TIMESTAMP The timestamp of when the snapshot was created.
SNAPSHOT_ID BIGINT The ID of the snapshot.
PARENT_ID BIGINT The snapshot ID of the parent snapshot. If there is no parent then this is NULL.
SCHEMA_ID INT The ID of the schema that is used for the snapshot.
OPERATION VARCHAR (16) The operation done by the snapshot. Possible values are “append”, “replace”, “overwrite”, or “delete”.
IS_CURRENT CHAR(1) Specifies if the snapshot is the current snapshot for the table. The value is either ‘Y’ or ‘N’.

Examples

SELECT SNAPSHOT_ID, PARENT_ID, CREATE_TS + 1000 microseconds, IS_CURRENT FROM TABLE(TABLE_SNAPSHOTS('SAMPLE', 'EMPLOYEE'))      

SNAPSHOT_ID          PARENT_ID            3                          IS_CURRENT
-------------------- -------------------- -------------------------- ----------
 3529959585658467630                    - 2024-07-17-05.42.29.100000 N         
   64002736169578712  3529959585658467630 2024-07-17-05.42.29.798000 N         
 5181664547719285448    64002736169578712 2024-07-17-05.42.30.491000 N         
 6342598394395523974  5181664547719285448 2024-07-17-05.42.31.165000 N         
 3238949612550447538  6342598394395523974 2024-07-17-05.42.31.881000 N         
 1219068768450651942  3238949612550447538 2024-07-17-05.42.41.216000 Y         

  6 record(s) selected.