Hello Nico,

Wednesday, December 20, 2017, 4:34:09 AM, you wrote:

> CREATE TABLE ADDRESSES (
> ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
> ADDRESSNAME VARCHAR(100) COLLATE UNICODE_CI_AI,

...

> Id = 1 / AddressName = Test / ......
> Id = 2 / AddressName = Test2 / ......

> I want to know the next Id number in my table Addresses without an
> insert. In my example it will be 3. I want to reserve (without an
> insert) Id number 3 because someone else can also ask the next Id
> number. In that case it must be id number = 4, because you've got
> Number 1 and 2 in the table and number 3 is reserved.

IDENTITY is just lexical candy, useful when you have a generated id
that fires only on inserts. You can mess around with the start number
to some degree, using DDL commands, but otherwise the underlying
generator (sequence) cannot be manipulated, as the name of the internal
generator for the column is unknown to clients. This is what IDENTITY
is about, in fact.

For any id's that you want to manipulate the way you describe, create
an explicit generator for it, along with a BI trigger for the table to
set the conditions for firing the generator. You can use the NEXT
VALUE FOR <generator-name> command when you want to capture the next
value without firing the trigger. Generators fire outside the
transaction context of the request so, once a value is fetched, the
generator cannot return the same value again.

Of course, you don't want the trigger to fetch another new value and
overwrite the one you reserved, so you must make your BI trigger
detect whether the INSERT command supplies a value.

Helen Borrie