Re: Random number access on the i5

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