RANDOM or RAND scalar function

The RANDOM function returns a random floating-point value in the range 0–1. An argument can be specified as an optional seed value.

FL 506

The schema is SYSIBM.

numeric-expression: If numeric-expression is specified, it is used as the seed value. The argument must be an expression that returns a value of a built-in integer data type (SMALLINT or INTEGER). The value must be in the range 0–2,147,483,646.
The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) and then assigned to an INTEGER value.

The result of the function is a double precision floating-point number.

The result can be null; if the argument is null, the result is the null value.

A specific seed value, other than zero, will produce the same sequence of random numbers for a specific instance of a RANDOM function in a query each time the query is executed. The seed value is used only for the first invocation of an instance of the RANDOM function within a statement. RANDOM(0) is processed the same as RANDOM().

Notes

Determinism:: RANDOM is a non-deterministic function.

Examples

Example 1

Assume that host variable HRAND is an INTEGER with a value of 100. The following statement returns a random floating-point number in the range 0–1, such as the approximate value .0121398:

   SELECT RANDOM(:HRAND)
     FROM SYSIBM.SYSDUMMY1;

Example

To generate values in a numeric interval other than 0 to 1, multiply the RAND function by the size of the interval that you want. For example, to get a random number in the range 0–10, such as the approximate value 5.8731398, multiply the function by 10:

   SELECT (RANDOM(:HRAND) * 10)
     FROM SYSIBM.SYSDUMMY1;