For the second time: How to get new row pri key UID?
Adam Flinton
I am using VAj with DB2 & the tables have the usual select max +1 way of
generating a new primary key. When I insert a new row via VAJava is there
anyway for me to get the new row UID?
I have a form which needs to be able to insert a new invoice & then assign
the selected items to it.
The latter is no problem so long as I know what the new Invoice number is.
I have the std select max +1 trigger running fine. It is a matter of getting
the value of the new row BACK to the app. The trigger is obviously under the
db control as it is a mulituser system.
Basically I hit I need a button which inserts the new row (thats' OK) the
finds the new row Pri Key UID (that's the prob) & then updates a number of
row of another table setting their foreign key to the value of the new row
pri key (that's OK).
If I try and find the new row UID via the client app it is possible that if I
use select MAX then someone else might be doing the same thing at the same
time (or soon after / before) & both clients would assign the foreign keys to
the same row / PriKey.
TIA
Adam
dav...@ca.ibm.com
I think the easiest solution is to do away with the trigger. Instead,
acquire an X-lock on the table, then generate the new row. To generate
the PK, SELECT MAX(), then add 1 within the application, then: INSERT,
and COMMIT. Before you think that this will impair concurrency too much,
note that the trigger is also 'trying' to serialize the INSERT.
Aside: I pick the word 'trying' in the last sentance with care.
Appendix H of the SQL Refr shows that before triggers acquires an S-lock
on the "SELECT MAX()" row (step 2 in the table). Therefore, another
concurrent application doing an INSERT can read the same MAX() value. If
you have a PK defined, this scenario will result in a SQL0803, PK
violation. So, if you stick with the trigger, then your application
should handle -803s (usually by retrying to INSERT the new record).
Another solution is to create a one row / one column table that contains
the current (or last PK) for the target table. Then the application must
maintain the PK in this new table (again with X-locks).
Hope this helps...
Adam Flinton
>Hope this helps..
Again so do I.......<G>.
Adam
M. Rusoff
sequential is to generate a GUID type key. On windows, you could use the
various GUID functions, OR the GUID RFC for doing it yourself. In my
experience, this is generally overkill for most applications. Simply
generating a number from the network-id, process-id, and timestamp, with
an additional sequential part so that you can do batched inserts by
incrementing rather than calling a function (a SLOOOWWWW function). For
many applications, something like:
2-4 bytes of ip address, 2-4 bytes of PID, 2-8 bytes of timestamp and
2-4 bytes of sequence, with the specific scheme depending on the
insertion rate from each node.
More alternatives include pseudo-random (can be a problem, but works OK
if you use a backoff type approach and accept that every so often an
insert will fail) or using actual data values that when COMBINED create
a unique identifier and devising a hash algorithm to generate a shorter
single key.