Re: Randomizing records in a file

Curious... Why two columns? Also seems little reason to code to the APIs [for random numeric] when SQL has already done effectively that with its scalar function RAND.

Given that both adding a column and defining a LF to establish a keyed access path over the random value is acceptable, then the collation is not required for a physical order. Thus order of selected and fetched data is all that matters. If an SQL query is acceptable to retrieve the data, then the CREATE VIEW I suggested in http://archive.midrange.com/rpg400-l/201206/msg00010.html should work well. If not, then the same idea described there can be applied generically to modify a physical TABLE:

alter table the_file add column r double
;
update the_file set r = rand()
;
create index the_file_rand
on the_file (r) /* not unique; rand() may give duplicates */
;

If the TABLE should not be modified or RLA I/O is desirable, and building an access path or temporary for a query ODP is acceptable at run-time [i.e. when the data should be ordered by the /random/ value], then an OPNQRYF request could be used to order the data from the VIEW.

Regards, Chuck

On 04 Jun 2012 13:29, Albert York wrote:

This seems to be the simplest way. I can add a field containing a
random number plus the record number and use a logical over it.

On Mon, Jun 4, 2012 at 12:21 PM, Nathan Andelin wrote:

We have a couple procedures which are based on the CEERANO api;
one to generate random numbers, another to generate random strings.
Do you have a field in your 12 million record file for storing a
random value? If not, consider generating a new table with one
column containing a random value, and another field containing a
sequential number representing the record number of the other
file.

Albert York on Monday, June 4, 2012 12:58 PM wrote:

I have a file that contains around 12 million records. I need a
way to randomize the order of the file in the most efficient
method. Does anyone have any ideas?