JR27937: ERROR -901 RETURNED WHEN INVOKING A FUNCTION THAT HAS THE "MODIFIES SQL DATA " ATTRIBUTE."

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

• the following criteria are required to reproduce the problem:
  
  - a for loop that has a SELECT statement to define a cursor
  - the SELECT statement in the for-loop cursor has a common table
  expression. i.e. using the WITH clause.
  - the select-statement references a table function with MODIFIES
  SQL DATA
  
  e.g.
  
  create function fn_mod_bug(v char(1))
  modifies sql data
  returns table (c char(1))
  begin atomic
  return select v from sysibm.sysdummy1;
  end
  @
  
  begin atomic
   declare a char(1);
   for v1 as
     with t (c) as
     (
     select f.c from sysibm.sysdummy1 d,
  table(fn_mod_bug(d.ibmreqd)) f(c)
     )
     select c from t
   do
     set a=v1.c;
   end for;
  end
  @
  

Local fix

• remove the "modifies sql data " clause if possible.
  

Problem summary

• Users affected : users having functions with "modifies sql data"
  attribute Problem description : incorrect -901 error returned
  Problem Summary : this apar fix avoids this error.
  

Problem conclusion

• First fixed in DB2 UDB Version 9, Fixpak 5
  

Temporary fix

Comments

APAR Information

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

  JR27916

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

Fix information

• Fixed component name

  DB2 UDB ESE WIN

• Fixed component ID

  5765F4101

Applicable component levels

• R910 PSY

     UP