You can remove a named field from the schema definition while the database remains running and users are connected to the database. The data of the field in the database table is also effectively removed in the process without requiring any exclusive lock.

How dropping a field online affects ABL clients

During an online Drop Field transaction, concurrent ABL clients are prevented from accessing the field that is being dropped.

After an ABL client drops a field online, the client notifies all the other connected clients that a field is dropped. The connected clients then complete the database transactions that are in process. After a client completes a transaction, it refreshes its schema cache.

Effects on transactional concurrency

Dropping a field online has the following effects on the concurrency of database transactions:

  • Concurrent schema changes are not allowed while a field drop transaction is in process.
  • New database connection requests are not allowed while the field drop transaction is in process. These requests must be terminated or wait until the transaction completes.
  • Concurrent transactions cannot read schema data. Read operations fail or are blocked until the field drop transaction is complete.

Methods for dropping a field online

You can drop a field online using the Data Adminstration tool. Select the Add new objects online checkbox in the Load Data Definitions dialog box to perform the loading operation online. Use this option only to load a delta .df file when there is a difference between two databases (a source database and a target database).

For more information, see Navigate within the Data Administration tool in Windows in Database Administration Tools.

Limitations of dropping fields online

Some limitations of dropping fields online are as follows:
  • If Drop Field is part of a transaction with other schema changes, then the entire transaction, including Drop Field, occurs offline.
  • If the dbnotification feature of the database is turned off, then Drop Field can only be offline.
  • In a single transaction, you can drop only one field online.
  • You cannot drop an LOB field online.
  • Online field drop works only if the database and all the clients are of version 12.4 or above.