View previous topic :: View next topic
|
Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Team,
I got a situation where I need to create a unique number,
Current processing calling Stored procedures in that,
Getting the existing number from the table
Code: |
select max(num_t1) from xyz with ur |
now this num _t1 contains lets say 123456 value and now I am using this value to form a unique request id like YYYY-123456-XX. and this eventually getting inserted to a another table which contains request id, user(which is paased to the SP) columns.
This works fine for a single instance but what if there are 10-20 users sending this request at the same time , then in that case there is great possibility of assigning the same request id to 10-20 users, so how can we avoid that?
so even if 10-20 hits coming in I should be assigning al of them a different request id. |
|
Back to top |
|
|
Bill O'Boyle
CICS Moderator
Joined: 14 Jan 2008 Posts: 2501 Location: Atlanta, Georgia, USA
|
|
|
|
What's the maximum-number of digits for this unique number?
Correct me if I'm wrong, but when you request the date/time from DB2, the time-portion is at the microsecond-level?
Hence, the question regarding the maximum-number of digits.
Then again, a called Assembler sub-program, which returns a Store-Clock (STCK --- OK in CICS) value is guaranteed to be unique or a different called Assembler sub-program, which issues a TIME Macro, LINKAGE=SYSTEM (OK to use in CICS), can return the time, with a format of HHMMSSTHMIJU (M=Milliseconds (Thousandths), I=Ten Thousandths, J=Hundred Thousandths, U=Millionths (Microseconds). |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Quote: |
What's the maximum-number of digits for this unique number? |
it will be 999999.
Quote: |
Correct me if I'm wrong, but when you request the date/time from DB2, the time-portion is at the microsecond-level? |
yes
Quote: |
Then again, a called Assembler sub-program, which returns a Store-Clock (STCK --- OK in CICS) value is guaranteed to be unique or a different called Assembler sub-program, which issues a TIME Macro, LINKAGE=SYSTEM (OK to use in CICS), can return the time, with a format of HHMMSSTHMIJU (M=Milliseconds (Thousandths), I=Ten Thousandths, J=Hundred Thousandths, U=Millionths (Microseconds). |
I do not know about any assembler program, so could not be possible to write it down
Can I use here the sequence object concept?
Any idea what happens when multiple hits at the same time happens to NEXT sequence object statement? |
|
Back to top |
|
|
Bill O'Boyle
CICS Moderator
Joined: 14 Jan 2008 Posts: 2501 Location: Atlanta, Georgia, USA
|
|
|
|
If the DB2 Timestamp resolves to the microsecond level, perhaps you could use these low-order digits (THMIJU)? From what you've posted, these 6-digits are surrounded by a prefix and suffix. Are they constants or can their values vary? If they are variable, then the likelihood of these prefix and suffix values, surrounding the "THMIJU" from the DB2 Timestamp and raising a duplicate, is reduced significantly.
Have you looked at COBOL Intrinsic Function "RANDOM" or its LE counterpart "CEERAN0"?
Maybe someone more familiar with DB2 will have a better answer as that's about all I can offer.... |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Thanks Bill, I would take a look at the RANDOM function.
The values surrounded by prefix and suffix does not vary , they are constant. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
even if the function RANDOM generates the new value , will it make sure to generate always a new value even if 2 hits happening at exact same time?
Also has anyone tried using the Sequence object in DB2, where in the NEXT VALUE generates the unique number all the time even if 2 hits happening at the same time? as per my knowledge if 2 hits are happening at this fuction the DB2 will keep any one in queue and make sure unique number is assigned?? |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Quote: |
even if the function RANDOM generates the new value , will it make sure to generate always a new value even if 2 hits happening at exact same time? |
Have you looked at the definition of RAND? Have you written some SQL using it, which did not work? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Actually, I am not able to create a scenario in DEV testing where 2 hits are happening at the same time, but in QA they have performance testing with some tool and then they can test there. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Rohit,
You can ask the performance testing guys to test this scenario.
And see if you can make use of Identity_Column for such cases.
I am not sure but I think it will not happen that same value of Identity_Column will be assigned to two different users
Regards,
Chandan |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
how about sequences and/or identifiers? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Anuj:
I tried executing below queries in one go,
Code: |
SELECT RAND(123)
FROM SYSIBM.SYSDUMMY1;
SELECT RAND(100)
FROM SYSIBM.SYSDUMMY1;
SELECT RAND(100)
FROM SYSIBM.SYSDUMMY1;
SELECT RAND(100)
FROM SYSIBM.SYSDUMMY1;
SELECT RAND(100)
FROM SYSIBM.SYSDUMMY1; |
Result are,
Code: |
1
-----------------
0.270715510598717
1> SELECT RAND(123)
2> FROM SYSIBM.SYSDUMMY1
3> go
1
-------------------
0.45298007803642193
1> SELECT RAND(100)
2> FROM SYSIBM.SYSDUMMY1
3> go
1
-----------------
0.270715510598717
1> SELECT RAND(100)
2> FROM SYSIBM.SYSDUMMY1
3> go
1
-----------------
0.270715510598717
1> SELECT RAND(100)
2> FROM SYSIBM.SYSDUMMY1
3> go
1
-----------------
0.270715510598717
1> SELECT RAND(100)
2> FROM SYSIBM.SYSDUMMY1
3> go |
So here I see the duplicate values has been assigned, Am I doing it in a correct way? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
just use sequence this will do what you want and is specifically made to replace these kind of select max(num_t1) from xyz
- You should never ever use WITH UR for this type of operation
- the second-best way would be to
SELECT num_t1 INTO :HV1 FROM FINAL_TABLE (UPDATE xyz SET num_t1 = num_t1 + 1 ); COMMIT;
ofcourse you would get locking on that table, (again:) that's why we have sequences. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Quote: |
just use sequence this will do what you want and is specifically made to replace these kind of select max(num_t1) from xyz |
Noted, and I will try this.
But
Quote: |
- the second-best way would be to
SELECT num_t1 INTO :HV1 FROM FINAL_TABLE (UPDATE xyz SET num_t1 = num_t1 + 1 ); COMMIT;
ofcourse you would get locking on that table, (again:) that's why we have sequences. |
will be an additional hit to DB2 for update and concerns of locking so not a good choice at this moment. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Quote: |
...will be an additional hit to DB2 for update and concerns of locking so not a good choice at this moment. |
I'm a bit confused : you are getting the number using select max, but you refuse to update it to add 1. And then you complain that you are getting the same number?
Anyway, the point is moot because of the sequence-solution. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
If two users are going to hit
Code: |
SELECT num_t1 INTO :HV1 FROM FINAL_TABLE (UPDATE xyz SET num_t1 = num_t1 + 1 ); COMMIT; |
then wouldn't they have a chances of getting the same number? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Rohit Umarjikar wrote: |
If two users are going to hit
Code: |
SELECT num_t1 INTO :HV1 FROM FINAL_TABLE (UPDATE xyz SET num_t1 = num_t1 + 1 ); COMMIT; |
then wouldn't they have a chances of getting the same number? |
No, one might experience locking, but ... No! |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Okay, DBA did not approve the above approach, so he is fine with Sequence creation.
Thanks All. |
|
Back to top |
|
|
|