View previous topic :: View next topic
|
Author |
Message |
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
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 |
|
|
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Why don't you make a use of adding a sequence number and see how you can rectify existing rows? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Bill O'Boyle
CICS Moderator
Joined: 14 Jan 2008 Posts: 2501 Location: Atlanta, Georgia, USA
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|