HI Everybody ,

This is the DB2 performance issue I was referring to .
We have requirement where we need to insert some values to the DB2
table and
get back its ID. Its possible by using an insert query , followed by
select
ID query. (Using IDENTITY_LOCAL_VAR)
However what we are looking for is (ofcourse, an ideal case) a single
query
which performs insert and returns sequence generated. (To adress some
performance bottlenecks)
My experience with this kind of thing is with Oracle and in those
cases, the
new sequence/identity is returned as part of the result object itself
(i.e.
you don't need to query the database again).
If there is any similar construct in DB2, it would be of greater help.

Thanks in Advance

Raja Shekar wrote:
> HI Everybody ,
>
> This is the DB2 performance issue I was referring to .
> We have requirement where we need to insert some values to the DB2
> table and
> get back its ID. Its possible by using an insert query , followed by
> select
> ID query. (Using IDENTITY_LOCAL_VAR)
> However what we are looking for is (ofcourse, an ideal case) a single
> query
> which performs insert and returns sequence generated. (To adress some
> performance bottlenecks)
> My experience with this kind of thing is with Oracle and in those
> cases, the
> new sequence/identity is returned as part of the result object itself
> (i.e.
> you don't need to query the database again).
> If there is any similar construct in DB2, it would be of greater help.
>
> Thanks in Advance
>
CREATE TABLE T(id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
c1 INT);
SET id = (SELECT id FROM NEW TABLE(INSERT INTO T(c1) VALUES(5);

Check out:
http://www-128.ibm.com/developerwork...dm-0411rielau/

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab