SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-5
Avoiding Contention Between DDL or Utility
Operations
must be retained on the system or on backup media until the MODIFY operation
completes. Audit trails should not be automatically deleted before the MODIFY
operation completes. If the audit fix-up process does not find audit files online, the
system prompts the operator (on the system console) to restore the audit trails. If
there are no backed-up audit trails, the request fails.
•
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