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-4
Minimizing Contention
MODIFY TABLE cat1.sch1.table1
ADD WHERE KEY = value ('San Jose') TO LOCATION $DATA12
WITH SHARED ACCESS
COMMIT;
When you specify the WITH SHARED ACCESS option with MODIFY, these actions
occur:
1. Initialization and load. Reads from source partitions use browse access and so do
not obtain locks. Writes to target partitions do obtain locks. If a given partition
already exists and another application has a file-level lock on that partition, writes
on behalf of MODIFY will wait and possibly time out. If timeout occurs, the
MODIFY operation fails.
2. Audit fix-up. Audit fix-up processes search TMF audit trails for changes made since
the load of the records. If changes are found, target objects are updated to reflect
the changes.
At this point, operation depends on COMMIT options selected with the MODIFY
statement. If a BEFORE or AFTER time was specified, NonStop SQL/MX waits for
the appropriate time window before starting the next (commit) phase. If the time
window has passed, the operation fails.
While the operation waits, the audit fix-up processes continue reading audit trails
and updating target objects.
3. Commit. NonStop SQL/MX acquires an exclusive table lock on each source object
and searches audit trails for changes made since the last audit fix-up work.
NonStop SQL/MX updates target objects to reflect the changes. Finally, NonStop
SQL/MX updates file labels and metadata files. At this time, exclusive locks are
obtained on the other partitions.
For more detailed information, see the WITH SHARED ACCESS option information for
MODIFY in the SQL/MX Reference Manual.
COMMIT Option Available for WITH SHARED ACCESS
The COMMIT option of WITH SHARED ACCESS controls the start time for the final
phase of the operation.
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