|
|
|
Valid in: ESQL
This statement polls the local DBMS server to determine the commit status of the local transaction associated with the specified distributed transaction. The distributed transaction is identified by its distributed transaction ID, a unique, 8-byte integer that is generated by the coordinator application.
This statement provides support for the two-phase commit functionality. For a discussion of two phase commit, see the chapter "Working with Transactions and Handling Errors."
Dynamic SQL cannot be used to execute this statement. This statement must be terminated according to the rules of your host language.
Note: The only SQL statements that can follow the prepare to commit statement are commit or rollback.
|
|
|
The Prepare to Commit statement has the following format:
EXEC SQL PREPARE TO COMMIT
WITH HIGHXID = value, LOWDXID = value;
Can be an integer constant or integer variable. The value associated with highdxid must be the high-order 4 bytes of the distributed transaction ID. The value associated with lowdxid must be the low-order 4 bytes of the distributed transaction ID.
|
|
|
All users are permitted to use this statement.
|
|
|
|
|
|
The following example shows a portion of a banking application that uses the prepare to commit statement:
...
exec sql begin declare section;
from_account integer;
to_account integer;
amount integer;
high integer;
low integer;
acc_number integer;
balance integer;
exec sql end declare section;
define sf_branch 1
define bk_branch 2
define before_willing_commit 1
define willing_commit 2
exec sql whenever sqlerror stop;
/* connect to the branch database in s.f */
exec sql connect annie session :sf_branch;
/* program assigns value to from_account,
** to_account, and amount
*/
/* begin a local transaction on s.f branch to
** update the balance in the from_account.
*/
exec sql update account
set balance = balance - :amount
where acc_number = :from_account;
/* connect to the branch database in berkeley. */
exec sql connect aaa session :bk_branch;
/* begin a local transaction on berkeley branch
** to update the balance in the to_account.
*/
exec sql update account
set balance = balance + :amount
where acc_number = :to_account;
/* ready to commit the fund transfer transaction.
** switch to s.f branch to issue the prepare to
** commit statement.*/
exec sql set_sql (session = :sf_branch);
/* store the transaction state information */
store_state_of_xact(sf_branch,
before_willing_commit, high, low, "annie"
exec sql prepare to commit with highdxid = :high,
lowdxid = :low;
/* store the transaction state information */
store_state_of_xact(sf_branch, willing_commit,
high, low, "aaa");
/* switch to berkeley branch to issue the prepare
** to commit statement.*/
exec sql set_sql (session = :bk_branch);
/* store the transaction state information */
store_state_of_xact(bk_branch,
before_willing_commit, high, low, "aaa");
exec sql prepare to commit with highdxid = :high,
lowdxid = :low;
/* store the transaction state information */
store_state_of_xact(bk_branch, willing_commit,
high, low, "aaa");
/* both branches are ready to commit; commit the
** fund transfer transaction. */
/* switch to s.f branch to commit the
** local transaction. */
exec sql set_sql (session = :sf_branch);
exec sql commit;
/* switch to berkeley branch to commit the
** local transaction. */
exec sql set_sql (session = :bk_branch);
exec sql commit;
/* distributed transaction complete */