Dropping a Table

The DROP TABLE statement removes the table from the schema, obsoletes the table from the CA Datacom Datadictionary, and removes the table from the CA Datacom/DB Directory. You must be the creator of the table, or have the authority to drop the table.

Enter the DROP TABLE statement in the numbered area of an EDIT Source Member Panel. See How to Submit SQL Statements for the steps to obtain the panel and an explanation of the fields on the panel.

Specifying Authorization ID

To be certain you are dropping a desired table, qualify the table name with the authorization ID in the DROP statement. If you do not specify the authorization ID, CA Datacom Datadictionary uses the current default authorization ID for the online session.

Impact

The results of processing the DROP TABLE have a far reaching impact.

The table data is deleted and the space is reclaimed.
All views and synonyms based on the table are dropped.
All application plans and statements that reference the table are invalidated.
All columns, keys, elements, and support data associated with the table are also obsoleted (columns appear as FIELD occurrences in CA Datacom Datadictionary ). The support data includes aliases, descriptors, relationship definitions, and text (SQL comments and text added through CA Datacom Datadictionary ).
If the table definition also exists in TESTor HIST status in CA Datacom Datadictionary, those substructure status/versions, including their elements, keys and fields, are deleted at the same time as the PRODuction status.

Example Source Member

The following example shows the DROP TABLE statement. See How to Submit SQL Statements for the steps to obtain the panel and an explanation of the fields on the panel.

 =>
 =>
 =>
 
 -------------------------------------------------------------------------------
 Interactive SQL Service Facility                                       SQLMAINT
 
                                 Source Panel                               S01S
            EDIT     Member: DTABLE    Output Line Limit: 01000
                     Person: JONES
             Current Authid: JONES
                Description: DROP TABLE DEPTTBL
 -------------------------------------------------------------------------------
 ====== ========================== T O P =======================================
 000001 drop table jones.depttbl;
 ====== ======================= B O T T O M ====================================
 
 
 
 
 
 PF1=HELP         PF2=END         PF3=SPLIT         PF4=PROCESS
 PF5=TOP          PF6=BOTTOM      PF7=BACKWARD      PF8=FORWARD
 PF9=EXECUTE      PF10=LEFT       PF11=RIGHT        PF12=ALTERNATE

The DROP statement is required, as is the keyword TABLE. The keyword must be followed by the name of the object you are dropping.

Once this source member is executed, you cannot recall the table definition. If you want to use this table again, you must re-create it.

See DROP for more information on the syntax of the DROP statement.

After placing your SQL statement in the numbered line area on the Source Panel, you can perform the following:

Enter additional SQL statements. Place a semicolon (;) after each complete statement.
Press PF9 (EXECUTE) to execute the source member. CA Datacom Datadictionary responds with the Output Panel which shows the results of the SQL processing.
Press PF2 (END) to obtain an SQLMAINT Panel where you can select another option.
Press the Clear key to return to the Interactive SQL Service Facility Panel where you can choose another option.

Example Output Member

When you execute the statement, you receive a Output Panel similar to the following example:

 =>
 =>
 =>
 
 --------------------------------------------------------------------------- >>>
 Interactive SQL Service Facility                                       SQLMAINT
 
                                 Output Panel                               S01O
              EDIT     Member= DTABLE
                  Description: DROP TABLE DEPTTBL
 -------------------------------------------------------------------------------
 ====== ========================== T O P =======================================
 000001 (OUTPUT CREATED FROM SOURCE MEMBER DTABLE  )
 000002 drop table jones.depttbl;
 000003 ---------+---------+---------+---------+---------+---------+---------+--
 000004 STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
 000005 ---------+---------+---------+---------+---------+---------+---------+--
 000006 SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
 000007 NUMBER OF INPUT RECORDS READ IS 0001
 000008 NUMBER OF SQL STATEMENTS PROCESSED IS 0001
 ====== ======================= B O T T O M ====================================
 PF1=HELP         PF2=END         PF3=SPLIT         PF4=PROCESS
 PF5=TOP          PF6=BOTTOM      PF7=BACKWARD      PF8=FORWARD
 PF9=EXECUTE      PF10=LEFT       PF11=RIGHT        PF12=ALTERNATE

To exit from the Output Panel, you can perform one of the following:

Press PF2 (END) to obtain an SQLMAINT Panel where you can select another option.
Press PF12 (ALTERNATE) to display the Source Panel.
Press the Clear key to return to the Interactive SQL Service Facility Panel.