Wednesday, October 30, 2013

Generating random numbers part II

In Generating random numbers part I I discussed what a pseudorandom numbers are and how to generate them using the CEERAN0 API. In this post I will discuss how to generate pseudorandom numbers using SQL.

I will be using the same scenario I did in part I. I need to generate 1 million random numbers, and write them to a DDS file, RANDOM_F.

The program looks very similar to the one in part I. I am not going to go into detail explaining what this program does, just the differences between it and the one in part I.

01 FRANDOM_F  O    E             DISK

02 D LowNbr          S                   like(KEY)
03 D HighNbr         S                   like(KEY)
04 D Range           S                   like(KEY) 
05 D i               S             10U 0
06 D RandomNbr       S              8F
07     HighNbr = 1000000 ;
08     LowNbr = 0 ;
09     Range = (HighNbr - LowNbr) + 1;

10     for i = 1 to 1000000 ;
11       exec sql  set :RandomNbr = RAND() ;
12       KEY = %int(RandomNbr * Range) ;
13       write RANDOM_FR ;
14     endfor;

15     *inlr = *on;

You can seed the RAND function, but I have chosen not to and allow it to self-seed. The program is almost identical to the one in part I. The only difference is line 11 where I execute the SQL, rather than call CEERAN0.

Below are the first five numbers that were produced during one of my testing runs. By all means a sample size of five cannot be definitive proof of anything, but it does give you an idea of what it can do:

 i  Floating point No. KEY
1 46253854.E-008 0462538
2 25608081.E-008 0256081
3 92571795.E-008 0925718
4 56761378.E-008 0567614
5 20947905.E-008 0209479

You can learn more about this from the IBM website:



Since writing part I and part II I have received information about two more methods of producing pseudorandom numbers:

  1. Calling a C function to generate the pseudorandom number.
  2. An API that creates a pseudorandom number that is deemed to be "cryptographically-secure".

Both of these will be discussed in future posts.


This article was written for IBM i 7.1, and it should work with earlier releases too.


  1. Per the IBM infocenter article you linked to:
    " If a seed value is not specified, a different sequence of random numbers is produced each time the query is executed."

    I believe, under the covers, the SQL RAND function is seeding itself with the current timestamp (or some other guaranteed unique value).

    1. You are right. Thank you for the info I don't know how I missed that.

      I have correct the post to reflect this.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.