PI47366: SQLCODE -122 ISSUED UNEXPECTEDLY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -122 is issued for a query that contains a GROUP BY
  with a user defined function (UDF) and the UDF contains an
  arithmetic expression.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of an SQL          *
  *                 statement containing a user defined          *
  *                 function in the GROUP BY list.               *
  ****************************************************************
  * PROBLEM DESCRIPTION: Unexpected SQLCODE122 M300              *
  *                      from DSNXOGB is received for an SQL     *
  *                      statement that satisfies all of the     *
  *                      following conditions:                   *
  *                      1. The SQL statement contains           *
  *                      a user defined function in the          *
  *                      GROUP BY list.                          *
  *                      2. One of the arguments of the          *
  *                      user defined function contains a        *
  *                      column expression and the other         *
  *                      argument contains a non column          *
  *                      expression.                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For example,
    CREATE FUNCTION UDF(P1 INT, P2 INT)
      RETURNS INT
      LANGUAGE SQL
      DETERMINISTIC
      READS SQL DATA
      NO EXTERNAL ACTION
      RETURN P1;
  
      SELECT
        C1, UDF(C2, 1+2), C3
        FROM T1
        GROUP BY C1, UDF(C2,1+2), C3;
  
  SQLCODE -122 may be issued for the statement above because DB2
  did not handle the GROUP BY clause that contains the user
  defined function correctly.
  

Problem conclusion

• DB2 was modified to handle the GROUP BY clause that contains the
  user defined function correctly to avoid issuing SQLCODE -122
  incorrectly.
  
  Additional Keywords: SQLUDF SQLFUNCTION 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:

  UI31648

Modules/Macros

• DSNXOOS1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI31648

     UP15/10/16 P F510

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.