Hello TonyV,

I believe I can explain most of Breck's observations but before I go "all long winded on this thread"

. . .

it might not hurt to ask if your application is a fully embedded, single connection design, or if you can tolerate an occassional skipped value in the sequence range?

Maybe doing something like this might be helpful?

SET currval_var = (*<seq-name>*.NEXTVAL - δ); 
// where δ is your unit of increment; assumptions here of course

  {which could be done inside your exception handler for the 
   SQLE_SEQUENCE_CURRVAL_NOT_DEFINED error returned when you use CURRVAL
   http://dcx/index.html#sa160/en/saerrors/errm1363.html }

Alternatively, getting the NEXTVAL directly and doing the conversion in client side code?

Of course either of those would burn a sequence increment on you. But that may not be as critical as it appears at first blush.

If you have more than more than one connection, or use snapshot isolation, or have some other mitigating factor ... you might need to use some user defined function or external function for this ... instead of using sequences.

Much would depend on the requirements here. But let us know so we can group-think other approaches ....

let us know if this was at all helpful

This seems crude, but can you setup a separate table that stores the last used ID and then use the same logic that the sequence generator uses to query that table, increment the result and "know" the next ID before the sequence generator is called?

@TonyV: You can't let the database generate the value itself (i.e. using the sequence as a default, such as

ID INT PRIMARY KEY DEFAULT(mySequence.nextval), ...

and then query that value after the insert?

Some more observations (that do not act as answers, I agree...):

• The behaviour of SYSSEQUENCE.resume_at (as of Breck's tests, I haven't checked for myself) seems different than that of the according DEFAULT (GLOBAL) AUTOINCREMENT values in the system catalog (i.e. the according max_identity of the particular column). These values do get updated during a checkpoint, as Glenn has explained in this FAQ. - Therefore I had expected the situation for a sequence would be similar...

• However, even if the value would be updated during a checkpoint, it would be worthless when there are concurrent transactions, as querying the system table could return a value that already has been used by a different connection - and to be honest, facilities like sequences and default autoincrements are meant to handle concurrent inserts automatically, so you would surely not want to "burn" these smart facilities by a "manual intervention..."... - That's my opinion, obviously.

• With DEFAULT AUTOINCREMENT, you are neither able to query the current value before you have done a real insert on that connection - or you can call get_identity() but that will "waste" an ID value if you do not use it afterwards. Therefore I think this is not "cheaper" than to have to select a sequence's NEXTVAL() initially...

So basically I would share Nick's suggestion:

If you can afford to "waste" a sequence value now and then (note, these are BIG INTs, so really huge ranges), then I'd call NEXTVAL() first. - If not, you may let the database create these values and read them after the fact (see my initial question).

How about using SELECT GET_IDENTITY?