PI46932: SLQCODE420 COULD BE RECEIVED WITH TABLE UDF CREATED AS NOT DETERMINISTIC AND NO EXTERNAL ACTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE420 could be received with TABLE UDF created as NOT
  DETERMINISTIC and NO EXTERNAL ACTION and the SQL referencing the
  UDF contains an explicit CAST
  
  Additional symptoms and search keywords
  SQLCDOE420 SQLCAST SQLTABLEUDF SQLTUDF
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS               *
  *                 users of NOT DETERMINISTIC                   *
  *                 SQL inline table user defined                *
  *                 functions.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: When an SQL table user-defined          *
  *                      function (UDF) is defined with the      *
  *                      NOT DETERMINISTIC option, the intent    *
  *                      could be to encapsulate  the result     *
  *                      of the table UDF into a work file so    *
  *                      that the referencing query is           *
  *                      not exposed to the table rows directly  *
  *                      from the UDF definition. DB2 may        *
  *                      merge the table UDF definition, for     *
  *                      better performance, into the            *
  *                      referencing query, regardless the       *
  *                      NOT DETERMINISTIC option is specified   *
  *                      or not, which then can expose the       *
  *                      table rows from the UDF definition      *
  *                      to the referencing query.               *
  *                      If the referencing query does not       *
  *                      expect such table rows directly from    *
  *                      the table UDF definition, the query     *
  *                      could produce an unexpected result,     *
  *                      for example, an unexpected SQLCODE.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For Example,
  
  CREATE TABLE TEST1 (C1 INT, C2 CHAR(10));
  CREATE TABLE TEST2 (C1 INT, C2 CHAR(10));
  
  INSERT INTO TEST1 VALUES (1,'1');
  INSERT INTO TEST1 VALUES (2,'2');
  INSERT INTO TEST1 VALUES (3,'A');
  
  INSERT INTO TEST2 VALUES (1,'1');
  INSERT INTO TEST2 VALUES (2,'2');
  INSERT INTO TEST2 VALUES (3,'A');
  
  CREATE FUNCTION TESTUDF (P1 INT)
     RETURNS TABLE (C1 INT, C2 CHAR(10))
     RETURN SELECT  C1, C2 FROM TEST1 WHERE C1 < 3;
  
   The TESTUDF function is implicitly NOT DETERMINISTIC which
  is the default option.
  
  SELECT TX.C2 FROM TEST2, TABLE (TESTUDF(3))TX WHERE
      TEST2.C1 = TX.C2;
  
  The predicate compares TEST2.C1 which is an integer column with
  TX.C2 which is defined as CHAR from the table function.
  
  Since DB2 decides to merge the table function to the
  referencing query, all the table rows will be exposed.
  The first two values that will be returned from the table
  function ('1' and '2') can be implicitly cast to integer,
  However, the last value ('A') cannot be cast to integer.
  In this case, SQLCODE420 is be issued.
  
  After this fix, when the MATERIALIZE_NODET_SQLTUDF
  subsystem parameter is set to YES, DB2 will disable the
  merging of NOT DETERMINISTIC SQL table functions so that only
  the rows that returned from the table function are exposed.
  In this case, SQLCODE will not be issued.
  

Problem conclusion

• If the NOT DETERMINISTIC option is specified in an SQL table
  UDF definition for the purpose of isolating the UDF result
  into a work file, so that the referencing query is
  not exposed to the table rows directly from the UDF
  definition, the new system parameter MATERIALIZE_NODET_SQLTUDF
  should be set to YES so that DB2 will not merge the table UDF,
  instead DB2 will materialize the table UDF result into a work
  file, otherwise keep MATERIALIZE_NODET_SQLTUDF set to NO so
  that DB2 will continue to merge the table UDF  definition into
  the referencing query for better performance.
  
  Additional Keywords: SQLTABLEUDF SQLINLINEUDF
  

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:

  UI36825 UI36826

Modules/Macros

• DSN@XAZP DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIDXB DSNTIJUZ DSNTILM3
  DSNTINMF DSNTINM1 DSNTINM3 DSNTINST DSNTIVIA DSNTIVIE DSNTIVIN
  DSNTIVMD DSNTIVMS DSNTIWIA DSNTIWIE DSNTIWIN DSNTIWMD DSNTIWMS
  DSNTXAZP DSNWZIFA DSNWZIFB DSNXOFD  DSNXOMPS DSNXOMTX DSNXOPRE
  DSNXOVC  DSNXOVD  DSNXOVM  DSNXOV1  DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI36825

     UP16/04/23 P F604

• RB10 PSY UI36826

     UP16/04/26 P F604

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.