how do i select random records in a sql query in DB2

[prasoonkc]

Hi I am working on a project where we have to migrate the database from oracle to db2. There is a query which picks up certain percentage of records randomly from a table. Currently this query is written in oracle but now I looking for a replacement for that in db2. The query uses DBMS_UTILITY.GET_HASH_VALUE() function of oracle.

SELECT DBMS_UTILITY.GET_HASH_VALUE (TO_CHAR(dbms_utility.get_time)||NU_EMP_ID
,2,1048576) "RANDOM_ORDER", NU_EMP_ID FROM DQDS_EMP

This query gives a sudo column RANDOM_ORDER the value of this column is always different for each time you run this query. after this query is run we pick up the first N records which is always diffenent for each time the query is run. Does DB2 have a similar hashing function like DBMS_UTILITY.GET_HASH_VALUE() of oracle.

Prasoon

 

[kregen]

hi prasoonkc,

i hope that i understand what you want.

you can use the rand funktion. here is the discription from the manual.

RAND(__________)
expression

The RAND function returns a random floating-point value between 0 and 1.An argument can be used as an optional seed value.

If an argument is specified, it must be an integer (SMALLINT or INTEGER)between 0 and 2 147 483 646.

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.

Example: Assume that host variable HRAND is an INTEGER with a value of 100. The following statement:

SELECT RANDHRAND)
FROM SYSIBM.SYSDUMMY1;


returns a random floating-point number between 0 and 1, such as the approximate value .0121398.


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


SELECT (RANDHRAND) * 10)
FROM SYSIBM.SYSDUMMY1;


i hope its helps,
cu
kai

 

[prasoonkc]

hi kregen

Thanks for the help. It was really useful. But i have run into another problem. Can i use order by clause in a nested query i,e the inner query contains order by clause. Db2 is giving me error when i use order by in inner query in a nested query. Also it does not allow me to use order by when i am creating a View.