PM13316: SQLCODE -302 ON A SELECT IN A NATIVE SQL PROCEDURE WITH A FIELD PROC DEFINED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• User has a table with a field procedure defined. When converting
  an external SQL procedure to a Native SQL procedure, the SELECT
  now gets a -302 SQLCODE (SQLCODE302).  This is the where clause:
  WHERE FP_COL = var ;
  where FP_COL is the column with the field proc defined.  If the
  query is changed to
  WHERE FP_COL = var || '' ;
  then no -302 is returned.
  

Local fix

• Workaround: change the query to concatenate an empty string.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R910 users of native SQL procedures  *
  *                 which reference a table column which is      *
  *                 defined with a field procedure.              *
  *                                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output could occur when       *
  *                      executing a native SQL procedure that   *
  *                      references an SQL variable or           *
  *                      procedure parameter in a SQL statement  *
  *                      containing a table column defined with  *
  *                      a field procedure.                      *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Incorrect output could occur when
  executing a native SQL procedure that references an
  SQL variable or procedure parameter in a SQL statement
  containing a table column defined with a field procedure.
  
  Example:
  
  CREATE TABLE T1 (C1 VARCHAR(5) FIELDPROC FPCVD4);
  INSERT INTO T1 VALUES('12345');
  
  CREATE PROCEDURE MYTEST2 (INOUT NUM INT)
   L1: BEGIN
    DECLARE A CHAR(5);
    SET A   = '12345';
    SELECT 9 INTO NUM FROM T1 WHERE C2 = A;
   END L1
  
  The SQL variable A in the predicate does not apply the
  definition of the field procedure correctly for comparison.
  

Problem conclusion

• DB2 is updated to handle SQL variables which need the
  application of field procedures correctly.
  
  Additional Keywords: SQLNATIVESQLPL SQLFIELDPROC SQLINCORR
                       INCORROUT SQLINCORROUT
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK60221

Modules/Macros

• DSNXOFL  DSNXOOP  DSNXOPL  DSNXOPT  DSNXOYPL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK60221

     UP10/09/18 P F009

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.