hello! I am pretty new with db2, and I have a question considering the use of the function identity_val_local().
Let's say I have a table called CUSTOMER, looking like this:

ID NAME LAST_NAME
----------------------------

where ID is the primary key which is auto generated with auto increment by 1. Now I want to create a query that should look something like this:

insert into customer (NAME, LAST_NAME)
values ('f_name', 'l_name')
select identity_val_local()

and as you see I would like the query to insert a new row in the table and as a result to return the last inserted value in the identity column (in this case ID). Now this is a very MSSQL 2005 like syntax (because I have used it), and according to me it should be all right. But it isn't!!! If anyone can help me with these problem I would be very grateful!
Thanks in advance!

problem solved!!

insert into customer (NAME, LAST_NAME)
values ('aaa', 'sss');
values identity_val_local()

I would avoid identity_val_local() because its semantics are not clear if you have multiple identity columns and triggers involved. Just select from the result table:

Also, you have to be careful with using SELECT IDENTITY_VAL_LOCAL FROM MYTABLE. That is an SQL statement with no WHERE clause. This means you would get every row in the table returned for that query and that could have just a little bit of an impact on your performance, lets say you would get back the identity value of 460002485, do you really that value returned to your app 460002485 times? I just ran across an app that was doing this 2 months ago.
Dave