TABLE_SNAPSHOT_REFS table function
The TABLE_SNAPSHOT_REFS table function is used to retrieve information about the snapshot references of a Datalake Iceberg table. The snapshot reference information can be queried and used for snapshot management, such as rolling back to a particular snapshot. The TABLE_SNAPSHOTS_REFS table function can be called by any user.
Authorization
Any user can execute this function.
Syntax
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:
| 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. |
| NAME | VARCHAR(32672) | |
| SNAPSHOT_ID | BIGINT | The ID of the snapshot. |
| TYPE | VARCHAR(6) | The type of reference which is either branch or tag. |
| MAX_REFERENCE_AGE_IN_MS | BIGINT | Specifies the maximum reference age retention property in milliseconds |
| MIN_SNAPSHOTS_TO_KEEP | INT | Specifies the number of snapshots to retain for the referenced snapshot. |
| MAX_SNAPSHOT_AGE_IN_MS | BIGINT | Specifies the maximum age retention property in milliseconds for the referenced snapshot. |
Examples
SELECT CAST(NAME AS VARCHAR(30)) NAME, SNAPSHOT_ID, TYPE FROM TABLE(TABLE_SNAPSHOT_REFS('SAMPLE', 'EMPLOYEE'))
NAME SNAPSHOT_ID TYPE
------------------------------ -------------------- ------
test-point 6342598394395523974 TAG
main 1219068768450651942 BRANCH
release_one 1219068768450651942 TAG
3 record(s) selected.