SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-5
Adding Columns
Adding Columns
To add a column to an SQL/MX table, use the ALTER TABLE statement with the ADD
COLUMN clause.
You can add columns to SQL/MX tables, but not to views or indexes. Each ALTER
TABLE statement adds only one column. To add several columns, use the statement
once for each column.
SQL applications that depend on the table are not affected by the addition of a new
column unless the application needs to use the new column, performs an INSERT
statement without a target-col-list, or uses a SELECT * statement which refers
to the new column. You should explicitly SQL compile these applications to avoid
automatic recompilation and to return the application to a valid state.
Adding a column does not cause existing data to be rewritten. For existing rows, the
new column takes on the default value you specify. Adding a column to a table does
not affect existing dependent views or indexes.
Use the ALTER TABLE statement with the ADD COLUMN clause with an exclusive
table lock to ensure that no rows are inserted during creation of the column. All
partitions and 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.
For more information, see the SQL/MX Reference Manual.
Steps for Adding a Column to a Table
1. Start an MXCI session. Enter a LOG command to initiate a log file for 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.
3. Determine the attributes for the column you wish to add including the data type, the
character set, the default value, the heading, and any constraints on the
associated data.
4. Use the DISPLAY USE OF command to identify which user modules are
associated with this object. See the similarity check criteria in the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Note. Where possible, avoid adding columns to a table. When you add a column, internal
expression optimizations are turned off, and the executor incurs a performance penalty
whenever a column is added. The extent of the impact on performance depends in large part
on data types used in added columns. For more information, see Database Design Guidelines
for Improving OLTP Performance on page 7-39.
Note. Views previously defined as “SELECT * FROM...” do not select the new column.