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

• Operator intervention might be necessary in these situations:
To restore backed-up audit trails if the audit fix-up process does not find an audit trail
online. If the requested audit trail does not exist, the request fails.
◦
◦ To restore the online dump if an online dump on tape is needed for a newly created
partition or index.
◦ In response to EMS event messages indicating when online dumps can be taken, operator
intervention is required to start the online dumps.
• MODIFY operations that use WITH SHARED ACCESS typically take considerably longer than
equivalent operations without WITH SHARED ACCESS. They do, however, cause less
application unavailability, because WITH SHARED ACCESS allows DELETE, INSERT, and
UPDATE access during the operation. The time difference depends largely on the number and
length of transactions on the nodes that contain source and target objects for the operation,
particularly the number and length of transactions that directly involve source objects for the
operation.
• You cannot use the WITH SHARED ACCESS option of MODIFY if one or more of these
conditions exist:
◦ The partitioning key of the range-partitioned table or index you want to MODIFY is not
a prefix of the table or index clustering key.
◦ You want to partition hash-partitioned tables or indexes. You cannot use the WITH SHARED
ACCESS option on hash-partitioned tables or indexes.
◦ You want to partition a system-clustered object. You cannot use the WITH SHARED
ACCESS option on system-clustered objects.
Avoiding Contention Between DDL or Utility Operations
Only one DDL or utility statement can operate on a given SQL/MX object (or partition of an SQL/MX
object) at a time. An error occurs if you attempt to execute a DDL or utility statement while another
process is executing a DDL or utility statement on the same object.
The specific error depends on the statement involved and the phase of the operation at which the
conflict occurs. Common file system errors for this situation include:
12 File in use
40 The operation timed out
73 The table is locked
You can also get this SQL/MX error message:
1134 A concurrent utility or DDL operation is being performed on object object-name, its parent, or one of its
dependencies. That operation must complete before the requested operation can run.
For more information about this and other SQL/MX error messages, see the SQL/MX Messages
Manual.
Other Operational Considerations
In general, SQL/MX utility operations follow a three-step process:
1. Lock the metadata for the object being acted upon. Also acquire a DDL lock to prevent metadata
or label changes from occurring until the utility operation completes. The DDL lock encompasses
several transactions and persists until the utility operation is complete.
2. Perform the utility operation. This step encompasses many transactions.
3. Update metadata to reflect changes caused by the utility operation.
4. Remove the DDL lock.
296 Enhancing SQL/MX Database Performance