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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-5
Minimizing Contention
Considerations for WITH SHARED ACCESS Option of
MODIFY
•
To eliminate the interval between the time the MODIFY operation completes and a
new online dump is taken, use the WITH SHARED ACCESS option so that you
can take online dumps while the MODIFY operation proceeds. NonStop SQL/MX
sends an event message to EMS, indicating when online dumps can be taken. An
operator uses the TMFCOM DUMP FILES command to start online dumps.
•
The audit fix-up process searches audit trails for relevant audit information starting
from when the associated MODIFY operation began. Therefore, audit information
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.