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

Unique number generation


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

Global Moderator


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

PostPosted: Thu Mar 07, 2013 2:55 am
Reply with quote

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
View user's profile Send private message
Bill O'Boyle

CICS Moderator


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

PostPosted: Thu Mar 07, 2013 5:57 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 7:25 am
Reply with quote

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 icon_sad.gif

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
View user's profile Send private message
Bill O'Boyle

CICS Moderator


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

PostPosted: Thu Mar 07, 2013 8:41 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 9:36 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 10:21 am
Reply with 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?

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Mar 07, 2013 12:09 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 12:18 pm
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Thu Mar 07, 2013 2:15 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 07, 2013 2:16 pm
Reply with quote

how about sequences and/or identifiers?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 7:13 pm
Reply with quote

GuyC, I think you are talking about

publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004201.htm

If so then, this is the only one thing left with me to test it, so I shall try using this along with discussion with DBAs here. means while if anyone who have already implemented something which will never allow to have duplicate numbers then it would be of great help.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 9:07 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 07, 2013 9:11 pm
Reply with 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

- 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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Mar 07, 2013 10:20 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Mar 08, 2013 1:59 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Fri Mar 08, 2013 9:57 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 11, 2013 12:59 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Wed Mar 13, 2013 11:43 am
Reply with quote

Okay, DBA did not approve the above approach, so he is fine with Sequence creation.

Thanks All.
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 Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Increase the number of columns in the... IBM Tools 3
No new posts DFHPI1008 JSON generation failed COBOL Programming 0
Search our Forums:

Back to Top