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

COMMIT WORK or ROLLBACK WORK. The result is the same since the transaction is already
aborted.
Some errors that might cause a TMF transaction abort might not be retryable, such as errors caused
by conditions that must be resolved with some corrective action by the user or application. For
example, depending on the application, it might not be useful to retry a duplicate key error returned
by an INSERT statement using ROWSETS. This error causes a TMF transaction abort, but it is not
retryable unless the existing record with the duplicate key is deleted. The application error-handling
logic can be written so that these types of errors are not retried.
Table 16 (page 213) identifies the SQLCODES that are most commonly not retryable unless corrective
action is taken by the application to correct the condition. For more information, see the SQL/MX
Messages Manual.
Table 16 SQLCODES and the Constraints That Prevent Retry Operations
MeaningSQLCODE
The operation is prevented by a check constraint.8101
The operation is prevented by a unique constraint.8102
The operation is prevented by a referential integrity constraint.8103
Recovery for Read-Only Queries
If the application is processing a FETCH statement (with or without rowsets) for a read-only cursor
(no UPDATE/DELETE), and the primary DP2 process fails because of a CPU failure, the application
receives SQLDCODE 8550 with NSK_CODE 1061.
It is not necessary to start a new TMF transaction. The application can incorporate special retry
logic, such as saving the key predicate of the last row returned for use in the WHERE clause of the
SELECT statement. Using this technique, the program can reopen the cursor and continue the query
from the point where the error was encountered.
SQL/MX and SQL/MP Differences in Recovery Action for Read-Only Queries
As noted previously, queries that update the database require a TMF transaction, and the TMF
transaction is aborted automatically by the system if the primary DP2 process fails. This is true for
NonStop SQL/MX, NonStop SQL/MP, and Enscribe. In some situations following a failure or
inaccessibility of the primary DP2 process, NonStop SQL/MP is able to continue processing a SQL
read-only query, and the application does not need to handle the recovery.
In some of these situations, a SQL/MX application must handle the recovery of the failure for a
read-only query, as described in “Recovery for Read-Only Queries” (page 213).
NonStop SQL/MX is designed to maximize performance and throughput for large-scale database
applications. Consequently, NonStop SQL/MX differs from NonStop SQL/MP in these important
ways:
• For performance reasons, NonStop SQL/MX uses a no-waited interface between the SQL/MX
file system and the DP2 process, whereas NonStop SQL/MP uses a waited interface to the
DP2 process.
• In NonStop SQL/MX, the query engine is embedded in the DP2 process directly, whereas in
NonStop SQL/MP, the query engine resides in a separate process.
To extend the recovery logic of NonStop SQL/MX for read-only queries would require maintaining
and checkpointing a large amount of context information between the DP2 process and the file
system and/or between the primary and backup DP2 process. This checkpointing recovery logic
would affect the performance and throughput of NonStop SQL/MX. Because of this, it is more
efficient for the application program to handle the recovery of some transient errors.
Writing SQL/MX Applications to Recover From Temporary Network or Hardware Service Interruptions 213