Identity OR sequence which is better

[Srini]

Hello,

I would like to know Identity OR sequence which is better?.

My requirement is simple. I have a parent table with ID (generated or
from sequence) and I want that ID after inserting parent record so
that I could use it for Child table(S).

I read in on the the thread ibm is planning to deprecate
identity_val_local() function in future. Appreciate the DB2 experts
advice.

Thanks,
Srini.

[Serge Rielau]

Either of them have their place.
If the key you need is local to a single table and you don't need
explicit control over it then IDENTITY gives you a few benefits.
E.g. IDENTITY can be generated by LOAD, sequences surrently cannot.

One of the few drawbacks of identity today is that you cannot switch it
OFF which can be a pain for IMPORT. This however will be addressed in
DB2 Stinger.

identity_val_local() shudl be deprecated because:
SELECT FROM INSERT in DB2 V8.1 FP4 does a much better job in retrieving
generated values (not only identity) in a set oriented fashion.
So instead of doing:
INSERT INTO parent(pk, c1) VALUES(DEFAULT, 5);
INSERT INTO child (pk, fk) VALUES(DEFAULT, identity_val_local());

you can now say:
SELECT pk INTO :pk
FROM NEW TABLE(INSERT INTO parent(pk, c1) VALUES(DEFAULT, 5);
INSERT INTO child (pk, fk) VALUES(DEFAULT, :pk);

Or even:
WITH parent AS (SELECT pk
FROM NEW TABLE(INSERT INTO parent(pk, c1)
VALUES(DEFAULT, 5))
SELECT fk INTO :parent
FROM NEW TABLE(INSERT INTO child(fk)
SELECT pk FROM parent);

One statement :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Srini]

Hi Serge,
Thank you very much for your help.
I have learned something new which is good.
I understand i have a better control with SEQUENCE than IDENTITY.

Question -1
-----------
identity_val_local() SHOULD be deprecated or WILL be deprecated or
BOTH?

Question -2
-----------
Will IDENTITY work fine with Concurrent INSERTS.
For Example, We have two tables that are NOT related in any way.

TABLE_A has ID (GENERATED AS IDENTITY)
TABLE_B has ID (GENERATED AS IDENTITY)

USER-A will INSERT a record in TABLE_A
USER-B will INSERT a record in TABLE_B

Now if USER-A calls identity_val_local() function, How he can be sure
he is getting ID for TABLE_A and not TABLE_B. Both the user will have
their own Transaction.

Thanks for your time,
Srini.

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c0**********@hanover.torolab.ibm.com>...

Either of them have their place.
If the key you need is local to a single table and you don't need
explicit control over it then IDENTITY gives you a few benefits.
E.g. IDENTITY can be generated by LOAD, sequences surrently cannot.

One of the few drawbacks of identity today is that you cannot switch it
OFF which can be a pain for IMPORT. This however will be addressed in
DB2 Stinger.

identity_val_local() shudl be deprecated because:
SELECT FROM INSERT in DB2 V8.1 FP4 does a much better job in retrieving
generated values (not only identity) in a set oriented fashion.
So instead of doing:
INSERT INTO parent(pk, c1) VALUES(DEFAULT, 5);
INSERT INTO child (pk, fk) VALUES(DEFAULT, identity_val_local());

you can now say:
SELECT pk INTO :pk
FROM NEW TABLE(INSERT INTO parent(pk, c1) VALUES(DEFAULT, 5);
INSERT INTO child (pk, fk) VALUES(DEFAULT, :pk);

Or even:
WITH parent AS (SELECT pk
FROM NEW TABLE(INSERT INTO parent(pk, c1)
VALUES(DEFAULT, 5))
SELECT fk INTO :parent
FROM NEW TABLE(INSERT INTO child(fk)
SELECT pk FROM parent);

One statement :-)

Cheers
Serge

[Serge Rielau]

Srini,

Deprecation is a very fuzzy word. E.g. LONAG VARCHAR and LONG VARGRAPHIC
are deprecated. You can see that from the fact that none of the new
features coming into DB2 support these old types.
Nonetheless neither support nor documentation has been withdrawn.

identity_val_local() was from the very beginning and consciously placed
into a small box (single row insert with values).
You will not see this function being extended and it is also very
unlikely you will see an identity_val_global() or so...

Will IBM formally pull support on this function at some point? I don't
know. It's to small a fish to worry about, IMHO.
If that happens there will be ample warning though.

W.r.t. concurrency.
identity_val_local() is local to the connection. IDENTITY generation in
another connection (whether the same or different table) will not affect
its value.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab