SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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 the “Database Design Guidelines for Improving
OLTP Performance” (page 103).
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.
NOTE: Views previously defined as “SELECT * FROM...” do not select the new column.
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 an SQL/MX 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 want 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
to determine if your changes are likely to cause similarly check to fail and force automatic
recompilation. If they will, you should SQL compile these modules after making the changes
to avoid expensive automatic recompilations at run time. SQL applications that are running
while you make these changes will still undergo automatic recompilation.
For more information about explicit and automatic recompilation, see the SQL/MX Programming
Manual for C and COBOL. For more information about using DISPLAY USE OF, see “Checking
Module Dependencies with DISPLAY USE OF” (page 226)and the SQL/MX Reference Manual.
5. Enter the ALTER TABLE statement with the ADD COLUMN clause.
6. Determine if the new column should 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 following this procedure.
7. Revise the application source code as needed to reflect your changes to the database. Process
and compile the updated source file. For more information, see the SQL/MX Programming
Manual for C and COBOL.
Adding Objects to an SQL/MX Database 153