DROP COLUMN

To drop columns from an existing table, use the DROP COLUMN setting with the ALTER TABLE command.

In this command, the syntax for dropping columns is as follows:

DROP [COLUMN] column_name

You cannot drop a column if the column is a primary key.

When you drop a column, a considerable amount of preliminary action is required to identify and remove any external dependencies on the column. You must search for any references to the column within triggers, procedures, views, and restriction filters by querying the relevant database tables. You must also search your probe rules files and gateway mapping files for references to the column.

Attention:

If you drop a column on which triggers, procedures, views, restriction filters, or indexes depend, these dependent objects are also deleted, and a warning is written to the ObjectServer log file. To avoid inadvertently deleting triggers, procedures, views, or restriction filters, read the following guidelines for dropping columns. (Because indexes are directly linked to columns, indexes are always deleted when their associated columns are dropped).
ObjectServers before OMNIbus v8.1. Fix Pack 8. If you drop a field on alerts.status, you must restart all probes and remove any SAF files after you drop the field.
ObjectServers at OMNIbus v8.1. Fix Pack 8 or later. Probes now are resilient to field drops on alerts.status, and no longer require a restart or removal of SAF files.

The following guidelines are based on an example scenario where you want to drop the Country column from a table in your ObjectServer:

Connect to your ObjectServer (for example, OWL) by using the SQL interactive interface, as shown in the following table. Your user name is assumed by default, but you must enter your password.
Table 1. Starting the SQL interactive interface

Option Description

UNIX Enter:
$NCHOME/omnibus/bin/nco_sql -server OWL

Windows Enter:
%NCHOME%\omnibus\bin\isql -S OWL
Back up your ObjectServer to a temporary location (for example, /tmp/mybackup) by using the ALTER SYSTEM BACKUP command. This precautionary measure ensures that you can restore your system, if required.
```
1> alter system backup '/tmp/mybackup';
2> go
```
List details of your triggers, as stored in the catalog.triggers table.
```
1> describe catalog.triggers;
2> go
```
The type of key, name, data type, and length of each column in the table are output to the screen.
Retrieve the names of all triggers that reference the Country column in the body or the evaluate clause of the trigger:
```
1> select TriggerName from catalog.triggers where CodeBlock like ' Country' or EvaluateBlock like ' Country';
```
Make a note of all listed triggers and remove the Country references by editing each trigger. You can do this from the Trigger Details window (Action tab) in the Netcool/OMNIbus Administrator.
Important: Before you make any of the changes that are described in the remaining steps of this procedure, check for the following conditions.
- There might be two or more tables that use the same column name in your schema. If so, identify the correct triggers for the table that you want to drop before you make any changes.
- Check there are no dependencies between triggers that might result in unwanted changes.

Table 1. Starting the SQL interactive interface
Option	Description
UNIX	Enter: `$NCHOME/omnibus/bin/nco_sql -server OWL`
Windows	Enter: `%NCHOME%\omnibus\bin\isql -S OWL`

Repeat steps 3 - 5 to identify any other objects that reference the Country column, and to remove all instances of the reference. The following table lists the database tables that you need to search, the relevant SELECT statements, and the Netcool/OMNIbus Administrator windows that you can use to edit the object.

Table 2. System catalog tables to be searched, SELECT statements, and Netcool/OMNIbus Administrator windows
Object type	Table name	SELECT statement	Netcool/OMNIbus Administrator window
Procedures	catalog.sql_procedures	`select ProcedureName from catalog.sql_procedures where CodeBlock like 'Country';`	SQL Procedure Details window
Restriction Filters	catalog.restrictions	`select RestrictionName from catalog.restrictions where ConditionText like 'Country';`	Restriction Filter Details window
Views	catalog.views	`select ViewName from catalog.views where CreationText like 'Country';`

Search your probe rules files and remove any references to the column.
Search your gateway mapping files $NCHOME/omnibus/gates/objserv_type/objserv_type.map, where type represents uni or bi. Remove any references to the column.
For ObjectServers before OMNIbus v8.1. Fix Pack 8, stop the probes. This step is not necessary for ObjectServers at OMNIbus v8.1. Fix Pack 8 or later.
After all the references have been removed, drop the Country column by using the ALTER TABLE ... DROP COLUMN syntax.
For ObjectServers before OMNIbus v8.1. Fix Pack 8, restart the probes. For ObjectServers at OMNIbus v8.1. Fix Pack 8 or later, you must tell the probes to reread the altered rules. For more information about Rereading the rules file, see https://www.ibm.com/support/knowledgecenter/en/SSSHTQ_8.1.0/com.ibm.netcool_OMNIbus.doc_8.1.0/omnibus/wip/probegtwy/task/omn_prb_rereadingrulesfile.html