IBM Support

PI91939: INCORRECT OUTPUT WHEN CAST FUNCTION ON DECIMAL AS INTEGER

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Incorrect output when using CAST FUNCTION such as "CAST(DECIMAL
    (98,14,5) AS INTEGER)" with a specific length of decimal.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 11 and Db2 12 for z/OS users of                      *
    * queries that CAST DECIMAL AS INTEGER or                      *
    * CAST DECIMAL AS SMALLINT.                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * The following two problems are being                         *
    * addressed with this apar.                                    *
    *                                                              *
    * An incorrect result set can be returned                      *
    * for a query that contains a                                  *
    * CAST DECIMAL AS INTEGER.                                     *
    *                                                              *
    * An ABEND04E rc00E70005 at DSNXGSFN M128                      *
    * can occur for a query that contains a                        *
    * CAST DECIMAL AS SMALLINT if the                              *
    * precision specified > 15 and the                             *
    * difference,  precision - scale < 5.                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    The following two problems are being addressed by this apar.
    
    Problem ONE.
    
    An incorrect result set can be returned for a query that
    contains a CAST DECIMAL AS INTEGER.
    
    Problem TWO.
    
    An ABEND04E rc00E70005 at DSNXGSFN M128 can occur for a query
    that contains a CAST DECIMAL AS SMALLINT if the precision
    specified > 15 and the difference, precision - scale < 5.
    
    
    The following examples will help to illustrate the two problems.
    
    Example 1.
    
    SELECT CAST(DECIMAL(98,14,5) AS INTEGER) AS DECIMAL_14_5
    FROM SYSIBM.SYSDUMMY1;
    
    The incorrect result returned.
    +----------------+
    |  DECIMAL_14_5  |
    +----------------+
    1_|            980 |
    +----------------+
    
    The expected or correct result.
    +----------------+
    |  DECIMAL_14_5  |
    +----------------+
    1_|             98 |
    +----------------+
    
    
    Example 2.
    
    For this example, please note.
    
    The precision specified is 16, the scale specified is 12.
    The difference, precision - scale < 5 since 16 - 12 = 4.
    
    SELECT
    CAST(DECIMAL(32.123456789123,16,12) AS SMALLINT)
    AS DECIMAL_16_12
    FROM SYSIBM.SYSDUMMY1;
    

Problem conclusion

  • The code in DB2 has been modified to correct the two
    aforementioned problems.
    
    Additional Keywords: SQLCAST SQLDECIMAL SQLINCORR
                         INCORROUT SQLINCORROUT DB2INCORR/K
                         ZSA3
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PI91939

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-01-03

  • Closed date

    2018-03-14

  • Last modified date

    2018-04-03

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

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

    UI54471 UI54472

Modules/Macros

  • DSNXGSFN
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI54471

       UP18/03/30 P F803 ¢

  • RB10 PSY UI54472

       UP18/03/30 P F803 ¢

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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI91939

Modified date: 03 April 2018


Translate this page: