Re: Randomizing records in a file

[CRPence <CRPbottle@xxxxxxxxx>]

  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?