Updates within a Cursor in DB2

1
Updates within a Cursor in DB2

• Pam Odden

2
Objectives

• Learn how to declare a cursor for update
• Learn how to specify which row to update
• What types of cursors can be updated?
• Consider performance issues

3
Positioned Update and Delete Statements

• UPDATE table-name SET column-name expression
  (, column-name expression)
• WHERE CURRENT OF cursor-name
• Updates the single row that is the current row of
  the cursor
• After updating, the row remains the current row
  of the cursor
• DELETE from table-name
• WHERE CURRENT OF cursor-name
• Deletes the single row that is the current row of
  the cursor
• After deletion, the cursor has no current row.
  It is positioned in the empty space left by the
  deleted row, and will be advanced to the next row
  by a subsequent FETCH statement
• There is no positioned insert statement

4
Strict criteria for positioned update and deletes

• According to the SQL1 Standard, the query
  associated with the cursor
• must draw its data from a single source table,
  not from a join.
• cannot specify an ORDER BY clause.
• cannot specify the DISTINCT keyword.
• must not specify a GROUP BY or HAVING clause.
• cannot use UNION or UNION ALL
• cannot be a subquery in which the same table is
  specified in the FROM clauses of both the outer
  query and the subquery
• The user must have the UPDATE or DELETE privilege
  on the base table.
• In DB2, the cursor must be specified FOR UPDATE
  OF column-name at the time it is declared
• These rules can change with versions of DB2.

5
Declare Cursor statement with For Update clause

• DECLARE CURSOR cursor-name FOR
• select statement
• FOR UPDATE OF column-name (, column-name)
• Declares the cursor will be updateable
• Specifies which columns will be updated
• Sometimes the database management system (DBMS)
  cannot determine whether a cursor is updateable
  or not. The cursor qualifies according to the
  criteria and the cursor select statement does not
  specify FOR UPDATE or FOR READ ONLY. This is
  called an ambiguous cursor.
• DB2 does not permit updates within an ambiguous
  cursor. You get SQL bind error -510 Table or
  view cannot be modified as requested . DB2 does
  allow deletes within an ambiguous cursor, but the
  row is not locked before the delete.
• Other Database Management Systems (DBMS) allow
  an ambiguous cursor and assume it may be updated.
  This causes extra work and extra locking.
• Get in the habit of specifying FOR UPDATE or FOR
  READ ONLY. Programs that explicitly declare the
  intention to update are more easily maintained.

6
So what happens when we do this?

• When the results table is materialized (we
  learned last week this may be at open time or
  fetch time), DB2 obtains a lock on a page as it
  is accessed.
• Since our TSO compile option 5.7 specifies an
  isolation level of CURSOR STABILITY, read-only
  page locks are released as soon as a different
  page is accessed.
• Page locks on data that is specified for update
  are not released until the updating program
  issues a Commit or Rollback.
• Consequently, depending on the structure of the
  program, a lot of data can wind up being locked.
• A batch program opening a large cursor may not
  issue a commit until it is finished. This wont
  cause a problem if the program runs at night and
  no other processes are accessing the same data,
  but if other processes do access the data, they
  will have to wait.
• An online program may open a small cursor with
  only a few rows. However, what if the user gets
  a phone call or goes to lunch before a commit is
  issued? Other users are meanwhile prevented from
  reading or updating data on those pages.

7
One Teams Strategy for Online Updates

• Isolation level is cursor stability
• Cursor is originally opened for read-only,
  allowing locks to be released when the next page
  is accessed
• Data is displayed to the user, who can scroll as
  desired
• When the user makes a change and clicks SAVE, the
  updated row is selected again, this time for
  update
• The timestamp column, LAST_UPDATE, is compared
  with the timestamp column of the row originally
  selected in the cursor.
• If they match, we know the data has not been
  updated by someone else since our original
  select. The update is issued, followed by a
  commit.
• If they dont match, we know the data has been
  updated. A message is displayed to the user
  trying to do the update that the data has
  changed, and the new values are displayed. The
  user then can decide whether to proceed with the
  update.

8
One Teams Strategy, cont.

• The strategy on the previous slide is not a
  solution for every application, just an example
  of how one team did it.
• Advantages
• Data not locked until ready for update
• Restriction against order by clause or join in
  cursor is avoided
• Disadvantages
• Rows are selected multiple times
• It worked well for a busy call-center application
  transmitting over a network, keeping locking to a
  minimum and transactions short.
• The important thing is to have a strategy and to
  consider your programs effect on other users.

9
Guidelines

• Keep transactions as short as possible
• Use WHERE CURRENT OF cursor-name rather than
  doing independent updates using a WHERE clause.
  It is faster and protects you from inadvertently
  updating more than the one current row you are
  processing
• Issue a commit as soon as possible after your
  program completes an update
• If you know the program will not refetch a row of
  data after the cursor has moved past it, use a
  less restrictive isolation level. This allows
  DB2 to unlock the row sooner.
• Explicitly specify whether your cursor is for
  read only or for update.
•  

10
Summary

• Use update or delete where current of cursor-name
  to update the current row of a cursor.
• Declare a cursor as FOR READ ONLY or FOR UPDATE
  OF column-name.
• Consider program performance and the need for
  concurrency when designing an update strategy.