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 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

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?

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].