PI77725: SQLCODE312 ISSUED FOR NESTED FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE312 issued for nested function when sublevel function
  returns the value of its input parameter as a column of its
  output table, and the calling function has this output column as
  part of its GROUP BY clause.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of nested SQL      *
  *                 table functions with an SQL statement        *
  *                 containing GROUP BY.                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE312 could be incorrectly         *
  *                      issued for an SQL statement that        *
  *                      references an SQL table function        *
  *                      with the SQL routine body satisfying    *
  *                      all of the following conditions:        *
  *                                                              *
  *                      1. It references another SQL table      *
  *                         function.                            *
  *                      2. It contains GROUP BY clause that     *
  *                         references the SQL table function    *
  *                         column.                              *
  *                      3. The GROUP BY column from the SQL     *
  *                         table function column is originally  *
  *                         from a function parameter or global  *
  *                         variable.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(C1 INT);
  
  CREATE FUNCTION TUDF1(P1 INT)
    RETURNS TABLE(C1 INT, C2 INT)
    DETERMINISTIC
    RETURN
    SELECT TUDF1.P1, T1.C1
    FROM T1
  ;
  
  CREATE FUNCTION TUDF2(P1 INT)
    RETURNS TABLE(C1 INT, C2 INT)
    DETERMINISTIC
    RETURN
    SELECT C1, MAX(C2)
    FROM TABLE (TUDF1(TUDF2.P1))
    GROUP BY C1
  ;
  
  SELECT *
  FROM TABLE (TUDF2(1))
  ;
  
  DB2 did not correctly process the SELECT statement, and
  SQLCODE312 was incorrectly issued.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, and there will be no SQLCODE312.
  
  Additional keywords: SQLTUDF SQLTABLEUDF SQLGROUPBY
  

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:

  PI77727 UI46218

Modules/Macros

• DSNXOP0
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI46218

     UP17/04/24 P F704

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.