Use DB2 Sequences during commit

Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

I have a similar use case for Invoices/Orders.

Hi Maarten,

the problem is that the number is not globally unique, nor is it unique at all. You can add new entries that get the same number as an existing one. And the sequence itself is on a per-user basis, meaning each user has their own squence starting with 1.

Joachim

Am Donnerstag, 29. Januar 2015 08:42:43 UTC+1 schrieb [hidden email]:

What if you make your order number unique, and then everytime you want to insert you first count the number of rows to get the unique next number then insert a new row with this number accompagnied with an error handler, if it fails someone else just took it, if commits correctly you got your unique number and then update the rest of your order ..

 

my 2cts

 

@+Maarten

> "Esteban A. Maringolo" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">emari...@...> |

I have a similar use case for Invoices/Orders.

I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.

The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 

One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.

I'm also open to see what others have to suggest here.

Regards,

Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">jtu...@...>:

Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

Esteban,

I cannot really use DB sequences, but I wasn't aware of it earlier today. The number is on a per-user basis, so not globally unique.

I think I am now on the right track with using triggers (and maybe stored procedures). These will be relatively complex and outside of the control of my Glorp Application. That is the only thing I don't like about this solution

But I have no better idea than triggers, because

* the sequence is not a global sequence, the numbers start with an initial value for each customer each year...
* not every insert needs a new number, only inserts that don't come with a number or which come with a special attribute/switch (some boolean that says: I want to get an automatic number)

So my current idea is this:

I implement a "before insert" trigger on the table that looks at the new row. If the new row says "i want an auto-number", it does a select on some table in which I have the last assigned number for this customer/year, adds 1 to the number and puts it into the new row.
Then I implement an after insert trigger that updates the "last assigned" table. Maybe that can even be done in the before insert trigger.

the great thing about this is: I can meet all the requirements, and if the trigger fails, the whole transaction fails, so no gaps (in my theory). Maybe for this doing all in one trigger is the better idea - not sure about this.

The not so great thing: this is highly DBMS specific and out of the control of my application. And: I need to find out how to get this new number back into Glorp. I guess this will be done by refreshing the inserted object after the insert.

Any ideas? Better ways to do this?

Joachim




Am Donnerstag, 29. Januar 2015 08:25:33 UTC+1 schrieb Esteban A. Maringolo:

I have a similar use case for Invoices/Orders.

I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.

The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 

One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.

I'm also open to see what others have to suggest here.

Regards,

Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">jtu...@...>:

Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

First off, if you really can't have gaps at all your problem is very difficult. At any point up until the transaction really and truly commits, it might fail to commit. So you can't know until after it's happened if it works or not. There's also the corresponding problem of two users who write the same sequence number simultaneously. If you don't have a database constraint to forbid that, then it might happen. It sounds like that might be ok for you, though. And database sequences don't usually guarantee not to have gaps.