PH10882: SQLCODE420 OCCURRED WHEN A QUERY HAS A PREDICATE COMPARING A DOUBLE COLUMN WITH A DECIMAL VALUE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE420 issued by DSNXRNUM at location -226 could occur when
  all the following conditions match:
  1/ Customer has DECIMAL=COMMA in their DECP.
  2/ The query has a predicate comparing a DOUBLE column with a
  DECIMAL value which uses PERIOD as the decimal point. For
  example, the DECIMAL value is 0.00 in the predicate.
  3/ The application is bound with CONCENTRATESTMT=YES (Statement
  Concentration option is enabled).
  4/ The first time the query ran successfully but the next
  execution got SQLCODE -420.
  

Local fix

• Rebind the application with CONCENTRATESTMT=NO.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 and 12 for z/OS users of DB2      *
  *                 dynamic statement caching for dynamic        *
  *                 SQL that is prepared with attribute          *
  *                 CONCENTRATE STATEMENTS WITH LITERALS         *
  *                 and the decimal point in the                 *
  *                 DECIMAL DECP is a COMMA.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -420 may occur for an SQL       *
  *                      statement that satisfies  all of        *
  *                      the following conditions:               *
  *                      1. The statement contains a numeric     *
  *                      literal constant with a decimal         *
  *                      point separator.                        *
  *                      2. The numeric literal constant is      *
  *                      driven to float or decimal float        *
  *                      type.                                   *
  *                      3. The statement is prepared with       *
  *                      attribute CONCENTRATE STATEMENTS        *
  *                      WITH LITERALS.                          *
  *                      4. The decimal point in the DECIMAL     *
  *                      DECP is a COMMA.                        *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  For Example,
      CREATE TABLE T1 (C1 DOUBLE);
  
      SELECT C1   FROM T1 WHERE C1 = 3.4  ;
  
      SELECT C1   FROM T1 WHERE C1 = 1.4  ;
  
  SQLCODE -420 may be issued incorrectly for the second
  statement above when it is prepared with CONCENTRATE
  STATEMENTS WITH LITERALS and the decimal point in
  DECIMAL DECP is a COMMA.
  
  The SQLCODE is issued incorrectly because Db2 did
  not handle numeric constant with decimal point
  separator correctly.
  

Problem conclusion

• DB2 has been modified so no SQLCODE will be issued  for the
  aforementioned SQL statement.
  
  Additional keywords: SQLDECIMAL SQLDECFLOAT SQLCOMMA
                       SQLDOUBLE  SQLFLOAT  DSNHDECP
  

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:

  UI63569 UI63570

Modules/Macros

•    DSNXOCKL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI63570

     UP19/06/28 P F906

• RC10 PSY UI63569

     UP19/06/26 P F906

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.