SQL Statements › DROP

DROP

Important! There is also a DROP PLAN statement that can only be submitted through DBSQLPR. See DROP PLAN (DBSQLPR).

To learn about using SQL keywords in CA Dataquery, see the CA Dataquery User Guide.

For information about the access rights required to execute this statement, see the CA Datacom/DB Database and System Administration Guide.

The DROP statement is a CA Datacom/DB extension. DROP removes an SQL-accessible object. The object's description is removed from the CA Datacom Datadictionary. Any application plans that reference the object are invalidated.

Important! When an object is dropped, any objects that are directly or indirectly dependent on that object are either dropped or marked for rebind.

When a table or view is dropped, the prepared statements in plans that reference the table or view are marked nonexecutable until rebound. An attempt to execute these statements invokes automatic rebind, and the rebind fails. If the plans containing referencing statements are currently executing or binding, the DROP request is aborted. This is always the case when the plan containing the DROP statement references the table or view, because this is an attempt to invalidate its own plan and is not allowed. Dropping an index causes all plans dependent on the indexed table to be marked invalid. You can run a CA Datacom Datadictionary Relationship Report to find out what plans are dependent on a table. See the CA Datacom Datadictionary Batch Reference Guide for information on Relationship Reports.

The DROP statement does not process and you receive a -118 return code when the CA Datacom Datadictionary entity-occurrence definition of the table, view, synonym, or index you specify is protected with a password or a Lock Level 1 or 2. The error message also includes a Datadictionary Service Facility (DSF) return code. The DSF return codes are:

• IPW (for password protected)
• IOR (for Lock Level 1 protected)
• NTF (for Lock Level 2 protected)

See Deleting SQL Objects for more information. See Preliminary Information—Lock Levels and to Datadictionary documentation for more information on passwords and lock levels.

Note: When the NOMAINT option of the CA Datacom/DB Utility (DBUTLTY) ACCESS function is in force, a DROP statement receives a CA Datacom/DB return code 94(87), where 87 is a decimal internal return code (hex 57) that tells you no maintenance statements are allowed while NOMAINT is in force.

Also note that the DROP statement is rejected if other DML statements (see SQL Statements) are in the same plan or if the table being dropped is in use by other plans.

With regard to table partitioning, DROP statements may not be issued against a table which is partitioned nor against a partition. For more information about table partitioning, see the CA Datacom/DB Database and System Administration Guide.

Following is the syntax diagram for the DROP statement:

►►─ DROP ─────────────────────────────────────────────────────────────────────►

 ►─┬─ TABLE ─┬───────────┬─ tab-name ──────────────────────────────┬──────────►◄
   │         └─ authid. ─┘                                         │
   ├─ SYNONYMsynonym-name ─────────────────────────────────────────┤
   ├─ VIEW ─┬───────────┬─ view-name ──────────────────────────────┤
   │        └─ authid. ─┘                                          │
   ├─ INDEX ─┬───────────┬index-nameFROM ─┬───────────┬─ tab-name ─┤
   │         └─ authid. ─┘                └─ authid. ─┘            │
   ├─┬────────────┬─ PROCEDURE proc-name ─┬──────────────────┬─────┤
   │ └─ SPECIFIC ─┘                       └─┬─ RESTRICT ◄ ─┬─┘     │
   │                                        ├─ INVALIDATE ─┤       │
   │                                        └─ CASCADE ────┘       │
   └─┬─ TRIGGER ─┬─ trig-name ─────────────────────────────────────┘
     └─ RULE ────┘

Note: DROP INDEX is not used by CA Dataquery.