|
View previous topic :: View next topic
|
| Author |
Message |
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
| Is there any DB2 function which will generate a unique no everytimes, when it will execute? |
|
| Back to top |
|
 |
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
Below query will give you unique number each time...
| Code: |
SELECT RAND() FROM SYSIBM.SYSDUMMY1; |
|
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| 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.  .
In case of UNIQUE Debasis Misra has to explain in what context he wants unique value. |
|
| Back to top |
|
|
Bill O'Boyle
CICS Moderator
Joined: 14 Jan 2008
Posts: 2501
Location: Atlanta, Georgia, USA
|
|
|
|
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. 
Principles of Operation (watch the wraparound) -
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DZ9ZR003/CCONTENTS?SHELF=DZ9ZBK03&DN=SA22-7832-03&DT=20040504121320
Regards, |
|
| Back to top |
|
|
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
| Please, can anyone have any sample program. |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| 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 |
|
|
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
| thanks, I will use that!!! |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| horrible approach from a performance point of view |
Understated. . . 
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 |
|
|
|
|
