IZ44872: In DPF environent, RAND() scalar fnuction returns repeating values.

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• In a DPF environment, rand scalar function does not work
  properly.
  
  It specified with/without seed value, rand function does not
  work properly.
  
  For example.
  Execute same sql 2 times.
  select c1,rand() from test1 order by 2
  select c1,rand() from test1 order by 2
  
  The expected results of these SQLs, returned random values from
  rand. However, it returned SAME VALUES.
  
  First time                      Second time
  C1      2                       C1       2
  -----   ----------------------  -----    ----------------------
      1   +0.00000000000000E+000      1    +0.00000000000000E+000
      6   +0.00000000000000E+000      6    +0.00000000000000E+000
     10   +0.00000000000000E+000     10    +0.00000000000000E+000
      5   +0.00000000000000E+000      5    +0.00000000000000E+000
      9   +0.00000000000000E+000      9    +0.00000000000000E+000
      8   +1.06753746147038E-001      8    +1.06753746147038E-001
      3   +3.04818872646260E-001      3    +3.04818872646260E-001
      2   +6.55171361430708E-001      2    +6.55171361430708E-001
      7   +6.55171361430708E-001      7    +6.55171361430708E-001
      4   +6.74977874080630E-001      4    +6.74977874080630E-001
  
  For example.
  Execute same sql 2 times.
  select c1,rand(1) from test1 order by 2
  select c1,rand(1) from test1 order by 2
  
  The expected results of these SQLs, returned same columns order
  from rand. However, it returned NOT SAME COLUMNS ORDER.
  
  First time                      Second time
  C1      2                       C1       2
  -----   ----------------------  -----    ----------------------
      6   +1.24698629718925E-001      9    +1.24698629718925E-001
      2   +1.71727652821436E-001      2    +1.71727652821436E-001
      9   +1.75725577562792E-001      7    +1.75725577562792E-001
      4   +2.26416821802423E-001      4    +2.26416821802423E-001
      5   +3.08633686330760E-001      6    +3.08633686330760E-001
      8   +4.94766075624866E-001      8    +4.94766075624866E-001
     10   +5.13870662556841E-001      5    +5.13870662556841E-001
      7   +5.34531693472091E-001     10    +5.34531693472091E-001
      3   +7.02230903042695E-001      3    +7.02230903042695E-001
      1   +9.47630237739189E-001      1    +9.47630237739189E-001
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * rand() returned incorrect results.                           *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See the APAR description.                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgraqde to DB2 UDB version 9.5 fixpack 5.                   *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 UDB Version V9.5 FixPack 5.
  

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:

  IC63946

Fix information

• Fixed component name

  DB2 UDB ESE AIX

• Fixed component ID

  5765F4100

Applicable component levels

• R950 PSN

     UP