PI93887: ALLOW A MODIFIES SQL DATA FUNCTION TO BE INVOKED IN A SUBSELECT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Allow a MODIFIES SQL DATA function to be invoked in a subselect
  (38912)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Db2 11 and 12 for z/OS new function                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Allow a user-defined function defined                        *
  * with the MODIFIES SQL DATA attribute                         *
  * to be invoked in a subselect.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available.                         *
  ****************************************************************
  Db2 is enhanced so that a user-defined function defined as
  MODIFIES SQL DATA can be invoked in a subselect.  Existing
  restriction on invoking a user-defined function defined as
  MODIFIES SQL DATA from a subselect is removed.
  
  Example
  =======
  
  CREATE TABLE T1 (C1 INTEGER)!
  
  CREATE FUNCTION MYFUNCTION(P1 INTEGER)
  RETURNS INTEGER
  DETERMINISTIC
  NO EXTERNAL ACTION
  MODIFIES SQL DATA
  BEGIN
  DECLARE V1 INTEGER;
  
  SET V1 = P1 + 1;
  INSERT INTO T1 VALUES (V1);
  RETURN V1;
  
  END!
  
  SELECT MYFUNCTION(1) FROM SYSIBM.SYSDUMMY1!
  

Problem conclusion

Temporary fix

Comments

• Db2 is enhanced so that a user-defined function defined as
  MODIFIES SQL DATA can be invoked in a subselect.
  
  Additional Keyword: SQLUDF SQLCODE740
  

APAR Information

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

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

  UI54810 UI54848

Modules/Macros

• DSNXOOS2 DSNXOEXH
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI54848

     UP18/04/13 P F804

• RB10 PSY UI54810

     UP18/04/13 P F804

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.