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 <srielau@ca.eye-be-em.com> wrote in message news:<c0dqd3$dba$1@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 k
> FROM NEW TABLE(INSERT INTO parent(pk, c1) VALUES(DEFAULT, 5);
> INSERT INTO child (pk, fk) VALUES(DEFAULT, k);
>
> Or even:
> WITH parent AS (SELECT pk
> FROM NEW TABLE(INSERT INTO parent(pk, c1)
> VALUES(DEFAULT, 5))
> SELECT fk INTO arent
> FROM NEW TABLE(INSERT INTO child(fk)
> SELECT pk FROM parent);
>
> One statement :-)
>
> Cheers
> Serge