SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

1. Suspend the current transaction (if supported by the API).
2. Start a new transaction.
3. Complete the work.
4. End the new transaction.
5. Resume the current transaction (if suspended).
You must ensure that the transactions started within the stored procedure are cleaned up properly.
Some of the Java APIs do not support the ability to suspend and resume transactions. In this case,
TMF marks the newly started transaction as the current transaction even though original transaction
is still active, because an SPJ will be executed in a multi-threaded environment.
NOTE: SPJs that return data via result sets are not allowed to use transactions started within an
SPJ for statements that populate the result set.
Committing or Rolling Back a Transaction
In an application, the failure of a CALL statement does not always automatically abort a transaction:
• When AUTOCOMMIT is ON during a system-initiated transaction, errors that occur during
the execution of a CALL statement cause NonStop SQL/MX to roll back the transaction
automatically, including database operations performed by the SPJ method, at the end of
statement execution.
• When AUTOCOMMIT is OFF during a system-initiated or user-defined transaction, errors that
occur during the execution of a CALL statement do not cause NonStop SQL/MX to roll back
the transaction automatically.
To ensure an atomic unit of work in a user-defined transaction or when autocommit is OFF during
a system-defined transaction, you should explicitly commit the transaction in the calling application
when the transaction is successful and roll back the transaction in the calling application when an
error occurs. For more information about transaction management, see the SQL/MX Reference
Manual.
Effect of the jdbcmx.transactionMode Property
If you set the jdbcmx.transactionMode property in a UDR_JAVA_OPTIONS attribute, you
might affect the transaction behavior of an SPJ. By default, an SPJ method runs in the default mixed
transaction mode. The mixed and external transaction modes do not interfere with transaction
behavior of the calling application.
However, if you set jdbcmx.transactionMode to internal in a UDR_JAVA_OPTIONS
attribute, the external transaction of the calling application will be suspended, and the SQL
operations of the SPJ method will be executed in an internal JDBC-managed transaction. The
internal transaction mode prevents an SPJ method from inheriting a transaction from its caller
and prevents the calling application from determining the outcome of an internal JDBC-managed
transaction. For those reasons, do not use the internal transaction mode in an SPJ environment.
For information about jdbcmx.transactionMode, see the JDBC Driver for SQL/MX Programmer's
Reference.
Using the CALL Statement
To invoke a stored procedure, specify the name of the stored procedure and its arguments in a
CALL statement, as shown:
72 Invoking SPJs in NonStop SQL/MX