JR31734: SQL SCALAR FUNCTION IN SELECT-CLAUSE BLOCKS USE OF UPDATE-CLAUSE WITH SQLSTATE 42829

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

• The following query should be valid:
  
  select T1.C1, MYSQLUDF( T1.C2 ) A from T1 for update
  
  where MYSQLUDF is a SQL scalar function.  The expected result is
  that the update-clause implicitly includes all updatable columns
  of table T1.  However, the query fails to compile with the
  following error:
  
  SQL0511N  The FOR UPDATE clause is not allowed because the table
  specified by the cursor cannot be modified.  SQLSTATE=42829
  

Local fix

• Explicitly specify the updatable columns in the update-clause.
  For example, using the query above:
  
  select T1.C1, MYSQLUDF( T1.C2 ) A from T1 for update of C1, C2;
  

Problem summary

• SQL SCALAR FUNCTION IN SELECT-CLAUSE BLOCKS USE OF
  UPDATE-CLAUSEWITH SQLSTATE 42829
  

Problem conclusion

• First Fixed in DB2 V9.1 FP9
  

Temporary fix

• N/A
  

Comments

APAR Information

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

  JR31733

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

  IC64585 IC64586

Fix information

• Fixed component name

  DB2 UDB ESE WIN

• Fixed component ID

  5765F4101

Applicable component levels

• R910 PSY

     UP