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-23
Altering Tables
Altering Table Columns
You cannot alter table columns directly. You can alter them indirectly, however, by
creating a new table with the different column sizes or data type definitions:
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 a name for the new table.
3. Query the system metadata to ensure the new table name is unique among
existing table, view, SQL/MP alias and SPJ names in the schema.
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
Java 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 information about explicit and automatic recompilation, see the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Java. For information about using DISPLAY USE OF, see Checking Module
Dependencies With DISPLAY USE OF on page 11-20 and the SQL/MX Reference
Manual.
5. Enter the CREATE TABLE statement to create a new table definition that contains
the new column attributes.
6. After creating the new table, load the old table’s data into the new table with
INSERT/SELECT statements.
7. After the loading the new table, drop the old table.
8. 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 and the SQL/MX
Programming Manual for Java.
Adding Table Columns
See Adding Columns on page 9-5.
Dropping Table Columns
See Dropping Columns on page 9-29.