IBM Support

PM70908: SQLCODE420 INCORRECTLY ISSUED FOR A QUERY USING THE DECIMAL FUNCTION WITH DECIMAL=COMMA IN THE DSNHDEC 12/08/21 PTF PECHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • For a query like this:
    
     SELECT X FROM
     ( SELECT '1,1' AS X FROM "SYSIBM".SYSDUMMY1 ORDER BY X ) AS T1
        WHERE DECIMAL(X)            > 0  ---- CHANGES ',' TO '.'
          AND DECIMAL(X, 1, 0, ',') < 0  ---- RECEIVES  SQLCODE -420
     ---------+---------+---------+---------+---------+---------+---
    
     X
     ---------+---------+---------+---------+---------+---------+---
    Receives this SQLCODE:
    
     DSNE610I NUMBER OF ROWS DISPLAYED IS 0
     DSNT408I SQLCODE = -420, ERROR:  THE VALUE OF A STRING ARGUMENT
    WAS
              NOT ACCEPTABLE TO THE DECIMAL FUNCTION
     DSNT418I SQLSTATE   = 22018 SQLSTATE RETURN CODE
     DSNT415I SQLERRP    = DSNXRDEC SQL PROCEDURE DETECTING ERROR
     DSNT416I SQLERRD    = -20031 0  0  -1  0  0 SQL DIAGNOSTIC
    INFORMATION
     DSNT416I SQLERRD    = X'FFFFB1C1'  X'00000000'  X'00000000'
              X'FFFFFFFF' X'00000000'  X'00000000'
              SQL DIAGNOSTIC INFORMATION
    
    when DECIMAL=COMMA in the DSNHDECP in the system zparms.
    

Local fix

  • Restore PTF UK79144.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  All DB2 10 for z/OS users of the DECIMAL    *
    *                  function with character input that have     *
    *                  PTF UK79144 applied.                        *
    ****************************************************************
    * PROBLEM DESCRIPTION: After the application of PTF UK79144,   *
    *                      SQLCODE420 may be incorrectly returned  *
    *                      for an SQL statement using the DECIMAL  *
    *                      function with character input.          *
    *                      Incorrect output is another possible    *
    *                      symptom.                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An SQL statement containing the DECIMAL built-in function can
    return incorrect output if all of the following conditions
    are true:
    
    1. the input expression is character
    2. the input expression contains a decimal character
    3. the decimal character is a comma
    4. the input expression is referenced elsewhere in the SQL
       statment.
    
    DB2 incorrectly modifies the input expression and replaces the
    comma with a period. SQLCODE -420 or incorrect output may
    occur if the input expression is used again in the SQL
    statement. The problem can also occur for the syntax
    CAST(string AS DECIMAL).
    
    Some examples of the problem follow:
    
     CREATE TABLE T1 (C1 CHAR(3) NOT NULL);
     INSERT INTO T1 VALUES ('1,1');
    
     SELECT C1
       FROM  T1
       WHERE DECIMAL(C1)  > 0    ;
     The value '1.1' is incorrectly returned for C1
     instead of '1,1' for this SQL statement.
    
    
     SELECT C1
       FROM  T1
       WHERE DECIMAL(C1)  > 0
         AND DECIMAL(C1, 1, 0, ',') > 0  ;
     SQLCODE -420 is incorrectly returned for this SQL
     statement because DB2 modifies the input value for
     C1 and the second invocation of the DECIMAL function
     fails.
    

Problem conclusion

  • DB2 code has been changed to use a temporary buffer for the
    input expression before modifying the decimal character so
    that other references to the expression are not affected.
    
    Additional keywords : SQLDECIMAL SQLCAST DB2INCORR/K
                          SQLINCORR SQLINCORROUT INCORROUT
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM70908

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-08-14

  • Closed date

    2012-10-29

  • Last modified date

    2012-12-04

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

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

    UK83088

Modules/Macros

  • DSNXRDEC DSNXRGPL DSNXRIHB DSNXRIHD DSNXRIHR
    DSNXRIHS DSNXROHB DSNXROHR DSNXROHS DSNXROH2 DSNXRSPG
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK83088

       UP12/11/14 P F211

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
04 December 2012