PI87661: OFFLOADING QUERY TO IDAA GET SQLCODE901 SQLERRM 42S02 AFTER INSTALLING PTFS FOR PI60480 17/09/21 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The following scalar functions currently do not work when the
  input is a literal, parameter marker or host variable
  DAYOFWEEK(), DAYOFWEEK_ISO(), DAYOFYEAR(), DAYOFYEAR(), YEAR(),
  WEEK_ISO(), MONTH(), QUARTER().
  
  For example YEAR('2017-09-30').
  
  It returns
  SQLCODE=-901/ SQLERRM=42S02: ERROR:  Function
  'DATE_PART(UNKNOWN,UNKNOWN)'
  
  does not exist  / SQLERRP=AQT
  

Local fix

• As a workaround, a DATE() cast can be added to the argument of
  the scalar function, for example:
  this DB2 query that currently fails:
  SELECT YEAR('2017-09-30')  FROM MATRIX.TIMEMATRIX;
  
  is rewritten to the following:
  SELECT YEAR(DATE('2017-09-30'))  FROM MATRIX.TIMEMATRIX;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of DB2 QUERY       *
  *                 ACCELERATION and queries that use the        *
  *                 following scalar functions:                  *
  *                 DAYOFWEEK(), DAYOFWEEK_ISO(), DAYOFYEAR(),   *
  *                 YEAR(), WEEK_ISO(), MONTH(), QUARTER()       *
  ****************************************************************
  * PROBLEM DESCRIPTION: With APAR PI60480 (PTF UI39472)         *
  *                      applied, queries that use the           *
  *                      scalar functions DAYOFWEEK(),           *
  *                      DAYOFWEEKISO(), DAYOFYEAR(), YEAR(),    *
  *                      WEEK_ISO(), MONTH(), or QUARTER()       *
  *                      could fail on the Accelerator with      *
  *                      SQLCODE -904 and message text token     *
  *                      as follows:                             *
  *                      "ERROR: Function 'DATE_PART(UNKNOWN,    *
  *                       UNKNOWN)' does not exist"              *
  *                                                              *
  *                      The query should not have failed on     *
  *                      the Accelerator and was successful      *
  *                      prior to PI60480.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  With APAR PI60480 applied, queries that use the scalar functions
  DAYOFWEEK (), DAYOFWEEKISO (), DAYOFYEAR (), YEAR (),
  WEEK_ISO (), MONTH (), or QUARTER () could fail the Accelerator
  with SQLCODE -904 and message text token as follows:
  "ERROR: Function 'DATE_PART(UNKNOWN,UNKNOWN)' does not exist"
  
  When the input expression to the listed scalar functions above
  is a character string, the query will fail with SQLCODE -904.
  For example, MONTH('2017-08-01') or MONTH(?) where the
  parameter marker value is a character string with the value
  '2017-08-01'.
  This usage of MONTH() should not have failed on the Accelerator
  and was successful prior to PI60480.
  
  If the input expression is a datetime arithmetic expression,
  the query will execute successfully and is not affected by
  PI60480. For example:
   YEAR ( CURRENT DATE - '2005-01-30' )
  

Problem conclusion

• DB2 code was changed to correctly transform the query.
  
  To make this fix effective for a static query that was
  previously bound for acceleration (using bind option
  QUERYACCELERATION ), the DB2 application package must be rebound
  after application of this PTF.
  
  Additional search keywords:  IDAAV4R1/K IDAAV5R1/K
  SQLDAYOFWEEK SQLDAYOFWEEK_ISO SQLDAYOFYEAR SQLYEAR SQLWEEK_ISO
  SQLMONTH SQLQUARTER
  

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:

  UI51270

Modules/Macros

• DSNXONZS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI51270

     UP17/10/24 P F710 Ø

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.