Re: Random number access on the i5

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  To implement the random selection of a row with a database SQL using 
the RAND() function, which presumably is available in most SQL, a couple 
possible examples are given below.  The example queries select a unique 
keyed value with a random row number; as written, the file referenced in 
the FROM clause require a user with *ALLOBJ special authority to run.  I 
would suggest to model only after the first example using a CTE, and I 
think that will meet the two stated requirements, deferring the /random/ 
to the database documentation.

 -- RAND() is inclusive zero and one, so limit bounds, and add one
 -- which assumes there is at least one row eligible to /win/

 -- The following queries select a random lib.file from the
 -- system database cross reference table QADBXREF in QSYS
 -- as the /winning/ user database file

 -- The first using CTE [Common Table Expression] compresses
 -- deleted rows which would prevent use of RRN(), and also
 -- handles removal of ineligible rows before selecting the
 -- random /winning/ row.  For example if the winning row must
 -- represent a user SQL TABLE, then add to the CTE WHERE clause:
 --     and dbxatr = 'TB'

 with
  compressed (dbxlib, dbxfil, rownbr) as
   (select dbxlib,dbxfil        /* unique key winner  */
          ,row_number() over () /* number each row    */
    from qsys.qadbxref          /* like SYSTABLES     */
    where  dbxlib not like 'Q%' /* Elim. ineligible   */
       and dbxlib not like '#%' /* Q & # pfx non-user */
   )
 select dbxlib,dbxfil,rownbr    /* rownbr optional    */
 from compressed Q
 where rownbr = ( select int((rand()*(select count(*)-1
                                     from compressed)) +1)
                  from  sysibm.sysdummy1 )

 -- The following selects one random /winning/ row over a file
 -- which has no deleted records nor ineligible [to win] rows;
 -- i.e. RRN() = ROW_NUMBER() over all rows

 select dbxlib,dbxfil,rrn(Q)    /* rrn(Q) optional    */
 from qsys.qadbxref Q
 where rrn(Q) = ( select int((rand()*(select count(*)-1 as rowcnt
                                      from qsys.qadbxref)) +1)
                  from  sysibm.sysdummy1 )

  FWiW to get one winner of 10M requires only seventy balls, seven sets 
of ten balls numbered zero to nine, spread across seven bins.  The 
problem is when there are for example 9499990 entrants.  That is, if the 
first bin selects ball-9, then the second bin must have ball-5 to ball-9 
removed, to allow the game to continue.  Similarly if the first six 
numbers selected are 949999, then the seventh tube must have balls one 
to nine removed, which means no seventh number need be selected; the 
outcome is already known.  Does that invalidate the game since it is no 
longer seven distinct "Ten Choose One" [IIRC what they are called] problems?
  If such a game is valid, then so should be a game using just one bin 
selecting from ten balls, seven times.  That of course means one bin and 
ten balls are all that is required; accommodating an adjustment of, from 
which balls are available to be selected, for each of the seven digit 
selections.

Regards, Chuck