Safe way to implement an auto-incremented ID Generation

, ,
1

Hello,

I’m writing a natural Programm which generates a unique ID for each record and writes this record into an Adabas Table. I’m using a rather simple mechanism to determine the next ID:

READ (1) TABLE DESC BY ID ADD 1 TO ID

Is there a better way to acomplish this. Is the table locked with such operation or better, how can I prevent 2 Users to get the same ID?

Regards,
Rok

2

Have you thought about non reusable ISNs?
Potential problem might be if many records are deleted. You would end up with a very sparse Address Converter Table.

3

Haven’t thought about it yet… The users won’t be allowed to delete records anyway…

My main concern is the primary key, which should be an automatically incremented Integer value… How to achieve this using Adabas/Natural? With Oracle/SQL one can use a custom sequence in combination with a trigger on insert for example - what would be the equvivalent in Adabas?

Regards,
Rok

4

I’m playing around with a sample programme, trying first to find a record with the calculated ID, and if it exists, make a recursive call, but only got to NAT1028: Not permitted to invoke recursive database loop…

5

A subroutine cannot PERFORM itself, but a subprogram can CALLNAT itself. But this is not the place for recursive calls. Two users could test for the existence of the same number, find none, then STORE the same value, resulting in a duplicate.

Few DBAs would recommend using ISN as a key, for the AC issue noted previously in this thread and because it makes some Adabas utilities difficult to use.

To avoid duplicate generated values, your program needs to place a control record in hold status. Because a record can be held by only one user at a time, duplicate values are avoided. Of course you must be very careful to hold the record for only as long as is absolutely necessary so that other users can get to it in a timely manner.

6

OK I understand… So perhaps the better way would be to forget about the IDs (they are allready depicted as ISNs) and just remove the attribute ID from the Adabas Table.

Thanks.

Regards,
Rok

7

As you describe your problem in greater detail, it appears this would likely be best served by an Adabas specification of a non-reusable ISN.
The two potential problems you mentioned were:
1 You wanted an integer that is always incremented by one. Non-reusable ISNs provide such a sequence of values.
2 You wanted the values to be unique. ISNs are unique.

You stated that records do not get deleted. Is this from the “data file” as well as the table file? If so, there would be no problem with a sparse ACT.

How will the “primary key” be used? It is quite apparent that the primary key has nothing to do with the business. It is supplied by the program/database, not business logic.

I am guessing now, but I imagine you might be using the primary key to logically link two or more Adabas files where there is no business key that could be employed.
A typical use might involve a search (FIND) on one file that identifies one or more records. Then, you wish to access “related” records on another file. ISNs would serve such a scenario without requiring playing games to ensure uniqueness (z.b. locking records in the table file) .

Just out of curiosity, what were your table file records going to look like? Would they just have a field that would be the unique ID and an ISN from another file? If so, by using non reusable ISNs you can eliminate the table file entirely and logically link data files without extra accesses to the table file (since it would not exist).