Datalake table Trash Bin support

The trash bin feature for Datalake tables helps prevent accidental deletion of files and directories when a Datalake table is dropped or when a partition is dropped by running the ALTER DATALAKE TABLE statement.

Important: The system administrator is responsible for enabling and managing the trash bin support and the trash data, including regular cleaning of the trash data if the feature is enabled. Db2 only provides the interface to enable and disable the Object Storage trash bin feature. By default, the featured is off (not enabled).

The trash bin feature enables support for DROP DATALAKE TABLE and ALTER DATALAKE TABLE operations, because the DELETE DATA clause can be specified without the PURGE option, which indicates that the data is to be removed from the table’s filepath and into a trash bin instead of being completely removed and unrecoverable.

When this feature is enabled, and the PURGE option is not used for DROP DATALAKE TABLE or ALTER DATALAKE TABLE operations, files to be deleted by these operations are not immediately removed from storage. Instead, the deleted files are moved to the /user/<instance-owner>/.Trash/Current directory, with their original filesystem path preserved.

Enabling and disabling trash bin support

By default, the trash bin support is not enabled. To enable this support, the fs.trash.interval configuration parameter must be set to a positive, non-zero value, which indicates how frequently the trash bin should be cleaned. Note that a fs.trash.interval of 0 means that the trash bin feature is disabled.

  • The configuration parameter fs.trash.interval can be configured using the SYSHADOOP.SET_DATALAKE_CONFIG stored procedure to make changes to configuration files. Any value other than 0 enables the trash bin feature.

    enable trash feature:

    • db2 "CALL SYSHADOOP.SET_DATALAKE_CONFIG ('CORE', 'fs.trash.interval', 1440)"

    disable trash:

    • db2 "CALL SYSHADOOP.SET_DATALAKE_CONFIG ('CORE', 'fs.trash.interval', null)"

Note: A restart of Big SQL is required in order to make the change effective.

Bypassing the trash bin

The auto.purge or skip.trash table property can be used to bypass the trash bin handling for a specific table. If this TBLPROPERTIES value is set to true, when a drop table or alter table drop partition is run with the DELETE DATA clause, the data will not be moved to the trash bin, and will be permanently deleted. See Table Properties, for more information on handling of table properties.