CREATE FUNCTION (SQL scalar) or CREATE FUNCTION (SQL table) with a data access classification of CONTAINS SQL may fail with SQLSTATE '2F004' and SQLCODE -579

In IBM i 7.2 with PTF SI63618, in some cases, a CREATE FUNCTION (SQL scalar) or CREATE FUNCTION (SQL table) with a data access classification of CONTAINS SQL will return an error where unqualified success used to be returned. Instead of SQLSTATE of '00000' and SQLCODE = 0, CREATE FUNCTION (SQL scalar) or CREATE FUNCTION (SQL table) may return an SQLSTATE of '2F004' and SQLCODE -579. The SQLSTATE '2F004' error indicates that reading SQL data is not permitted within the scalar function or table function. If the scalar function or table function returns a query, the function must have a data access classification of READS SQL DATA or MODIFIES SQL DATA. It cannot have a data access classification of CONTAINS SQL.

To determine whether you have this exposure, query the QSYS2/SYSROUTINES table:

SELECT *
           FROM QSYS2.SYSROUTINES
           WHERE (SQL_DATA_ACCESS = 'CONTAINS' AND
                 ROUTINE_TYPE = 'FUNCTION' AND
                 ROUTINE_BODY = 'SQL') AND
                (FUNCTION_TYPE = 'T' OR
                 FUNCTION_TYPE = 'S') AND
                (UPPER(ROUTINE_DEFINITION) LIKE '%SELECT%' OR
                 UPPER(ROUTINE_DEFINITION) LIKE '%UNION%')

To execute this query, you will need to make sure the job CCSID is set to a value other than 65535. Examine the contents of the ROUTINE_DEFINITION column for all returned routines. If the routine returns a query, the function should have it's data access changed from CONTAINS SQL to either READS SQL DATA or MODIFIES SQL DATA to create successfully.