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

5. 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.
6. Enter the ALTER TABLE ADD CONSTRAINT statement.
7. 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.
For more information, see “Creating Constraints on SQL/MX Tables” (page 98).
Adding Indexes to SQL/MX Tables
To add an index to an existing table, use the CREATE INDEX statement. To add an SQL/MX index,
you must own the schema for the underlying table or be the super ID, and have access to all
partitions of the underlying table. An index always has the same security as the table it indexes,
so users authorized to access the table can also access the index. (You cannot access an index
directly, however.) You should consider creating indexes on only the most frequently used table
columns.
Because CREATE INDEX executes in a single TMF transaction, it could experience TMF limitations
such as a transaction time-out if the created index contains a particularly large amount of data.
CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table being indexed. If
other processes have row in the table locked when the operation begins, CREATE INDEX waits
until its lock request is granted or time-out occurs.
By default, or if you specify the POPULATE option, CREATE INDEX both creates and loads the new
index. If you are creating an index on a large SQL/MX table that is already populated, use the
NO POPULATE option, and then run the POPULATE INDEX utility to load the index. After running
POPULATE INDEX, you should perform a FUP RELOAD on the index and all its partitions, to organize
the index structure more efficiently and to reduce index levels.
If the MAXEXTENT value that you specify is too small, the POPULATE INDEX utility automatically
increases the value to the largest possible size. When POPULATE INDEX completes, it adjusts the
MAXEXTENTS value to the value you specified, if it is greater than the number of extents that
needed to be allocated. If the number of extents that need to be allocated is greater than the value
you specify, POPULATE INDEX adjusts the value for MAXEXTENTS to a value equal to the number
of extents that it allocated, plus 50. This behavior is similar to that of NonStop SQL/MP.
For non-unique indexes, the sum of the lengths of the columns in the index plus the sum of the length
of the clustering key of the underlying table cannot exceed 2048 bytes. For unique indexes, the
sum of the lengths of the columns in the index cannot exceed 2048 bytes.
There is no restriction on the number of indexes per table, and there is no restriction on the number
of partitions an index supports, but HP recommends a maximum of 512 partitions.
Consider the following guidelines when adding an index to an existing table:
• Index creation can be quite a long operation, depending on the size of the table and the load
on the system. Therefore, the default locking strategy acquires a shared table lock on the
underlying table. The shared table lock ensures that no users can modify rows during the
creation of the index. This lock will prohibit access to the table by other users that make write
requests during the index creation process.
• Make a TMF online dump of the index immediately after creating in preparation for potential
file recovery (which could be faster than rebuilding the index).
154 Adding, Altering, and Dropping SQL/MX Database Objects