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

7 Adding, Altering, and Dropping SQL/MX Database
Objects
After you create an SQL/MX database, you can assume the database is consistent and the
application data is valid. Database management operations that add, alter, or drop database
objects must maintain this same level of data consistency and validity, and must be planned
carefully.
This section addresses these topics:
• “Planning Operations on SQL/MX Objects” (page 150)
• “Adding Objects to an SQL/MX Database” (page 150)
• “Altering Objects in an SQL/MX Database” (page 162)
• “Dropping Objects From an SQL/MX Database” (page 168)
This section applies to SQL/MX objects only. For more information, see the SQL/MP Installation
and Management Guide.
Planning Operations on SQL/MX Objects
As with any change to the database, the first step is careful planning. While planning your changes
to the database, you should consider these issues, discussed in more detail in this section:
• Are there dependent SQL/MX objects that the change might affect? Sometimes a single change
makes other changes necessary for consistency. Your plan for completing a change should
include performing an initial change and, if needed, changes to dependent SQL/MX objects
throughout the database.
• Does the user making the change have the authority to perform this change? If not, the
command does not execute.
• Are the necessary base tables, partitions, and systems available?
• What impact would the change have on production system applications? Should the
applications be stopped to apply this change consistently and without system degradation?
• Does the user making the change have a valid recovery mechanism to undo the change if
required?
When you make changes to the database, you should always maintain a log of all operations that
add, drop, or alter database objects. For more information, see “Managing Database Applications”
(page 212).
Adding Objects to an SQL/MX Database
Table 8 (page 150) summarizes the objects you can add to an SQL/MX database and identifies
the statement, command, or utility required for each add operation.
Table 8 Adding Objects to an SQL/MX Database
Statement, Command or UtilityOperationObject
CREATE CATALOG statementAddCatalog
ALTER TABLE ADD COLUMN statementAddColumn
ALTER TABLE ADD CONSTRAINT statementAddConstraint
CREATE INDEX statementAddIndex
MODIFY utilityAddPartition
150 Adding, Altering, and Dropping SQL/MX Database Objects