IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

RAND() for uniqueness


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 74
Location: Boston

PostPosted: Tue Jul 14, 2015 12:28 pm
Reply with quote

Hi,
For a very long time, the below query was used to generate a random number(this is inside a subroutine).
Code:

SELECT                                     
     CAST (RAND ()          * 1000000000000
           AS DECIMAL(12,0))               
     INTO :WS-RANDOM
FROM SYSIBM.SYSDUMMY1                     

The main program calls the subroutine, gets the random number and tries to insert it into a table(There is a unique index on this column). There is a logic where incase this fails with a -803, it again tries 499 times and if again it fails after all the 500 tries, the program is force-abended.

The problem is, having run all these years(there is no cleanup process on these tables), most of these values have exhausted and occasionally it abends and the job is restarted. I would not be able to have this rand() integerated into the insert query directly as after the insert is successful, I would need to use the sucessfully inserted random number for by downstream process in the same program.

Now my question is,

When I independently generate the random number, can I have a join on the table where I actually do the insert and create the random number thus making sure I generate a number which is not on the table instead of me trying 500 times to get a unique number?

Thanks,
Ashwin.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 14, 2015 8:43 pm
Reply with quote

1)How will you save the value that is being returned by RAND and then use that in your Insert?

2) Instead of Checking Inset with -803 for new number generation , why don't you first give a SELECT on that table with UR and provide the recent RAND number and if you get SQLCODE=100 then make a INSERT else generate a new number ?

3) instead of 500 times , try making it more like 1000 as you say the clean up is not yet done, check if it has any performance imapct.
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 74
Location: Boston

PostPosted: Tue Jul 14, 2015 9:37 pm
Reply with quote

Quote:
1)How will you save the value that is being returned by RAND and then use that in your Insert?

It's being fetched into a host variable and then used subsequently.

Quote:
2) Instead of Checking Inset with -803 for new number generation , why don't you first give a SELECT on that table with UR and provide the recent RAND number and if you get SQLCODE=100 then make a INSERT else generate a new number ?

I feel it doesn't make a difference here, please correct me. Also, in cases where I get a successfully generated unique number the first time, wouldn't I be wasting time doing a select for the existence check?

Quote:
3) instead of 500 times , try making it more like 1000 as you say the clean up is not yet done, check if it has any performance imapct.

Yes, it might work as you are simply trying to increase the possibility of getting a unique number from 500 to 1000. But still I feel, if there should be a way to generate a number excluding the ones that's already on the table, i could considerably be reducing my processing time.

Thanks,
Ashwin.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Jul 14, 2015 9:45 pm
Reply with quote

Quote:
But still I feel, if there should be a way to generate a number excluding the ones that's already on the table

unfortunately Your feeling is wrong ...

RAND ( and in general any random number generator )
has no memory and while statistically pseudo-random it was never guaranteed as a generator of UNIQUE numbers
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 74
Location: Boston

PostPosted: Tue Jul 14, 2015 10:11 pm
Reply with quote

Quote:
while statistically pseudo-random it was never guaranteed as a generator of UNIQUE numbers


I think you are correct. I ran a query to fetch the number of records between 100000000000 and 99999999999 on the column and interestingly, the count was only 50000, and yet it says it tried 500 times and couldn't get a number that's not on that table. This actually was making me wonder if there was a reference start point from which RAND starts generating the number.

Thanks for your thoughts enrico, I think this might mean we will have to think of an alternate logic in our process for the number generation.

Thanks,
Ashwin.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Jul 14, 2015 10:58 pm
Reply with quote

in general the random number generators provide two formats of the call

no arguments provided that should be used in the normal flow of the program logic
<random_number_returned> = <generator_name>()

one argument provided, THE SEED that it used to initialise once for all the generator logic
<unused_random_number_returned> = <generator_name>(SEED)

when testing usually an out of process call is made
with a user provided SEED so that the random sequence returned in the subsequent calls is repeatable and the tests can be done with predictable
results

the call with the seed is dropped when going to production,
this is one of the rare exceptions to the promotion process,
where a production source could be different from a test source.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Jul 15, 2015 1:22 am
Reply with quote

Why don't you make a use of adding a sequence number and see how you can rectify existing rows?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 15, 2015 5:19 pm
Reply with quote

instead of trying to generate the perfect random number,
some thought should be given
as to why this method of selecting a primary key
was chosen.

Random insertion into DB2 is no longer needed.................

to avoid wasting cycles with duplicat avoidance,
i would suggest modifying the table, and processes.
yeah it is costly, but that is the problem of old methodology
in a new world.
Back to top
View user's profile Send private message
Bill O'Boyle

CICS Moderator


Joined: 14 Jan 2008
Posts: 2501
Location: Atlanta, Georgia, USA

PostPosted: Wed Jul 15, 2015 7:15 pm
Reply with quote

If you need a random-number, Assembler can return a STCKE (Store Clock Extended) in a PIC S9(31) PACKED-DECIMAL format (after your conversion), the current date/time as "CCYYMMDDHHMMSSTHMIJU" (U=Microseconds and conversion required), or the current Microseconds, as a PIC S9(31) PACKED-DECIMAL format (after your conversion).

HTH....
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Jul 15, 2015 7:24 pm
Reply with quote

If you have 500 attempts to get a new random number after 5000 have been generated, and every single one is duplicated by an existing number, then you plain and simple have a problem somewhere.

As has been pointed out to you already, sequence numbers are unique and neither random numbers nor pseudo-random numbers (of the same size) are.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error in Rand function COBOL Programming 6
Search our Forums:

Back to Top