The DROP statement removes an object at the current server. Except for storage groups, any objects that are directly or indirectly dependent on that object are deleted. Whenever an object is deleted, its description is deleted from the catalog at the current server, and any packages that refer to the object are invalidated.
The DROP VIEW statement removes the specified view, which was originally created by the CREATE VIEW statement. Because a view is purely a logical construct (an alias for a query) with no physical data behind it, DROP VIEW only involves changes to metadata in the metastore database, not any data files in HDFS.
Syntax
DROP VIEW [IF EXISTS] [db_name.]view_name
Statement type: DDL
Cancellation: Cannot be cancelled.
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
The following example creates a series of views and then drops them. These examples illustrate how views are associated with a particular database, and both the view definitions and the view names for CREATE VIEW and DROP VIEW can refer to a view in the current database or a fully qualified view name.
-- Create and drop a view in the current database. CREATE VIEW few_rows_from_t1 AS SELECT * FROM t1 LIMIT 10; DROP VIEW few_rows_from_t1; -- Create and drop a view referencing a table in a different database. CREATE VIEW table_from_other_db AS SELECT x FROM db1.foo WHERE x IS NOT NULL; DROP VIEW table_from_other_db; USE db1; -- Create a view in a different database. CREATE VIEW db2.v1 AS SELECT * FROM db2.foo; -- Switch into the other database and drop the view. USE db2; DROP VIEW v1; USE db1; -- Create a view in a different database. CREATE VIEW db2.v1 AS SELECT * FROM db2.foo; -- Drop a view in the other database. DROP VIEW db2.v1;
The DROP TABLE statement removes a table definition and all the table's data, metadata, and indexes. If you use the DELETE statement to remove all the rows in a table, the table still exists until it is removed with the DROP TABLE statement.
You cannot use the DROP TABLE statement to remove a table that is referenced by a foreign key constraint. You must drop the foreign key constraint first, and then remove the table. If you remove a table with indexed columns, then all the indexes are automatically removed. If you drop a composite index, then the index is removed for all the columns that are named in that index.
When specifying the DROP TABLE statement in the same submission as a CREATE TABLE statement, specify the FORCE option in the DROP TABLE statement. The DROP TABLE statement writes messages to the log when the specified table cannot be found. These messages can interfere with the CREATE TABLE statement. The FORCE option suppresses these messages so that the CREATE TABLE request can succeed.
The DELETE statement deletes only the rows (data) in a table. The DROP TABLE statement removes the table from the database completely, including the table structure.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
Table, table space, or index
Ownership of the object (for an index, the owner is the owner of the table or index), DBADM authority SYSADM or SYSCTRL authority, System DBADM, If the table space is in a database that is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
Db2 DROP statement
Sometimes, you may want to delete one or more unused tables from a database. To do this, you use the DROP TABLE statement as follows −
DROP TABLE [schema_name.]table_name;
First, specify the name of the schema to which the table belongs. The schema is optional. If you skip it, the statement will delete the specified table in the current schema. Second, specify the name of the table that you want to drop.
When you use the DROP TABLE statement to delete a table, Db2 performs the following actions, Delete all data in the table permanently. Delete all columns of the dropped table and the indexes associated with these columns. Mark all views that reference to the dropped table as inoperative. Also, mark all triggers that depend on the dropped table as inoperative. Revoke all privileges on the table and dependent views.