SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-10
Adding Columns
Adding Columns
You can add a column to any key-sequenced table. In addition, you can add a column
to any relative table already defined with enough extra bytes in the RECLENGTH value
to accommodate the new column. You cannot, however, add columns to entry-
sequenced tables or to views or indexes. To add a column, use the ALTER TABLE
statement with the ADD COLUMN clause. Each ALTER TABLE statement adds only
one column. To add several columns, use the statement once for each column.
Each new column is added as the last column in the table. If you want to add a column
to a table so that it does not appear as the last column, follow the steps for altering
columns under Altering Database Objects on page 7-13.
Existing programs that depend on the table are not affected by the addition of a new
column unless a program needs to use the new column or includes an INSERT * or a
SELECT * statement that refers to the new column’s table. Adding a column, however,
invalidates programs that depend on the table unless the 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.
You should include steps to explicitly SQL compile invalid programs to avoid automatic
recompilation and to return the application to a valid state.
Adding a column does not cause any existing data to be rewritten. For existing rows,
the new column takes on the system default value unless you specify a default value.
Adding a column to a table does not affect existing dependent views or indexes.
The ALTER TABLE statement with the ADD COLUMN clause requires an exclusive
table lock to ensure that no rows are inserted during creation of the column. All
partitions and protection views must also be accessible.
After you add a column, you should consider whether the new column must be
integrated into existing or new views and indexes. Application programmers can write
new programs to use the new column or alter existing programs to use the new
column.
This example adds a column to the CUSTOMER table:
>> LOG $VOL.DBCHANGE.CNGLOG;
>> ALTER TABLE $VOL1.SALES.CUSTOMER
+> ADD COLUMN PRIOR_YEARS_SALES
+> PIC S9(9)V99 COMP DEFAULT SYSTEM NOT NULL;
--- SQL operation complete.
Note. Views previously defined as “SELECT * FROM...” will not select the new column.