DB2 FOR LUW / DB2FORLUW / B10 - DB2

(1) SQL0801 (divide by zero) is possible from 
sysibm.stddev_samp, sysibm.variance_samp and covariance_samp 
routines when applied in an OLAP specificat 
ion. 
 
Repro instructions: 
 
create table SUPPLY ( SNO varchar(20), QTY integer ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S1', 100 ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S1', 200 ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S2', 300 ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S2', 400 ) 
DB20000I  The SQL command completed successfully. 
 
select stddev_samp(QTY) over ( order by SNO ) from supply 
 
1 
------------------------ 
  +7.07106781186548E+001 
 
SQL0801N  Division by zero was attempted.  SQLSTATE=22012 
 
SQL0801N can also be obtained when using sysibm.variance_samp or 
sysibm.covariance_samp in place of sysibm.stddev_samp. 
 
(2) Wrong results are possible from sysibm.stddev_samp, 
sysibm.variance_samp and covariance_samp routines when applied 
in an OLAP specification. 
 
Repro instructions: 
 
create table SUPPLY ( SNO varchar(20), QTY integer ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S1', 100 ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S1', 200 ) 
DB20000I  The SQL command completed successfully. 
 
insert into SUPPLY values ( 'S1', 200 ) 
DB20000I  The SQL command completed successfully. 
 
select QTY, stddev_samp(QTY) over (order by SNO) from supply 
where SNO='S1' 
 
QTY         2 
----------- ------------------------ 
        100   +5.77350269189626E+001 
        200   +8.16496580927726E+001 
        200 
 
  3 record(s) selected. 
 
Expected is +5.77350269189626E+001 for each row. 
 
Similar wrong results can also be obtained when using 
sysibm.variance_samp or sysibm.covariance_samp in place of 
sysibm.stddev_samp.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users using SYSIBM.STTDEV_SAMP, SYSIBM.VARIANCE_SAMP or      * 
* SYSIBM.COVARIANCE_SAMP                                       * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Apply v11.1 Fixpack 1 to the DB2 server.                     * 
****************************************************************

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
 DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in v11.1 Fixpack 1

See LOCAL FIX.

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

17.08.2016
18.05.2017
18.05.2017