Dropping remote tables using transparent DDL

You can drop remote data source tables that were created through the federated database using transparent DDL. You cannot drop tables that were created directly at the remote data source.

Before you begin

The privileges held by the authorization ID of the transparent DDL statements must include at least one of the following:
  • SYSADM or DBADM authority
  • CREATETAB authority on the database and USE privilege on the table space as well as one of:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
    • CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema

To issue transparent DDL statements, your authorization ID must have the necessary privileges on the nickname (for the federated server to accept the request), and the comparable privileges on the remote data source server (for the data source to accept the request).

About this task

To drop a remote table that was created through the federated database using transparent DDL, you can use the DROP statement.

Dropping a nickname for a remote table created using transparent DDL merely drops the local nickname for that table. The DROP NICKNAME statement does not drop the remote table. You must use the DROP TABLE statement to drop the remote table.

Dropping a remote table first deletes the table on the data source, then deletes the corresponding nickname for the remote table in the federated database. Deleting the nickname invalidates any packages based on that nickname.

Restrictions

You cannot drop tables that were natively created at the remote data source.

Procedure

To drop a remote table, issue the DROP TABLE statement.
Example: To drop a table named SPALTEN, issue the following DROP statement: 
DROP TABLE SPALTEN
where SPALTEN is the local name for the remote table.