Generating unique

[Poul Møller Hansen]

I have made a stored procedure, containing this part for generating a unique
reference number.

SET i = 0;
REPEAT
SET i = i + 1;
SELECT RAND()
INTO reference
FROM SYSIBM.SYSDUMMY1;
SET p_reference = ref_prefix || SUBSTR(CAST(reference AS CHAR(12)),3);
SELECT cardno
INTO cardno
FROM HTTP.TB00008 WHERE reference = p_reference;
UNTIL (cardno IS NULL OR i = 1000)
END REPEAT;
IF (i = 1000) THEN
SIGNAL c_failed_reference
SET MESSAGE_TEXT = 'Unique reference could not be generated in a 1000
attempts';
END IF;

Unfortunately it happens from time to time, that it fails generating a
unique reference in a 1000 attempts.
Is the RAND() function not unique enough, and what can I do else ?
Thanks in advance

Poul

[Serge Rielau]

You try a 1000 times to find a gap in your card no? Are the rows missing
or is cardno truly NULL in teh row? If the later, what about this:
How about this:
SELECT reference INTO p_reference
FROM
(SELECT reference,
rownumber() over () as rn,
count(*) over cnt
FROM HTTP.TB00008
WHERE cardno IS NULL) AS X
WHERE rn = integer(rand() * cnt) + 1

This will find you a random gap.

Cheers
Serge

[Serge Rielau]

Small correction:

SELECT reference INTO p_reference
FROM
(SELECT reference,
rownumber() over () as rn,
count(*) over cnt
FROM HTTP.TB00008
WHERE cardno IS NULL) AS X, TABLE(VALUES(rand())) AS R(rand) WHERE rn = integer(rand * cnt) + 1

Cheres
Serge

[Poul Møller Hansen]

> You try a 1000 times to find a gap in your card no? Are the rows missing

or is cardno truly NULL in teh row? If the later, what about this:
How about this:
SELECT reference INTO p_reference
FROM
(SELECT reference,
rownumber() over () as rn,
count(*) over cnt
FROM HTTP.TB00008
WHERE cardno IS NULL) AS X
WHERE rn = integer(rand() * cnt) + 1

No, I'm making a unique reference that hasn't already been used, at the time
of inserting.
cardno is a not null field generated in a triggered sequence.
So if the select cardno from http.tb0008 where reference = "generated value"
gives no result,
I have found one.

The escaping after a 1000 attempts is a temporary one to prevent looping
forever, when it fails finding a unique one.
I'm just surprised that the select rand() is giving the same result a 1000
times in a row.
Cheers
Poul

[Jonathan Leffler]

Poul Møller Hansen wrote:

I have made a stored procedure, containing this part for generating a unique
reference number.

Does it need to be a random number? Why not use a sequence? If you
have a big 'stride' which is mutually prime with the number of entries
in the sequence (eg - if the sequence ranges over 1,000,000 to
1,000,000,000, then 31,572,493 is prime relative to 999,000,000 and
will generate numbers such as 32,572,493 followed by 64,144,986 and
95,717,479 and 127,289,972 and so on. The 'mutual prime' property
ensures that every value in the range will be used eventually.

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[AK]

Poul,

the easiest way to get unique numbers is to use a sequence, which
gives you consecutive numbers, i.e. 1,2,3,...

if you need unique numbers to be not consecutive, you might want to
play with swapping bytes and / or bits.

For instance, get numbers 0x00000001, 0x00000002, 0x00000003,...
and swap bytes to 0x01000000, 0x02000000, 0x03000000,...

You might also read Donald Knuth's book, where he describes basics of
random numbers generating

[Gordon Slishman]

To measure the number of repeats in a small rand() sample of
size 10,000, one can do something like this on UDB:

create table samp ( i integer ) ;

insert into samp
with temp(n) as ( values( 1 )
union all
select n + 1 from temp
where n < 10000 )
select n from temp ;

commit ;

select count( * ) from samp ;
select count( distinct rand(1) ) from samp ;
select count( distinct rand(5) ) from samp ;
select count( distinct rand(7) ) from samp ;

The desired result is 10,000 from each select.

-Gordon

"Poul Møller Hansen" <[e-mail]> wrote in message
news:HQ**************@news.get2net.dk...

I have made a stored procedure, containing this part for generating a
unique reference number.

SET i = 0;
REPEAT
SET i = i + 1;
SELECT RAND()
INTO reference
FROM SYSIBM.SYSDUMMY1;
SET p_reference = ref_prefix || SUBSTR(CAST(reference AS CHAR(12)),3);
SELECT cardno
INTO cardno
FROM HTTP.TB00008 WHERE reference = p_reference;
UNTIL (cardno IS NULL OR i = 1000)
END REPEAT;
IF (i = 1000) THEN
SIGNAL c_failed_reference
SET MESSAGE_TEXT = 'Unique reference could not be generated in a 1000
attempts';
END IF;

Unfortunately it happens from time to time, that it fails generating a
unique reference in a 1000 attempts.
Is the RAND() function not unique enough, and what can I do else ?
Thanks in advance

Poul