SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-11
Adding Columns
To add a column, 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 to which you want to add the column. You can
add columns to all key-sequenced tables or to relative tables with sufficient
RECLENGTH length.
3. Match the data type for the column with any corresponding column’s data type so
that users can perform joins or predicate searches.
4. Determine which programs depend on the table by using the DISPLAY USE OF
command. These programs will be invalidated unless a program was compiled with
the CHECK INOPERABLE PLANS option and the similarity check is enabled for
the table. For more information, see Using Similarity Checks on page 10-15.
5. Optionally, prevent the use of the table for the duration of the ALTER TABLE
operation to eliminate conflicts in access to the table; this operation requires
exclusive use of the table.
6. Enter the ALTER TABLE statement with the ADD COLUMN clause.
7. Determine if the new column will also be added to any existing index or view or if
program changes are required. After adding the new column, follow the steps for
integrating the new column into an existing application, described in the following
text.
8. SQL compile the invalidated programs.
9. For an audited table, make a new TMF online dump.
10. Restart use of the table if you stopped its use.
To integrate the new column into the existing database or application programs, do
these:
•
If you want to create a new index using the new column, follow the steps for adding
indexes.
•
If you want to add the new column to an existing index, first follow the steps for
dropping indexes, then follow the steps for adding indexes to add the new column
definition. You cannot alter an index or view to add a column.
•
If you want to create a new view using the new column, follow the steps for adding
views.
•
If you want to add the new column to an existing view, first follow the steps for
dropping views, then follow the steps for adding views. You cannot alter a view to
add a column.
•
If you want to use the new column in programs, you must change existing
programs to refer to the new column. You might need to change screen sections to
display the column on the screen, and you might need to change code sections to