RE: SQL RAND function question

[<lgoodbar@xxxxxxxxxxxxxx>]

Wow. The distribution is fairly even except for the topmost number in
the range, regardless of the multiplier. Even tried a permutation such
as (rand()*121)/11 but the top number was still short in the
distribution.

The argument for the RAND function is the seed value. I omitted it in my
runs to get a new seed every time the RAND function is called. For
instance, when I called with rand(-1)*11, I got the number 5, 10000
times for a run of 10000.

Have you filed a PMR? This is reproducible.

Created a table:
create table loyd/randno (number int)

Wrote a program to populate based on 0-11 range:
d x               s             10i 0 inz
 /free
    exec sql set option commit=*none;
    for x = 1 to 1000000;
      exec sql insert into loyd/randno (number)
             values(cast(rand()*11 as decimal(2,0)));
    endfor;
    *inlr = *on;
    return;
 /end-free

Verified the file contained the correct number of records (in the first
run, 1,000,000).

Results in SQL:
select number, count(*) from loyd/randno group by number order by 1

....+....1....+....2....+....3 
       NUMBER     COUNT ( * )  
            0          90,751  
            1          90,312  
            2          91,026  
            3          90,885  
            4          91,061  
            5          91,168  
            6          90,453  
            7          91,287  
            8          90,914  
            9          90,861  
           10          91,245  
           11              37  
********  End of data  ********

Results for 100,000:
....+....1....+....2....+....3 
       NUMBER     COUNT ( * )  
            0           9,183  
            1           8,986  
            2           9,275  
            3           9,190  
            4           9,109  
            5           8,926  
            6           9,132  
            7           9,092  
            8           9,082  
            9           8,987  
           10           9,034  
           11               4  
********  End of data  ********

Results for 10,000 (11 does not show):
....+....1....+....2....+....3 
       NUMBER     COUNT ( * )  
            0             891  
            1             899  
            2             938  
            3             947  
            4             907  
            5             890  
            6             906  
            7             881  
            8             883  
            9             919  
           10             939  
********  End of data  ********


Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Wednesday, February 13, 2008 07:15
To: Midrange Systems Technical Discussion
Subject: RE: SQL RAND function question

Loyd,

Yes it does.  BUT if you call it 1000's of times, it doesn't given you
an even distribution of numbers
between 0-11.

By definition, RAND() should given an even distribution.

Charles Wilt 
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx