SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Table 11 Authorization Requirements for Altering Database Objects (continued)
Authorization RequirementsOperation
To alter a table with the ALTER TABLE statement, you must own its schema or be
the super ID.
nl
“Altering SQL/MX Tables”
(page 164)
To alter a table with the GRANT and REVOKE statements, you must have both
that privilege and the right to grant or revoke that privilege. If the super ID user
issues a GRANT or REVOKE statement using the BY authid-grantor clause, the
authid-grantor must hold the right to grant or revoke the specified privileges. For
more information, see the SQL/MX Reference Manual.
nl
To alter a table using the FIXUP utility, you must be the super ID. For more
information, see the SQL/MX Reference Manual.
To alter a trigger with the ALTER TRIGGER statement, you must own its schema
or be the super ID. Only the super ID can use ALTER TRIGGER DISABLE ALL or
ALTER TRIGGER ENABLE ALL.
“Altering Triggers” (page 166)
To alter a view with GRANT and REVOKE statements, you must have both that
privilege and the right to grant or revoke that privilege. If the super ID user issues
“Altering Views” (page 166)
a GRANT or REVOKE statement using the BY authid-grantor clause, the
authid-grantor must hold the right to grant or revoke the specified privileges. For
more information, see the SQL/MX Reference Manual.
Altering SQL/MX Indexes
To alter these physical file attributes of SQL/MX indexes, use the ALTER index statement:
• ALLOCATE/DEALLOCATE
• AUDITCOMPRESS
• CLEARONPURGE
• MAXEXTENTS
For more information, see the SQL/MX Reference Manual.
Steps for Altering an Index
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 index you want to alter.
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 223) and the SQL/MX Reference Manual.
4. Enter the ALTER INDEX statement.
5. 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.
Example of Altering an SQL/MX Index
This example changes the maximum number of extents to 760:
ALTER INDEX xempname ATTRIBUTE MAXEXTENTS 760
162 Adding, Altering, and Dropping SQL/MX Database Objects