DB2 FOR LUW / DB2FORLUW / B10 - DB2

The precision handling of round() scalar function is not
correctly processed on column-organized table and the result of
round() scalar function with floating point expresion may be
different from one on row-organized table around the rounding
boundary.

  The following is a sample senario of round() scalar function
on column-organized table.

$ db2set -all
[i] DB2_WORKLOAD=ANALYTICS
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON [DB2_WORKLOAD]
[i] DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD]

$ db2 "create table TABROW ( COL_FLOAT float, COL_DECFLOAT
decfloat ) organize by row"
$ db2 "insert into  TABROW values ( 0.02875, 0.02875 )"
$ db2 "create table TABCOL ( COL_FLOAT float, COL_DECFLOAT
decfloat ) organize by column"
$ db2 "insert into  TABCOL values ( 0.02875, 0.02875 )"

$ db2 "select COL_FLOAT, round( COL_FLOAT, 4 ) ROUND4_COL_FLOAT
from TABROW"

COL_FLOAT                ROUND4_COL_FLOAT
------------------------ ------------------------
  +2.87500000000000E-002   +2.87000000000000E-002

  1 record(s) selected.

$ db2 "select COL_FLOAT, round( COL_FLOAT, 4 ) ROUND4_COL_FLOAT
from TABCOL"

COL_FLOAT                ROUND4_COL_FLOAT
------------------------ ------------------------
  +2.87500000000000E-002   +2.88000000000000E-002

  1 record(s) selected.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
****************************************************************

Use more precise numeric-expression2 in ROUND scalar function,
then estimate your requiring precision like as below.

$ db2 "select COL_FLOAT, round( COL_FLOAT, 3 ) ROUND3_COL_FLOAT,
round( COL_FLOAT, 4 ) ROUND4_COL_FLOAT, round( COL_FLOAT, 5 )
ROUND5_COL_FLOAT from TABROW"

COL_FLOAT                ROUND3_COL_FLOAT
ROUND4_COL_FLOAT         ROUND5_COL_FLOAT
------------------------ ------------------------
------------------------ ------------------------
  +2.87500000000000E-002   +2.90000000000000E-002
+2.87000000000000E-002   +2.87500000000000E-002

  1 record(s) selected.

$ db2 "select COL_FLOAT, round( COL_FLOAT, 3 )
ROUND3_COL_FLOATT, round( COL_FLOAT, 4 ) ROUND4_COL_FLOAT,
round( COL_FLOAT, 5 ) ROUND5_COL_FLOAT from TABCOL"

COL_FLOAT                ROUND3_COL_FLOATT
ROUND4_COL_FLOAT         ROUND5_COL_FLOAT
------------------------ ------------------------
------------------------ ------------------------
  +2.87500000000000E-002   +2.90000000000000E-002
+2.88000000000000E-002   +2.87500000000000E-002

  1 record(s) selected.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.11.2018
16.01.2020
16.01.2020