Performing positioned UPDATE and DELETE operations in an
SQLJ application
As in DB2 applications in other languages, performing positioned UPDATEs and
DELETEs with SQLJ is an extension of retrieving rows from a result table.
The basic steps are:
1. Declare the iterator.
The iterator can be positioned or named. For positioned UPDATE or DELETE
operations, the iterator must be declared as updatable. To do this, the
declaration must include the following clauses:
implements sqlj.runtime.ForUpdate
This clause causes the generated iterator class to include methods for
using updatable iterators. This clause is required for programs with
positioned UPDATE or DELETE operations.
with (updateColumns=″column-list″)
This clause specifies a comma-separated list of the columns of the result
table that the iterator will update. This clause is optional.
You need to declare the iterator as public, so you need to follow the rules for
declaring and using public iterators in the same file or different files.
If you declare the iterator in a file by itself, any SQLJ source file that has
addressability to the iterator and imports the generated class can retrieve data
and execute positioned UPDATE or DELETE statements using the iterator.
The authorization ID under which a positioned UPDATE or DELETE statement
executes depends on whether the statement executes statically or dynamically.
If the statement executes statically, the authorization ID is the owner of the plan
or package that includes the statement. If the statement executes dynamically
the authorization ID is determined by the DYNAMICRULES behavior that is in
effect. For the IBM Data Server Driver for JDBC and SQLJ, the behavior is
always DYNAMICRULES BIND.
2. Disable autocommit mode for the connection.
If autocommit mode is enabled, a COMMIT operation occurs every time the
positioned UPDATE statement executes, which causes the iterator to be
destroyed unless the iterator has the with (holdability=true) attribute.
Therefore, you need to turn autocommit off to prevent COMMIT operations
until you have finished using the iterator. If you want a COMMIT to occur
after every update operation, an alternative way to keep the iterator from being
destroyed after each COMMIT operation is to declare the iterator with
(holdability=true).
3. Create an instance of the iterator class.
This is the same step as for a non-updatable iterator.
4. Assign the result table of a SELECT to an instance of the iterator.
This is the same step as for a non-updatable iterator. The SELECT statement
must not include a FOR UPDATE clause.
5. Retrieve and update rows.
For a positioned iterator, do this by performing the following actions in a loop:
a. Execute a FETCH statement in an executable clause to obtain the current
row.
b. Test whether the iterator is pointing to a row of the result table by invoking
the PositionedIterator.endFetch method.
114 Application Programming Guide and Reference for Java
â„¢
To Next Page
Loading...
Need help?
General