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

Altering SQL/MX Tables
You can use various statements and utilities to alter SQL/MX tables in many ways. For more
information about these statements and utilities, see the SQL/MX Reference Manual.
Altering SQL/MX 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. 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.
4. Enter the CREATE TABLE statement to create a new table definition that contains the new
column attributes.
5. After creating the new table, load the old table’s data into the new table with INSERT/SELECT
statements.
6. After the loading the new table, drop the old table.
Adding Table Columns
See “Adding Columns to an SQL/MX Table” (page 152).
Dropping Table Columns
See “Dropping Columns from an SQL/MX Table” (page 170).
Altering Table Constraints
You cannot alter constraints, but you can change them by dropping an existing constraint or by
adding a new constraint to the table. Constraints reside in definition only; therefore, they have no
physical or security attributes to alter.
Adding a Table Constraint
See “Adding Constraints” (page 154).
Dropping a Table Constraint
See “Dropping Constraints” (page 171).
Altering Table File Attributes
To alter these physical file attributes of SQL/MX tables, use the ALTER TABLE statement:
• ALLOCATE/DEALLOCATE
• AUDITCOMPRESS
• CLEARONPURGE
• MAXEXTENTS
166 Adding, Altering, and Dropping SQL/MX Database Objects