SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-4
Minimizing Contention
Options Available for WITH SHARED ACCESS
The WITH SHARED ACCESS option supports these options:
•
REPORT starts or stops EMS reporting for the operation. Events can be sent to $0
or to an alternate collector.
•
NAME specifies an SQL identifier as the name of the operation so that you can
identify EMS messages for the operation or identify the operation in a CONTINUE
statement.
•
COMMIT controls the start time for the final phase of the operation and specifies a
timeout period for lock requests and handling of retryable errors during the final
phase of the operation. It also specifies whether you want user response before
continuing to the commit phase. You can use this clause with the initial DDL
request or in a CONTINUE statement.
Considerations
These considerations apply to use of the WITH SHARED ACCESS OPTION:
•
To eliminate the interval between the time the DDL operation completes and a new
online dump is taken, use the WITH SHARED ACCESS option to take online
dumps while the DDL operation proceeds. If an online dump exists for a table (or
for the base table of an index) and REPORT is specified, SQL sends an event
message to EMS indicating when online dumps can be taken. An operator must
use the TMFCOM DUMP FILES command to start these online dumps.
•
The audit fix-up process searches audit trails for relevant audit information starting
from when the associated DDL operation began. Therefore, audit information must
be retained on the system or on backup media until the DDL operation completes.
Audit trails should not be automatically deleted before the DDL 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:
°
If the audit fix-up process does not find an audit trail online, operator
intervention is needed to restore backed-up audit trails. If the requested audit
trail does not exist, the request fails.
°
If an online dump on tape is needed for a newly created partition or index,
operator intervention is needed to restore the online dump.
°
If an online dump exists for the table (base table for an index) and REPORT is
specified, an event is sent to EMS indicating when online dumps can be taken.
Operator intervention is needed to start these online dumps.
•
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