IZ04873: A POSITIONED UPDATE OR DELETE FAILS WITH SQL0509N IF A CURSOR IS OPENED AGAINST ALIAS AND UPDATE OR DELETE AGAINST BASE TABLE.

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• A positioned update or delete fails with SQL0509N if a cursor is
  opened against alias and update or delete against base table.
  The following steps illustrates the -509 scenario.
  .
  db2 create table S1.T1 (C1 int, C2 char(10))
  db2 insert into S1.T1 values(1,'a')
  db2 create alias S2.A2 for S1.T1
  db2 +c declare CUR1 cursor for select C2 from S2.A2 where C1=1
  for update
  db2 +c open CUR1
  db2 +c fetch CUR1
  db2 +c update S1.T1 set c2='1' where current of CUR1
  .
  SQL0509N  The table specified in the UPDATE or DELETE statement
  is not the same table specified in the SELECT for the cursor.
  SQLSTATE=42827
  .
  The same problem can occur using the IBM DB2 Driver for JDBC
  with updatable ResultSets:
  .
  Statement stmt = con.createStatement(
  ResultSet.TYPE_FORWARD_ONLY,
  
  ResultSet.CONCUR_UPDATABLE );
  ResultSet rs = stmt.executeQuery( "SELECT C2 FROM S2.A2 WHERE
  C1=1" );
  while (rs.next())
  {
     rs.updateString( "C2","1" );
  }
  

Local fix

• none. Jcc driver can use views for positioned updates.
  

Problem summary

• Users affected
  Users who use positioned update or delete against aliases.
  .
  Problem Description
  If this APAR is not applied, positioned update or delete against
  aliases fails with SQL0509N.
  .
  Problem Summary
  DB2 LUW servers checked base table, but did not checked alias.
  

Problem conclusion

• First fixed in DB2 Version 9.1, FixPack 5 (s080512)
  

Temporary fix

• none.
  Users can use views for posisioned update or delete.
  

Comments

APAR Information

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

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

  IZ04970 IZ08035

Modules/Macros

• ENGN_JCC
  

Fix information

• Fixed component name

  DB2 UDB WSE AIX

• Fixed component ID

  5765F3500

Applicable component levels

• R910 PSN

     UP