Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 function which will generate a unique no. everytimes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Fri Jun 26, 2009 5:10 pm    Post subject: DB2 function which will generate a unique no. everytimes
Reply with quote

Is there any DB2 function which will generate a unique no everytimes, when it will execute?
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Jun 26, 2009 6:01 pm    Post subject:
Reply with quote

Below query will give you unique number each time...
Code:

SELECT RAND() FROM SYSIBM.SYSDUMMY1;         
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jun 26, 2009 6:25 pm    Post subject:
Reply with quote

Sambhaji wrote:
Below query will give you unique number each time...
Code:

SELECT RAND() FROM SYSIBM.SYSDUMMY1;         


Random and Unique are not the same.

Unique in what context, within a file/table, a system, a network, worldwide, or universe.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Jun 26, 2009 6:36 pm    Post subject:
Reply with quote

Quote:
Random and Unique are not the same.

Unique in what context, within a file/table, a system, a network, worldwide, or universe.


Yes i aggree... I mistaken the reuirement. icon_sad.gif .


In case of UNIQUE Debasis Misra has to explain in what context he wants unique value.
Back to top
View user's profile Send private message
Bill O'Boyle

CICS Moderator


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

PostPosted: Fri Jun 26, 2009 8:34 pm    Post subject: Reply to: DB2 function which will generate a unique no. ever
Reply with quote

If it's just a matter of ensuring uniqueness, then call an Assembler sub-program, which issues a Store-Clock instruction (STCK), which generates a binary-doubleword (8-Bytes) or a Store-Clock Extended instruction (STCKE), which generates a binary-quadword (16-Bytes). Regardless of which one you choose, the value is guaranteed to be unique. However, the 8-Byte Store-Clock reaches standard epoch and will turnover in September, 2042, so you can plan this ahead of time for your Grandchildren. icon_wink.gif
Principles of Operation (watch the wraparound) -

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DZ9ZR003/CCONTENTS?SHELF=DZ9ZBK03&DN=SA22-7832-03&DT=20040504121320

Regards,
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Jun 29, 2009 8:57 am    Post subject:
Reply with quote

Hi Sambhaji
I have to generate Unique no, this is to generate account no.

Hi Bill O'Boyle,

I have read the docs, but not able to get the exact thing. Can you help me out in programming level?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 29, 2009 9:33 am    Post subject:
Reply with quote

Hello,

Quote:
call an Assembler sub-program, which issues a Store-Clock instruction (STCK), which generates a binary-doubleword (8-Bytes) or a Store-Clock Extended instruction (STCKE), which generates a binary-quadword (16-Bytes). Regardless of which one you choose, the value is guaranteed to be unique
To do what Bill described, you would write a small assembler routine that would return the value generated by a STCK or STCKE instruction.
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Jun 29, 2009 10:02 am    Post subject: Reply to: DB2 function which will generate a unique no. ever
Reply with quote

Please, can anyone have any sample program.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Jun 29, 2009 10:41 am    Post subject:
Reply with quote

Quote:

Hi Sambhaji
I have to generate Unique no, this is to generate account no.


Why dont you generate (max(account no) +1) every time.

It will be always unique. This is the normal logic followed usually for fields such as account number or transaction number etc
Back to top
View user's profile Send private message
Debasis Misra
Warnings : 1

New User


Joined: 16 Sep 2008
Posts: 72
Location: Bangalore

PostPosted: Mon Jun 29, 2009 6:05 pm    Post subject:
Reply with quote

thanks, I will use that!!!
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Mon Jun 29, 2009 6:17 pm    Post subject: Reply to: DB2 function which will generate a unique no. ever
Reply with quote

Quote:
Why dont you generate (max(account no) +1) every time.


horrible approach from a performance point of view
what will be the insert rate ??

how do You plan to protect the processing window
between the select and the insert

unless properly coded there is a small chance of getting twice the same value
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 29, 2009 9:48 pm    Post subject:
Reply with quote

Hello,

Quote:
horrible approach from a performance point of view
Understated. . . icon_cool.gif

Not counting the possibility of a duplicate. . .

One way this is sometimes done (with the possibility of causing a bottleneck) if to have a separate table that has the "next available" number. When an insert is to be done, this row is read for update, the next available number is updated and the lock is released.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us