II05812: FOR UPDATE OF CLAUSE NOW PREVENTS BLOCKING IN V2R3 FOR DISTRIBUTED QUERIES.

APAR status

• Closed as canceled.

Error description

• 5740XYR00
  User is doing a distributed query using DB2 private DUW
  protocols. The SQL DECLARE of the cursor contains the
  'FOR UPDATE OF' clause indicating that the cursor may be
  updated by an UPDATE/DELETE 'WHERE CURRENT OF' SQL statement.
  However the program does not contain an UPDATE or DELETE
  'WHERE CURRENT OF' statement.  Also there are no dynamic
  SQL statements.  As a result, there is no way that the cursor
  can be updated even though the cursor was declared with
  'FOR UPDATE OF'.
  
  When the program is executed on a DB2 V2R2 (regardless if the
  DB2 server is V2R2 or V2R3), the data will be blocked thus
  getting improved performance.  Blocking was done even though
  the cursor was declared 'FOR UPDATE OF' because DB2 V2R2 design
  was to block data when no updates on a cursor can be done
  (regardless of the 'FOR UPDATE OF' clause).
  
  The design was changed in DB2 V2R3.  In DB2 V2R3, if you say
  'FOR UPDATE OF', that will force DB2 to avoid blocking, even if
  the cursor could not be updated.  This allows users to force
  single row fetching (as opposed to blocking).
  So when the program is executed on a DB2 V2R3 (regardless if the
  DB2 server is V2R2 or V2R3), the data will no longer be blocked
  thus causing a performance degradation. Users experiencing this
  should remove the 'FOR UPDATE OF' clause if they want DB2 to
  block the data.
  ****************************************
  Additional symptoms and keywords:
   DDF Distributed Data Facility R220 R230
   performance blocking
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels