Performing positioned updates and deletes

You can perform positioned updates and deletes when your application uses annotated methods and runs against a DB2® database.

Before you begin

Ensure that your application meets the following requirements:
  • You can perform positioned updates and deletes only on DB2 data sources.
  • The annotated method that runs the SELECT statement that declares the cursor should return either a ResultSet or an Iterator.
  • The declaration of the method that runs the SELECT statement and the declaration of the method that runs the UPDATE or DELETE statement must be in a single interface.
  • The names that you use for cursors cannot begin with DB2JCCCURSOR or DB_PDQ.
  • You must not open two cursors at the same time on a single connection if those cursors have the same name.
  • pureQuery throws an exception if an application follows these steps:
    1. Runs a SELECT statement that declares a cursor for a positioned update or delete.
    2. Runs the same SELECT statement again on the same connection that the last statement ran against previously.
    3. Runs an UPDATE or DELETE statement that uses a cursor that either of the SELECT statements declared.
    pureQuery cannot determine which of the two cursors the UPDATE or DELETE statement refers to.

About this task

For syntax diagrams for the annotated methods, see the section "Syntax for positioned updates and deletes" in Syntax of annotated methods that run SQL against databases.

Procedure

To write the code for performing a positioned update or delete:

  1. When you declare the annotated method to run the SELECT statement that positions the cursor, use the cursorName attribute of the @Cursor annotation to specify the name of the cursor. This method must return either a java.sql.ResultSet object or an Iterator object.
    Attention: If the SELECT statement contains a FOR UPDATE clause, but you set the concurrency attribute of the @Cursor annotation to java.sql.ResultSet.CONCUR_READ_ONLY (the default value), the FOR UPDATE clause takes precedence over the attribute. pureQuery opens the cursor as an updatable cursor.
  2. When you declare the annotated method to run an UPDATE or DELETE statement that uses the cursor, use the positionedCursorName attribute in the @Update annotation to specify the name of the cursor.

Example

For an example that uses a SELECT statement and an UPDATE statement, see the section "Syntax for positioned updates and deletes" in Syntax of annotated methods that run SQL against databases.

Feedback