SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-33
Deleting Columns
Steps for Dropping Partitions
This example drops an empty partition of a key-sequenced table:
>> ALTER TABLE $VOL1.SALES.CUSTOMER
+> DROP PARTITION $VOL5.SALES.CUSTOMER;
--- SQL operation complete.
To drop a partition, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine the name of the table or index from which you want to drop the partition.
3. Determine if the partition is empty by using the FILEINFO command to check the
end-of-file indicator for the partition.
4. Determine which programs depend on the table by using the DISPLAY USE OF
command. These programs will be invalidated.
5. Enter the ALTER TABLE or ALTER INDEX statement with the DROP PARTITION
specification.
6. SQL compile the invalidated programs.
If you plan to use the TMF subsystem for recovering an audited SQL table or index,
Recovering Purged SQL Tables on page 11-14 before proceeding.
Deleting Columns
Deleting columns from a table is not allowed. If you want to prevent access to a column
of a table, you might create a protection view of the table, excluding the column you
want to drop. This method does not physically alter the table structure but essentially
masks the unwanted column. This method can work only if the excluded column is
defined with a default value. If the excluded column is defined with the NO DEFAULT
clause, no user can perform update or insert operations through the view.
To physically delete a column, you must create a new table as follows:
1. Rename the old table and create a new table definition, excluding the columns you
do not want in the new table.
2. After creating the new table, load the old table’s data into the new table with the
LOAD or COPY command, eliminating the missing columns.
3. After the LOAD or COPY operation completes, drop the old table.
You cannot drop columns from views or indexes. To remove a column from a view or
index, you must drop the existing object and create a new object, excluding any
unwanted columns.
For more information, see Altering Columns on page 7-26.