Seeding db2's RAND function

Most computers cannot generate truly random numbers. For this reason, many computer programming languages include pseudo-random number generators. These pseudo-random generators usually need to be seeded so that they don't always reproduce the same sequence of numbers.

The most common approach to seeding the generator, is to do so once per execution / session - ether manually (eg C/C++), via a constructor (eg Java), or automatically (eg JavaScript/ECMAScript). However, IBM's db2 allows the programmer to optionally seed the generator each time the RAND function is called, and not in any other way.

So, given that the common approach mentioned above is a good ballance between efficiency and randomness, I have written a really simple RANDOMIZE stored procedure for db2 that can be called once per session to increase the probability that each session will receive a unique sequence of psuedo-random numbers. And here it is:

CREATE PROCEDURE randomize ( )
  DYNAMIC RESULT SETS 0
  READS SQL DATA
  NOT DETERMINISTIC
  LANGUAGE SQL
  NO EXTERNAL ACTION
BEGIN
  DECLARE time TIMESTAMP;
  DECLARE seed BIGINT;
  DECLARE ignored DOUBLE;

  SET time = CURRENT_TIMESTAMP;
  SET seed = ( BIGINT(MINUTE(time) ) * 60 + SECOND(time) ) * 1000000 + MICROSECOND(time);

  SET ignored = RAND(INTEGER(MOD(seed,2147483648)));
END

There's really nothing fancy about this stored procedure... it simply uses the current time (with microsecond accuracy) to seed db2's psuedo-random number generator. So a typical use might look something like this:

...
CALL RANDOMIZE();
...
SET random_number_1 = RAND();
SET random_number_2 = RAND();
...
SET random_number_n = RAND();
...

Very simple :)

Paul.