DROP DATALAKE TABLE statement

This statement is used to DROP a Datalake table.

Invocation

This statement can only be executed as a dynamic statement using EXECUTE IMMEDIATE. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

When dropping a Datalake table, the privileges held by the authorization ID of the statement must include at least one of the following authorities:

DROPIN privilege on the schema for the object
Owner of the object, as recorded in the OWNER column of the catalog view for the object
SCHEMAADM authority on the schema for the object
DBADM authority

Syntax

Description

table-name

Identifies the Datalake table that is to be dropped. The table-name must identify a table that is described in the catalog.

All constraints that are defined on the table are dropped. All views that reference the table are made inoperative. All packages which depend on any object dropped or marked inoperative will be invalidated.

IF EXISTS: Specifies that no error message is shown if the specified table name does not exist in the current database and schema.; Unless other conditions or dependencies prevent the drop operation, a successful message is returned even if no table is dropped. The condition for the failure is ignored if the table does not exist.

DELETE DATA

Indicates that the external data associated with the Datalake tables should be deleted as part of the DROP statement. This will only be successful if the Datalake table being dropped was defined with the external.table.purge TBLPROPERTIES value set to true. For Datalake Iceberg tables the gc.enabled property can optionally be used. Otherwise an error is returned.

The DELETE DATA clause indicates the tables' directories and files are to be removed when a table is dropped. The table's root directory, subdirectories and all data files associated with the table will be removed. If this clause is not specified, the table's directories and files will not be removed.

In order to use the DELETE DATA clause, set the table external.table.purge (or gc.enbaled property for Iceberg tables) property to true. If you use this clause without the PURGE clause, sch the enable trash bin support or set the table skip.trash or auto.purge properties to true. For more information on table properties, see Table Properties. Specifying the DELETE DATA clause without enabling trash bin support or setting one of these properties will cause the DROP statement to fail.

Deleted directories and files will be moved to the trash bin unless the PURGE clause is specified or the table's skip.trash or auto.purge property is set to true. For more information regarding trash bin support, see Datalake Table Trash Bin support.

PURGE

When this clause is specified in addition to the DELETE DATA clause, the table’s directories and files are permanently removed and cannot be recovered. .

Usage Notes

The DELETE DATA and PURGE options are not supported for externally managed Datalake tables. For information on externally managed tables, see Terminology.
When a table is dropped any other object that is dependent on the table are also affected. In addition, all statistics that were gathered on the table are also removed, and all privileges and authorizations that had been granted for access the table are also removed. See Dropping Tables for more information.

Examples

Drop a Datalake table named TDEPT. The table is removed from the Db2 catalog and the Db2 local metastore but the table’s directories but the directories and data files are not removed.
```
DROP DATALAKE TABLE TDEPT
```
Drop a Datalake table named TDEPT, if it exists. The table is removed from the Db2 catalog and the Db2 local metastore but the directories and data files are not removed.
```
DROP DATALAKE TABLE IF EXISTS TDEPT
```
Drop a Datalake table named TDEPT and move the table’s directories and files to the trash bin. The table needs to be defined to allow the data to be deleted, otherwise an error occurs. The trash bin feature also needs to be enabled, or an error will occur, unless the table is defined to skip the trash bin support.
```
DROP DATALAKE TABLE TDEPT DELETE DATA
```
Drop a Datalake table named TDEPT along with its data. The table needs to be defined to allow the data to be deleted, otherwise an error occurs. This statement will remove both the meta data and the data files associated with the table.
```
DROP DATALAKE TABLE TDEPT DELETE DATA PURGE
```