PH29175: SQLCODE199 REBINDING AN SQLPL PACKAGE WHICH CONTAINS SQL WITH A TABLE EXPRESSION THAT HAS AN ORDER BY WITH VARIABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -199 is issued when rebinding an SQLPL package which
  contains an SQL STATEMENT with a table expression, and the
  table expression has an ORDER BY clause with variable as the
  sort key.
  
  Keywords:
  SQLTABLEEXPR SQLORDERBY SQLNATIVESQLPL
  

Local fix

• BYPASS/CIRCUMVENTION:
  Update the procedure code by removing ORDER BY
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of native SQL                      *
  * procedure, compiled SQL scalar function,                     *
  * or advanced trigger with ORDER BY clause                     *
  * in a routine body.                                           *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Unexpected SQLCODE -199 could be                             *
  * issued when rebinding a native                               *
  * SQL procedure, compiled SQL scalar                           *
  * function, or advanced trigger if the                         *
  * routine body satisfies the following                         *
  * conditions:                                                  *
  * 1.It contains a SELECT statement with                        *
  *   a table expression                                         *
  * 2.The table expression contains an                           *
  *   ORDER BY clause                                            *
  * 3.The sortkey of the ORDER BY clause                         *
  *   is a standalone SQL variable                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  The following example could recreate the problem:
   CREATE FUNCTION MYFUN()
   RETURNS VARCHAR(4)
   MODIFIES SQL DATA
    L1: BEGIN
    DECLARE NUM INTEGER;
    DECLARE VAR1 CHAR(10);
     SELECT 1 INTO NUM FROM (
     SELECT T1.C1
      FROM T1
      ORDER BY VAR1 ) ;
    RETURN('AAAA');
    END L1
  Db2 didn't handle the ORDER BY clause in the table expression
  correctly when CREATE the above compiled SQL scalar function
  which causing SQLCODE -199 issued during rebinding.
  

Problem conclusion

• Db2 is updated to handle the aforementioned native SQL
  procedure, compiled SQL scalar function and advanced trigger
  correctly.
  Keywords: DB2 12 for z/OS, SQLFUNCTION, SQLNATIVESQLPL,
  SQLSETFUNCTION, SQLUDF, SQLPROCEDURE, SQLSP,
  SQLSTOREDPROC, and SQLTRIGGER
  

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:

  UI72147

Modules/Macros

• DSNXOP0
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI72147

     UP20/10/28 P F010

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.