I am trying to use a sequence in the DB2 database with the help of the hibernate "seqhilo" generator. But the problem is its not in sync with the sequence defined in the database. I defined a sequence in the database as "starts with 1 increement with 1" and i gave a value of "max_lo" as "20" in the hibernate mapping file. But the first value which i am getting from hibernate sequence is like 85.

There are no other programs that are fetching the sequence value from the database.

Is this a problem or i am doing something wrong?

Thanks

your sequence = 1
Hibernate init itself, get next value of sequence (2 if increased before, I can't remember)
Then the hi value = 2 * (maxLo+1) = 42
lo value = 1

=> next generated value = 43

if while getting the value for the first time should it not get the first value as "1", why the logic is there to jump ahead and fetch something else?

Lets say i created a sequence as "start with 1 and increment with 1"
and i gave the "max_lo" as "20", why it should then start with 43 why not 1 is fetched at the first time , why there is a logic of getting some other value.

It can create problem as if some application is trying to create records without using hibernate but making use of the database sequence?

Thanks

Thats just the way hibernates hiLo algorithm works. You will allways have problems with HiLo if other applications are inserting into the same tables and are not using the same HiLo algorithm. If you want just straight id retrieval from the sequence, use sequence generator.

Thanks for the reply, yeah i didnt wanted to use the sequence directly as it creates two sql calls to the DB during insert.

But why the "seqhilo" logic should not make this problem go away?

Thanks

You can create your own id generator that matches the HiLo algorithm you desire. Should not be that difficult.

When using the seqhilo algorithm (hibernate 2.1.1) with a max_lo of 1000, I end up with an id skip every 1000 inserted rows.

For example, the following ids were skipped in one of our tables:
720722, 721723, 722724, 723725, 724726, ....

I think this gap is due to the use of (maxLo +1) in the seq hilo generator code:


As I understand it, for my max_lo of 1000, this code would reserve 1001 ids. Since the lo starts at 1 and resets when it hits 1001, the algorithm only counts through 1000 ids (which is what I would expect).

While this behavior doesn't cause problems when using only hibernate, it makes it quite confusing when trying to use a compatible id generation algorithm outside of hibernate-- for example, in a PL/SQL module-- since the interesting value is 1000 + 1, not 1000).

If I'm misunderstanding what I'm seeing, please let me know. Thanks.