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

Minimizing Contention
When managing partitions, you can minimize contention by using the WITH SHARED ACCESS
option of the MODIFY TABLE or MODIFY INDEX statements. For example, this MODIFY TABLE
statement uses the WITH SHARED ACCESS option to provide online execution on range-partitioned
objects:
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 information, see 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 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.
Understanding the Implications of Concurrency 295