SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-26
Altering Columns
If there is a concern that a restored table might have had the INCOMPLETE
SQLDDL OPERATION flag set, use the SQLCI ALTER TABLE <name>
PARTONLY RECOVER INCOMPLETE SQLDDL OPERATION command for the
table. This step will not harm the table, even if it did not have the flag set
previously.
•
If you attempt to use SQL DUP from a node running version 315 or earlier, SQL
does not recognize the flag and proceeds with the operation. If the
UNRECLAIMED FREESPACE flag is set, a FUP RELOAD of the object from a
node running a SQL/MP version earlier than 315 might corrupt the object or cause
a processor halt. If the INCOMPLETE SQLDDL OPERATION flag is set, the target
object might have extraneous data that will be visible to accessing applications. Do
not run UPDATE STATISTICS when one of these flags is set. The results might be
incorrect.
For more information about partitions, see Creating Table Partitions on page 5-32 and
Creating Index Partitions on page 5-48.
Altering Columns
You are not allowed to alter column definitions or sizes. To achieve an alteration of an
existing column, you must first create a new table with the column sizes and data type
definitions you want and then load the new table from the old table.
You cannot alter a view or index definition to add or delete columns. You can
accomplish these operations by dropping the old object and adding a new object to
comply with the new structure.
Also, you cannot change a collation associated with a column. You can, however, add
an index with a different collation for the column, provided both collations have the
same shifting rules.
Example 7-1 on page 7-27 shows the operations required to effectively alter a column.
The column CUST_PO is increased from 35 to 45 bytes. The example includes the
original table definition ($VOL1.SALES.ORDERS), the CREATE TABLE statement for
the new table, and the LOAD command. The LOAD command must include the
MOVEBYNAME option to load the new table correctly.