NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-52
Considerations—COMMIT WORK
AUDITONLY
directs SQL to retain existing locks on nonaudited objects.
If your program holds locks on nonaudited objects and specifies AUDITONLY, the
program must explicitly use CLOSE, UNLOCK TABLE, or FREE RESOURCES to
close cursors and release locks.
Considerations—COMMIT WORK
•
BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK
ends a transaction. See TMF Transactions
on page T-5 for more information.
•
COMMIT WORK does not cause a write to disk.
•
Use in host programs
Within a program, using COMMIT WORK is equivalent to using the following
sequence of statements:
FREE RESOURCES (an SQL statement)
ENDTRANSACTION procedure call
COMMIT WORK returns status information to the SQLCA so you can use
WHENEVER to check for error conditions.
Examples—COMMIT WORK
•
Suppose your application adds information to the inventory. You have just received
24 terminals from a new supplier and want to add the supplier and update the
quantity on hand. The part number for the terminals is 6402, and the supplier is
assigned supplier number 17. The cost of each terminal is $800.
The transaction must add the supplier to the SUPPLIER table, add the order for
terminals to PARTSUPP, and update QTY_ON_HAND in PARTLOC. After the
INSERT and UPDATE statements execute successfully, you commit the transaction.
>> VOLUME INVENT;
>> BEGIN WORK;
>> INSERT INTO PARTSUPP VALUES (6402, 17, 800.00, 24);
--- 1 row(s) inserted.
>> INSERT INTO SUPPLIER VALUES (17, "Super Peripherals",
+> "4751 Sanborn Way", "Santa Rosa", "California", "95405");
--- 1 row(s) inserted.
>> UPDATE PARTLOC SET QTY_ON_HAND = QTY_ON_HAND + 24
+> WHERE PARTNUM = 6402 AND LOC_CODE = "G48";
--- 1 row(s) updated.
>> COMMIT WORK;