Generic Sequence number generator program

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

Hi,
I need to write a generic 9 byte (Numeric) Sequence number generator program. This unique Sequence number is going to be stored it in a table.
The flow of the program is below mentioned,
1) Select the maximum value of the table and increment it.
Select max(Col1)
from table1.
2) Insert into the table.

This works fine for the batch. When i thought to implement the same logic in online.
If i implement this logic in online,
if a two users using same online program concurrently for generating a 9 byte unique sequence number then
i may fell into this below scenarios,
a) If the table have maximum value as 000000100 then for both user will get 000000101 and trying to insert it.
however it can be handled during insert, if there is second row inserting then it will be throw a sqlcode -803 and again the second user who missed(failed) it need to try the same transaction again.

So to make it in general i would need to change the logic in batch also.

Whether there is any other way to make the sequence number in generic to both online and batch application ?

dbzTHEdinosauer · Posted: Tue Jun 12, 2012 7:53 pm

1. generic? it is anything but. it is a very specific value based on the existing max value.

2. why not have the same module that 'calculates the next sequence no'
CALLed by both batch and cics?

As far as batch, there is no need to have a separate routine,
because someone from the online could be working at the same time as the batch job.
i.e. loop on -803.

and, why SELECT, add, INSERT?

just insert max + 1.

what are the rules on 999999999 + 1?
billion - 1 is not uncommon these days.

and i image the sequence no (part no) is a decimal.
should be large integer.

PLUS, THIS IS A DB2 QUESTION. WHY IS IT IN COBOL?

Pandora-Box · Posted: Tue Jun 12, 2012 8:03 pm

Have a field with Latest_sq_used something like that .

Update this field what was the last sequence + 1 that is being generated so the program which is going to need the next sequence can read from this field next time will pick the sequence from there.

So the first run you might need insert a static data

Just a thought

Bill O'Boyle · Posted: Tue Jun 12, 2012 8:33 pm

Write a small called Assembler sub-program (can be used in Batch or On-Line), passing a 9-Byte Display-Numeric parm as well a 24-Byte workarea (for re-entrancy), which issues a Store-Clock into a Doubleword, then load the Doubleword into an 8-Byte register (LG), followed by a CVDG (Convert to Decimal Grande) into a 16-Byte Quadword, ensure the Quadword-sign nibble is 'F' and finally, unpack the Quadword into the 9-Byte Display-Numeric parm.

I can guarantee you that the returned-value in the 9-Byte parm will be unique 99.99% of the time.

The Doubleword and Quadword are composed of the 24-Byte workarea passed from the Caller.

Naish · New User Joined: 07 Dec 2006 Posts: 82 Location: UK

Wouldn't CREATE SEQUENCE help?

Experts please correct me if I am wrong.

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

Thank you for replying Dick!

Dick, Yes The above piece of code which i have tried can be called in batch and CICS.But still my program is a Table specific(COST_TABLE1).
The Requirement is to write a Generic module which needs to populate 9 byte value for inserting a key for any clustering index for a DB2 table.

As per the requirement it wont available for that count( APPROXIMATELY 9,999,999). Hence 9 byte will be hold good for our requirement.

I tried it with DB2, but even accomplishing it with cobol or anyother solutions will be good. Going forward i will post it appropriately.

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

Thanks Pandora, the suggestion provided is of table specific.
The Requirement is to write a Generic module which needs to populate 9 byte value for inserting a key for any clustering index for a DB2 table.

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

Thanks Bill,
As i am new to the assembler level coding, i googled it and went through some of the assembler language manuals.
1) IBM High Level Assembler for MVS & VM & VSE Release 5
2) IBM High Level Assembler for MVS & VM & VSE
Release 2 Presentation Guide

It would be of great help if you could provide me with any other manuals or guidance for me to start with assmbler level coding.

dbzTHEdinosauer · Posted: Wed Jun 13, 2012 8:30 pm

is this sequence no in the high order portion of the key?

do you need a random sequence no
or
a sequenced number based on table?
or
an unused no that is > last sequence generated.

random unique you will not get from Bill's solution.

the only way you will be 100percent certain that the sequence no generated has not been used before is to have some control over
the sequence no generated in relation to sequence no's previously generated.

you prattle on about generic and not specific to a table,
yet you showed in your opening post,
the INSERT SQL, which is not generic, it is specific to a table.

I think that you are deluding yourself in thinking billion - 1 is enough.

today, next year maybe, but it a couple of years you better be working at a new site,
because i have seen this "oh, a billion is more than enough"
bite many applications in the ass.
some could solve it by going negative, some could roll and start from 1 again,
but many were caught in a trap created by their own optimism.

sorta like voting for obama and thinking things would get better

GuyC · Posted: Wed Jun 13, 2012 8:41 pm

just define the column as
- identifier if it has to be unique per table
- sequence if it has to be unique .

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

The requirement is to create a random unique number and also that the previously used number should not be used again . It should be unique always.

dick scherrer · Posted: Thu Jun 14, 2012 9:21 pm

Hello,

Craq Giegerich · Posted: Thu Jun 14, 2012 9:44 pm

dbzTHEdinosauer · Posted: Thu Jun 14, 2012 9:45 pm

The TS is caught in a trap.

if he uses the concept provided by GuyC
(and incidentally used by most professional IT shops throughout the planet)
his module is not needed

also, the notion of adding new rows at various 'random' positions has been proven - BS.

New rows (inserted) should not be out-of-sequence with the basic key.
we used to take a timestamp, and move 1 for 4 to the end, 20-26 to the begining,
(there were other idiot attempts at spacing out additional rows)
but it has been proven that it saves nothing,
helps nowhere,
and in general a waste of cycles to create this randomness.
old BS design.

Muralikrishnan_Ram · New User Joined: 28 May 2012 Posts: 6 Location: India

It is not going to be used across multiple tables. It should be a random but unique number generator. It will be called to generate unique numbers concurrently .

dbzTHEdinosauer · Posted: Thu Jun 14, 2012 9:54 pm

Pandora-Box · Posted: Thu Jun 14, 2012 9:57 pm

Take the current timestamp and remove dots and dashes and apend with 1 billionth of a second

enrico-sorichetti · Posted: Thu Jun 14, 2012 10:01 pm

the topic is just becoming a useless waste of resources
will be locked in a while

Naish · New User Joined: 07 Dec 2006 Posts: 82 Location: UK

Experts, as I have previously mentioned-

dick scherrer · Posted: Thu Jun 14, 2012 10:11 pm

Hello,

For whatever reason, there is a belief that the number needs to be random.

What business reason might there be for such a "requirement"?

agkshirsagar · Posted: Sat Jun 23, 2012 12:56 am

OP did not put his requirement correctly. Truly random numbers can't be unique by definition.

There is an intrinsic function in COBOL to generate random numbers. Also, there is DB2 function RAND that provides this same functionality.
Read the manual my friend!

Esentially, OP is looking for a semaphore like mechanism to avoid contention. I fully agree with Naish that it can be achieved with DB2 SEQUENCES.

Bill O'Boyle · Posted: Sat Jun 23, 2012 1:39 am

Here's the example sub-program I had described -