It is also possible to modify the database schema. You can add or remove entire tables or views from a running database using the same procedure used for modifying stored procedures described in Section 7.2, “Updating the Stored Procedures”. That is, you can modify the schema definition, recompile the application catalog, and update the database on the fly using the @UpdateApplicationCatalog system procedure.
However, you cannot make smaller modifications, such as changing individual columns within a table on the fly. To make these sorts of changes to the schema you must:
Save the current data.
Shut down the database.
Replace the application catalog.
Restart the database with the new catalog.
Reload the data saved in Step #1.
Using these steps, you can add or remove columns to existing tables. You can also change the datatype of existing columns, as long as you make sure the new type is compatible with the previous type (such as exchanging integer types or string types) and the new datatype has sufficient capacity for any values that currently exist within the database.
However, you cannot change the name of a column, add constraints to a column or change to a smaller datatype (such as changing from INTEGER to TINYINT) without the danger of losing data. To make these changes safely, it is better to add a new column with the desired settings and write a client application to move data from the original column to the new column, making sure to account for exceptions in data size or constraints.
See Section 9.1.3, “Changing the Database Schema or Cluster Configuration Using Save and Restore” for complete instructions for using save and restore to modify the database schema.